Hi I have been trying to implement following query using
Hibernate Criteria APIs but cannot find the way to do it.

Select table.* from Table table where table.field1 in (
select table1.field2 from Table table2 where table2.field3 = value ).

To summarize it is a case of SELF-JOIN.

Help is appreciated.

Thanks....

Regards,

Hemant

The Hibernate3 Criteria API should make your queries easier, not tougher. :)

What are the names of your classes? Remember, with the Criteria API, we think in terms of objects, not databases.

Here's a sweet little Criteria API tutorial to get you in the mood. Maybe it can help you out:

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=09howtousethecriteriaapi

Hi! All,

With some research I found the way to implement self join using
criteria API.

The key is to use DetachedCriteria, for the same object and
set the projection for the join field.

The code may looks like as follows;

DetachedCriteria dc = DetachedCriteria.forClass(Table.class);
dc.add(Restrictions.eq("field1", value1);
dc.setProjection(Property.forName("field2"));
Criteria c = l_session.createCriteria(Table.class);

c.add(Property.forName("field2").in(dc));
c.list();


Thanks....

Hemant

Wow! Great follow up post!

Thanks for the insight, Hemant.

-Cameron McKenzie

That was really good one helped me solving my problem.


Thanks

I realize this is an old post, but I really need help with returning column from the table in the detached criteria.
Using your example, let's say Table.class has a column called Field3. and you would like to add that column as projection in criteria c, such that:

criteria.add(Property.forName("field2").in(dc));
criteria.setProjection(Projections.add(Projections.property(dc's field3 column)));

Been trying different things and no luck :(

Thanks