Hi,
I am having problems doing queries with joins. Maybe it's because I'm
using ojb0.9.2. I really must upgrade soon. I have been reading the
section on joins in the documentation here:
http://db.apache.org/ojb/query.html, but this may not apply to ojb0.9.2
and I can't find anything in the mailing list archives.
The problem is when I use QueryByCriteria to perform a query that joins
2 tables, I get some unexpected sql generated.
I am doing a query by crieria, selecting objects from one table
(reference) by a single value in a column of an associated table
(external_entity). There is a 1-M association between
external_entity:reference. Here is the snippet of java that performs the
query and the class descriptors from the repository.xml for the two
relevant classes:
String databaseId = "A1234";
Criteria crit = new Criteria();
crit.addEqualTo("ExternalEntity.database_id", databaseId );
Query query = new QueryByCriteria( Reference.class, crit );
Collection col = broker.getCollectionByQuery(query);
<!-- Definitions for uk.ac.ebi.integr8.data.Reference-->
<class-descriptor
class="uk.ac.ebi.integr8.data.Reference"
table="REFERENCE"
>
<field-descriptor id="1"
name="referenceId"
column="REFERENCE_ID"
jdbc-type="BIGINT"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="externalEntityId"
column="EXTERNAL_ENTITY_ID"
jdbc-type="BIGINT"
/>
<reference-descriptor
name="externalEntity"
class-ref="uk.ac.ebi.integr8.data.ExternalEntity"
>
<foreignkey field-id-ref="2"/>
</reference-descriptor>
</class-descriptor>
<!-- Definitions for uk.ac.ebi.integr8.data.ExternalEntity -->
<class-descriptor
class="uk.ac.ebi.integr8.data.ExternalEntity"
table="EXTERNAL_ENTITY"
>
<field-descriptor id="1"
name="externalEntityId"
column="EXTERNAL_ENTITY_ID"
jdbc-type="BIGINT"
primarykey="true"
autoincrement="true"
/>
<field-descriptor id="2"
name="db_id"
column="DATABASE_ID"
jdbc-type="VARCHAR"
/>
<collection-descriptor
name="references"
element-class-ref="uk.ac.ebi.integr8.data.Reference"
auto-retrieve="false"
auto-update="false"
auto-delete="false"
proxy="false"
>
<inverse-foreignkey field-id-ref="2"/>
</collection-descriptor>
</class-descriptor>
The query that is generated by ojb is:
SELECT A0.REFERENCE_ID,A0.EXTERNAL_ENTITY_ID FROM REFERENCE A0 WHERE
ExternalEntity.database_id = ?
But I was hoping for a query of this form to be generated, i.e. ojb
would perform the join based on the foreign key information in the
repository.xml:
SELECT A0.REFERENCE_ID,A0.EXTERNAL_ENTITY_ID FROM REFERENCE A0,
EXTERNAL_ENTITY EE WHERE EE.EXTERNAL_ENTITY_ID = AO.EXTERNAL_ENTITY_ID
AND EE.database_id = ?
Any help would be greatly appreciated,
Many thanks
Lorna
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
