Is It Possible Add Joins To QueryByCriteria?

[Danilo Tommasina <dt...@risksys.com>]

Hello,

We are having following problem:

Table TEMP {
   attr1, attr2, attr3
}

Table A {
   attr1
   (...)
}

Table B {
   attr1
   (...)
}

Table C {
   attr1
   (...)
}

There are no foreign key contraints between TEMP and the A,B,C tables however 'attr1' in all tables refer to the same 'logical object'
We have QueryByCriteria objects for returning entities of type A, B or C however we need to do some filtering according to the entries in table TEMP.
Currently we are using a IN-criteria with sub-selects, ex.
SELECT (...) FROM A where (...) AND A.attr1 IN ( SELECT attr1 FROM TEMP WHERE attr2 = X AND attr3 = Y  )

The above query is not optimal, a better way would be to use a join, in SQL we could do an inner join between A and TEMP tables, ex.
SELECT (...) FROM A INNER JOIN TEMP ON A.attr1 = TEMP.attr1 WHERE (...) AND TEMP.attr2 = X AND TEMP.attr3 = Y

Since there is no foreign key constraint and no references in the repository.xml we cannot use the standard Criteria objects, since there is no known 
relationship from tables A,B,C to table TEMP in OJB.
I know there is a QueryBySql, however the (...) part in the WHERE clause is very complex and completely dynamically generated, so a QueryBySql is a big no-no 
for us.

My question is: Is it somehow possible to force the inclusion of a join between tables in OJB using a QueryByCriteria ?
Any ideas?
Adding the references in the repository.xml or in real-time may also be problematic since we do not want to materialise TEMP entities within A,B or C entities.

thanks for support
Danilo

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org