CODEWITHSUNDEEP
javadatabasehibernatejoincriteria
mindas
mindas

Reputation: 26713

Criteria joining two tables using more than one parameter

I have two tables which are related:

+-----------+           +----------+
|   First   |  *     1  |  Second  |
+-----------+ --------- +----------+
| fk_Second |           | id       |
| version   |           | ...      |
| ...       |           | y        |
| x         |           +----------+
+-----------+

Hibernate has a ManyToOne definition from First to Second. The {fk_Second, version} is a composite-id of the First table (although I don't think it's relevant in this case).

I am trying to write Criteria call, which in SQL would look like as:

SELECT * FROM First WHERE 
   First.fk_Second = Second.id AND 
   First.x = Second.y

I'm finding trouble in generating the last bit - the extra join condition.

Criteria c = session.createCriteria(First.class);
   .createCriteria("second", "join_between_first_and_second")
   .add(Restrictions.eqProperty("y", "x")   // <- fails: "x" is not found

I can not use HQL queries in this situation. Is there any way writing this differently? Can this be written avoiding subquery/DetachedCriteria?

Upvotes: 0

Views: 5760

Answers (2)

JB Nizet
JB Nizet

Reputation: 691795

Criteria c = session.createCriteria(First.class, "first");
c.createAlias("first.second", "theSecond");
c.add(Restrictions.eqProperty("first.x", "theSecond.y");

If you don't prepend an alias to your property, the property is considered part of the root entity of the criteria (First in this case).

Upvotes: 1

manurajhada
manurajhada

Reputation: 5380

Try HQL 'With' clause..

SELECT f.* FROM First f left join Second s ON f.fk_Second = s.id with f.x = s.y;

Upvotes: 0

Related Questions