Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of mkienenb@gmail.com designates
 209.85.162.180 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:date:from:to:subject:in-reply-to:mime-version
         :content-type:content-transfer-encoding:content-disposition
         :references;
        b=Kzf7oQCjFvCtYD7uwtQQakPUreTOzvaC4E/p9BEEzty2y1WSUeCJAx0XMyJ0JKkAPa
         HT5wEBeA1RX7iII27FFEKp6p+EBzpnE9zJ6q4yeJLIxZB/7pKy0JsajdXhDuXGpRch5j
         7TdzYU6hsv3i7BFc1+v+dSPIM2/AQITdk1t1Q=
Message-ID: <8f985b960807140812v7de4370eo14a0a72cd5014ab0@mail.gmail.com>
Date: Mon, 14 Jul 2008 11:12:49 -0400
From: "Mike Kienenberger" <mkienenb@gmail.com>
To: user@cayenne.apache.org
Subject: Re: Retrieve extra, generated column when loading objects
In-Reply-To: <487B382A.8080302@tsi-solutions.nl>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <487B382A.8080302@tsi-solutions.nl>

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 <w.maaskant@tsi-solutions.nl> 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.
>