Hi
In our project we needed a generic way to relate many different
entities. Cayenne does not allow vertical inheritance, therefore I
had to make my way around, and create a fake relationship. I'm using
custom superclass for entities, in which accessing methods, like the
one in cayenne are implemented (getTags/addToTags/removeFromTags).
Those methods execute SelectQuery(). I had to expose the pk's , but
it works well.
The application is using derby and ROP.
the problem we have is with creating a query like:
select
$attributes
from $entity
join $joinClause
where ((id in
(
select
TAGRELATION.ENTITYRECORDID
from TAGRELATION join tag on TAGRELATION.tagid = tag.id
where ((TAGRELATION.ENTITYIDENTIFIER = $entityCode) AND (id =
$entityId))
)) AND ($qualifier))
order by $orderby
org.apache.cayenne.exp.Expression does not support that complexity,
therefore I started to use SQLTemplate, but when query like this is
executed:
SELECT #result('Room.name' 'java.lang.String'), #result('site.name'
'java.lang.String'), #result('Room.seatedCapacity'
'java.lang.Integer') FROM ROOM JOIN SITE on ROOM.siteid = SITE.id
exception is thrown:
org.apache.cayenne.CayenneRuntimeException: [v.3.0-SNAPSHOT Mar 29
2007 11:34:53] Remote error. URL - http://localhost:8181/angel-server-
cayenne; CAUSE - [v.3.0-SNAPSHOT Mar 29 2007 11:34:53] [v.$
{project.version} ${project.build.date} ${project.build.time}]
Exception processing message org.apache.cayenne.remote.QueryMessage.
Root cause: [v.${project.version} ${project.build.date} $
{project.build.time}] Error getting ResultIterator: Query Exception:
java.sql.SQLException: Column 'SITE.NAME' is either not in any table
in the FROM list or appears within a join specification and is
outside the scope of the join specification or appears in a HAVING
clause and is not in the GROUP BY list. If this is a CREATE or ALTER
TABLE statement then 'SITE.NAME' is not a column in the target table.
at org.apache.derby.client.am.SQLExceptionFactory.getSQLException
(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown
Source)
at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement
(ConnectionWrapper.java:274)
at org.apache.cayenne.conn.ConnectionWrapper.prepareStatement
(ConnectionWrapper.java:280)
at
org.apache.cayenne.access.TransactionConnectionDecorator.prepareStatemen
t(TransactionConnectionDecorator.java:179)
at org.apache.cayenne.access.jdbc.SQLTemplateAction.execute
(SQLTemplateAction.java:130)
at org.apache.cayenne.access.jdbc.SQLTemplateAction.performAction
(SQLTemplateAction.java:107)
at org.apache.cayenne.access.DataNodeQueryAction.runQuery
(DataNodeQueryAction.java:57)
at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:236)
at org.apache.cayenne.access.DataDomainLegacyQueryAction.execute
(DataDomainLegacyQueryAction.java:82)
at org.apache.cayenne.access.DataDomain$1.transform(DataDomain.java:
704)
at org.apache.cayenne.access.DataDomain.runInTransaction
(DataDomain.java:802)
at org.apache.cayenne.access.DataDomain.performQueries
(DataDomain.java:698)
at org.apache.cayenne.access.DataContext.internalPerformIteratedQuery
(DataContext.java:1261)
at org.apache.cayenne.access.DataContext.performIteratedQuery
(DataContext.java:1227)
at org.apache.cayenne.access.IncrementalFaultList.fillIn
(IncrementalFaultList.java:190)
at org.apache.cayenne.access.IncrementalFaultList.<init>
(IncrementalFaultList.java:156)
at
org.apache.cayenne.access.DataContextQueryAction.interceptPaginatedQuery
(DataContextQueryAction.java:109)
at org.apache.cayenne.access.DataContextQueryAction.execute
(DataContextQueryAction.java:54)
at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:1321)
at org.apache.cayenne.access.ClientServerChannelQueryAction.runQuery
(ClientServerChannelQueryAction.java:105)
at org.apache.cayenne.access.ClientServerChannelQueryAction.execute
(ClientServerChannelQueryAction.java:66)
at org.apache.cayenne.access.ClientServerChannel.onQuery
(ClientServerChannel.java:79)
at org.apache.cayenne.intercept.DataChannelDecorator.onQuery
(DataChannelDecorator.java:57)
at org.apache.cayenne.remote.service.DispatchHelper.dispatch
(DispatchHelper.java:40)
at org.apache.cayenne.remote.service.BaseRemoteService.processMessage
(BaseRemoteService.java:151)
When the same query is executed directly in derby there are no
problems, so either:
- cayenne does not handle joins in SQLTemplate correctly (is http://
issues.apache.org/cayenne/browse/CAY-552 related ?)
- I'm doing something wrong
Also, was my idea to use SQLTemplate right ? I have tried to use
Expression, but I could not find a way to encode the query I need. Is
there any better way to archive what I'm after ?
Cheers
Marcin
