Join on 2 tables via Criteria api

rotrived · **Joined:** Wed Apr 11, 2007 3:16 pm **Posts:** 5

Is it possible to do a join on 2 tables that are related by foreign key only using the Criteria api?

I see that the Criteria api allows us to do a join for an association, but if there isn't an association between 2 tables, how do I do a join using this api?

Thanks in advance,
Roopa

amila733 · **Posted:** Wed Jun 13, 2007 1:12 am

Hi

send more detail about your requirment

Amila

rotrived · **Joined:** Wed Apr 11, 2007 3:16 pm **Posts:** 5

Here is the mapping for the first table

<hibernate-mapping>
<class name="AssignmentQueue" table="ASSIGNMENTQUEUE">
<id name="assignmentQueueId" type="java.lang.Long">
....
</id>
<version column="CHANGESTAMP" name="changeStamp" type="java.lang.Integer"/>
<property name="ouId" type="java.lang.Long">
<column name="ouid" not-null="true" />
</property>
<property name="incomingAddrId" type="java.lang.Long">
<column name="INCOMINGADDRID" not-null="true" />
</property>

The incomingAddrId refers to the id from another table, whose mapping is below:
<hibernate-mapping>
<class name="IncomingAddr" table="INCOMINGADDR">
<id name="incomingAddrId" type="java.lang.Long">
...
</id>
...
<property name="address" type="java.lang.String">
<column name="ADDRESS" not-null="true" />
</property>
...

What I need to do is using the Criteria API, do a join between the two tables so I can get the ouId from AssignmentQueue table and address from the IncomingAddr table.

Can this be done?

Thanks,
Roopa

jits_1998 · **Posted:** Wed Jun 13, 2007 4:13 pm

First things first, its not a good idea to have this type of none-referential integrity in your app.

But it can be achieved by this hql :

Code:

select queue,addr from AssignmentQueue queue,IncomingAddr addr where (queue.ouId = addr.incomingAddrId)

Regards,
Jitendra

pramodkp · **Joined:** Mon Mar 26, 2007 12:38 am **Posts:** 119

Hi,
>>> but if there isn't an association between 2 tables, how do I do a join using this api?

In 2007 Edition of "Java Persistence With Hibernate" by Christian Bauer & Gavin King,
It is stated that Only joining of associated entities is supported with Criteria api.

This feature is expected ( likely ) in later versions of Hibernate.

But, I don't know if it is available already.

christian · **Posted:** Thu Jun 14, 2007 1:38 am

jits_1998 wrote:

First things first, its not a good idea to have this type of none-referential integrity in your app.

But it can be achieved by this hql :

Code:

select queue,addr from AssignmentQueue queue,IncomingAddr addr where (queue.ouId = addr.incomingAddrId)

Regards,
Jitendra

It's just a theta style inner join, which is a perfectly good idea in any application and it has also nothing to do with referential integrity. That is the job of foreign key constraints, which obviously are present regardless of what query is executed.

jits_1998 · **Posted:** Thu Jun 14, 2007 2:50 am

Hi Christian,

According to the hibernate mappings above, there does not appear to be any foreign key relationship in the two tables, unless it is created directly on the db. That is what i meant is not a good idea, having a mismatch in application and db.

Please guide me to correct views on this.

Apart from this, the query will do the job i think ..

Regards,
Jitendra

christian · **Posted:** Thu Jun 14, 2007 2:53 am

Yes, sorry I missed the mapping. That is indeed an extremely bad idea.

jits_1998 · **Posted:** Thu Jun 14, 2007 3:20 am

Thanks Christian.

I think I can get the credit now. Can I? I am very eager to open my account of credits :-). Did some interesting work in hibernate and am trying to get involved :-).

cheers!
Jitendra