Writing a Hibernate Criteria API Query with restrictions for multiple sub-elements

Question

I have a data model that looks like this (simplified example):

public class Address { private List<AddressLine> addressLines; } public class AddressLine { private String value; private String type; }

I am trying to use the Criteria API to search for Addresses in the database that contain specific combinations of AddressLines. For example, to retrieve all addresses that contain the address lines {(type="CITY", value="London"), (type="COUNTRY", value="GB")}. I haven't been able to find any examples of such a query.

As far as I have been able to get is to query for an Address based on a single AddressLine.

session.createCriteria(Address.class) .createCriteria("addressLines") .add(Restrictions.and(Restrictions.eq("type", type), Restrictions.eq("value", value))).list()

If I add a restriction for a second address lines the SQL that hibernate generates is basically asking SELECT x WHERE x.y = 'a' AND x.y = 'b' so will never return any results.

I have found similar questions being asked before but none of them have an accepted or voted for answer.

JB Nizet · Accepted Answer · 2011-04-20 20:24:52Z

You need to write the Criteria equivalent of

select a from Address a where exists (select line1.id from AddressLine line1 where line1.address.id = a.id and line1.type = 'CITY' and line1.value = 'London') and exists (select line2.id from AddressLine line where line2.address.id = a.id and line2.type = 'COUNTRY' and line2.value = 'GB')

This means writing a DetachedCriteria for each subquery, with an id projection, and using these detached criterias as argument of two Subqueries.exists() calls. The alias of the address entity in the main criteria can be used in the detached criterias to implement the line1.address.id = a.id restriction.

I am not sure how to turn that into Criteria (I am new to Hibernate). I'll study the Docs a bit more today. I also found this question that is very similar. It does not answer the question but has some code snippets that will help implement your solution. However, those code snippets don't show the exists() call. stackoverflow.com/questions/3001136/…
The possible solutions proposed in the answers I linked to suffer from the following exception: org.hibernate.QueryException: duplicate association path

Collectives™ on Stack Overflow

Writing a Hibernate Criteria API Query with restrictions for multiple sub-elements

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related