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.
