FAQ
Hello,

I am building a complaint management system. When I'm loading a list of
complaints, I'd like to retrieve an extra column that contains some
information that is determined in SQL on the fly. For example, if the
complaint table has a column to store the date by which the complaint
should be resolved, I'd like to determine whether that date is in the
past or not.

This is what the SQL generating code could look like (the example has
been simplified):

StringBuilder query = new StringBuilder();
query.append("SELECT ");
query.append("#result('c.COMPLAINT_ID' 'Integer' 'COMPLAINT_ID'), ");
query.append("#result('c.RESOLVE_BY_DATE' 'Date'), ");
query.append("CASE WHEN (SYSDATE > c.RESOLVE_BY_DATE) THEN 1 ELSE 0 END
TOO_LATE ");

The column "TOO_LATE" is determined on the fly. How can I retrieve this
column?

I've tried two things:

SQLTemplate template = new SQLTemplate(Complaint.class, query.toString());
DbEntity dbe =
dataContext.getEntityResolver().getDataMap("ComplaintsMap").getDbEntity("CPL_COMPLAINT");
Object a = dbe.getAttributeMap();
System.out.println(a);
if (dbe.getAttribute("TOO_LATE") == null) {
DbAttribute dba = new DbAttribute("TOO_LATE", 4, dbe);
dbe.addAttribute(dba);
}
List<Complaint> result = dataContext.performQuery(template);

In this code I tried to temporarily add a 'fake' DbEntity to the DataMap
for "TOO_LATE". The problem with this code is that Cayenne looks for
"t0"."TOO_LATE", which doesn't exist, obviously.

My other attempt was to retrieve DataRows, use the DataContext to create
DataObjects and manually read and set "TOO_LATE":

SQLTemplate template = new SQLTemplate(Complaint.class, query.toString());
template.setFetchingDataRows(true);
List<DataRow> dataRows = dataContext.performQuery(template);
List<Complaint> result = new ArrayList<Complaint>();
Iterator<DataRow> i = dataRows.iterator();
while (i.hasNext()) {
DataRow dataRow = (DataRow) i.next();
Complaint c = (Complaint)
dataContext.objectFromDataRow(Complaint.class, dataRow, false);
c.tooLate = dataRow.get("TOO_LATE").equals(1);
}

The problem with this code is that somehow, the column "TOO_LATE" is not
included in the DataRow. dataRow.get("TOO_LATE") returns null.

I'm not very experienced in Cayenne, so perhaps I'm making a very
obvious mistake. Can anyone offer any suggestions on how to instantiate
a Complaint object and retrieve the "TOO_LATE" column?

Thanks in advance!

Best regards,

Wout

I'm using an Oracle database.

Search Discussions

  • Mike Kienenberger at Jul 14, 2008 at 3:13 pm
    What you're looking for is called a derived attribute. I'm not
    certain what support Cayenne currently has for derived attributes.

    Two ways you can handle it independent from Cayenne are:

    1) create a method on your ObjEntity class:

    public boolean getTooLate()
    {
    return [something based on getResolveByDate()];
    }

    2) Create a view in oracle so that the computed value of TOO_LATE
    looks like a column. For example, for a calculated current balance
    of an ACCOUNT table, I've created an ACCOUNT_BALANCE view that
    consists of ACCOUNT_NUMBER (which is also the primary key of the
    ACCOUNT table), and the computed "BALANCE_AMOUNT" value. I then made
    a DbEntity and ObjEntity AccountBalance, which was read-only, and set
    a dependent relationship between it and the Account.

    Because the account balance needs to be computed in real time each
    time it's used, rather than cached, I use a SelectQuery where
    AccountBalance.account equals the target account every time I need a
    current balance.

    You might need to use method 2) since your value is dependent on
    SYSDATE. If you were willing to switch it to depend on the system
    date of the client, you could use method 1).

    On 7/14/08, Wout wrote:
    Hello,

    I am building a complaint management system. When I'm loading a list of
    complaints, I'd like to retrieve an extra column that contains some
    information that is determined in SQL on the fly. For example, if the
    complaint table has a column to store the date by which the complaint should
    be resolved, I'd like to determine whether that date is in the past or not.

    This is what the SQL generating code could look like (the example has been
    simplified):

    StringBuilder query = new StringBuilder();
    query.append("SELECT ");
    query.append("#result('c.COMPLAINT_ID' 'Integer'
    'COMPLAINT_ID'), ");
    query.append("#result('c.RESOLVE_BY_DATE' 'Date'), ");
    query.append("CASE WHEN (SYSDATE > c.RESOLVE_BY_DATE) THEN 1 ELSE 0 END
    TOO_LATE ");

    The column "TOO_LATE" is determined on the fly. How can I retrieve this
    column?

    I've tried two things:

    SQLTemplate template = new SQLTemplate(Complaint.class, query.toString());
    DbEntity dbe =
    dataContext.getEntityResolver().getDataMap("ComplaintsMap").getDbEntity("CPL_COMPLAINT");
    Object a = dbe.getAttributeMap();
    System.out.println(a);
    if (dbe.getAttribute("TOO_LATE") == null) {
    DbAttribute dba = new DbAttribute("TOO_LATE", 4, dbe);
    dbe.addAttribute(dba);
    }
    List<Complaint> result =
    dataContext.performQuery(template);

    In this code I tried to temporarily add a 'fake' DbEntity to the DataMap
    for "TOO_LATE". The problem with this code is that Cayenne looks for
    "t0"."TOO_LATE", which doesn't exist, obviously.

    My other attempt was to retrieve DataRows, use the DataContext to create
    DataObjects and manually read and set "TOO_LATE":

    SQLTemplate template = new SQLTemplate(Complaint.class, query.toString());
    template.setFetchingDataRows(true);
    List<DataRow> dataRows =
    dataContext.performQuery(template);
    List<Complaint> result = new ArrayList<Complaint>();
    Iterator<DataRow> i = dataRows.iterator();
    while (i.hasNext()) {
    DataRow dataRow = (DataRow) i.next();
    Complaint c = (Complaint)
    dataContext.objectFromDataRow(Complaint.class, dataRow,
    false);
    c.tooLate = dataRow.get("TOO_LATE").equals(1);
    }

    The problem with this code is that somehow, the column "TOO_LATE" is not
    included in the DataRow. dataRow.get("TOO_LATE") returns null.

    I'm not very experienced in Cayenne, so perhaps I'm making a very obvious
    mistake. Can anyone offer any suggestions on how to instantiate a Complaint
    object and retrieve the "TOO_LATE" column?

    Thanks in advance!

    Best regards,

    Wout

    I'm using an Oracle database.
  • Wout at Jul 21, 2008 at 7:40 am

    Mike Kienenberger schreef:
    Wout wrote:
    I am building a complaint management system. When I'm loading a
    list of complaints, I'd like to retrieve an extra column that
    contains some information that is determined in SQL on the fly.
    2) Create a view in oracle so that the computed value of TOO_LATE
    looks like a column.

    You might need to use method 2) since your value is dependent on
    SYSDATE. If you were willing to switch it to depend on the system
    date of the client, you could use method 1).
    Thanks for the tip! I hadn't even thought of views. I'm now using views,
    because I need to be able to sort and filter my results on the extra column.

    Best regards,

    Wout

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedJul 14, '08 at 11:28a
activeJul 21, '08 at 7:40a
posts3
users2
websitecayenne.apache.org

2 users in discussion

Wout: 2 posts Mike Kienenberger: 1 post

People

Translate

site design / logo © 2022 Grokbase