Hibernate query with subquery in WHERE clause and multiple joins

Question

I have been trying to get Hibernate to generate me a query with a subquery in its where clause. I've used this answer as a base to help me going, but this question mentioned only one table.

However, this is what I would need (in SQL):

SELECT [...]
FROM a
LEFT OUTER JOIN b on a.idb = b.idb
LEFT OUTER JOIN c on b.idc = c.idc
[...]
LEFT OUTER JOIN k out on j.idk = k.idk
WHERE k.date = (SELECT max(date) from k in where in.idk = out.idk) OR k.date is null

As I am not very used to using Hibernate, I'm having trouble specifying these inner joins while navigating in the inner constraints. I was able to re-create the initial criteria as in the linked answer, but I can't seem to join the criteria and the rootCriteria.

score 0 · Accepted Answer · answered Oct 16 '17 at 14:03

If the entities are properly joined with @ManyToOne annotations, simply joining the criteria to the previous table will be enough to propagate the criteria to the whole query.

The following code seems to work properly to add the WHERE clause I'm looking for.

DetachedCriteria kSubquery = DetachedCriteria.forClass(TableJPE.class,"j2");
kSubQuery = kSubQuery.createAlias("k","k2");
kSubQuery.setProjection(Projections.max("k2.date"));
kSubQuery = kSubQuery.add(Restrictions.eqProperty("j.id", "j2.id"));
rootCriteria.add(Restrictions.disjunction()
     .add(Subqueries.propertyEq("k.date",kSubQuery))
     .add(Restrictions.isNull("k.date")));

Hibernate query with subquery in WHERE clause and multiple joins

1 Answers1