2

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.

Eamonn
  • 53
  • 4

1 Answers1

1

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.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • 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. http://stackoverflow.com/questions/3001136/hibernate-query-for-multiple-items-in-a-collection – Eamonn Apr 21 '11 at 08:49
  • The possible solutions proposed in the answers I linked to suffer from the following exception: org.hibernate.QueryException: duplicate association path – Eamonn Apr 21 '11 at 10:01