Querying ManyToMany relationship with Hibernate Criteria

Question

I'm not sure how to describe this problem, so I think an example is the best way to ask my question:

I have two tables with a manyToMany relationship:

DriversLicence <-> LicenceClass

LicenceClass is things like "Car", "Motorbike", and "Medium Rigid".

Using Hibernate Criteria, how can I find all licences that have both "Car" and "Motorbike" LicenceClasses?

UPDATE 12/11/2008 I have discovered that this can easily be achieved by using a custom ResultTransformer. However the problem is that a result transformer only gets applied AFTER the query returns its results, it does not actually become part of the SQL. So I guess my question is now "Can you do what I initially described in SQL - and is there a Hibernate Criteria analog?"

Mosasaur

Answer 1

Here's how I finally achieved it using HQL:

public List<DriversLicence> findDriversLicencesWith(List<LicenceClass> licenceClasses) {
    String hqlString = "select dl from DriversLicenceImpl dl where 1=1 ";
    for (int i = 0; i < licenceClasses.size(); i++) {
        hqlString += " and :licenceClass" + i + " = some elements(dl.licenceClasses)";
    }

    Query query = getSession().createQuery(hqlString);
    for (int i = 0; i < licenceClasses.size(); i++) {
        query.setParameter("licenceClass" + i, licenceClasses.get(i));
    }
    return query.list();
}

Or using Hibernate Criteria with an sqlRestriction:

for (LicenceClass licenceClass : licenceClasses) {               
    criteria.add(Restrictions.sqlRestriction("? = some(select " + LicenceClass.PRIMARY_KEY + " from " +
                    LICENCE_CLASS_JOIN_TABLE + "  where {alias}." +
                    DriversLicence.PRIMARY_KEY + " = " + DriversLicence.PRIMARY_KEY + ")",
                    licenceClass.getId(), Hibernate.LONG));
}

LICENCE_CLASS_JOIN_TABLE is the name of the table that hibernate generates to support the many-to-many relationship between driversLicence and LicenceClass.

Cabstand

Answer 2

You can still use dot notation to work across the relations. For example, assuming you have a DriversLicence.licenceClass property and LicenceClass.type property, then:

session.createCriteria(DriversLicence.class)
   .add(Expression.or(
     Expression.eq("licenceClass.type", "Car"),
     Expression.eq("licenceClass.type", "Motorbike")
   )
).list();

Personally though, I'd simply avoid using criteria in this case because it's not a dynamic query, but instead use:

session.createQuery("from DriversLicence where licenceClass.type in (:types)")
  .setParameterList("types", myListOfTypes)
  .list();

Intelligibility

I don't think this going to work. I want to find all licences that have both "Car" and "Motorbike" LicenceClasses. Not either. – Mosasaur

Ahh... good point. While not the most efficient, you could: select dl from DriversLicence dl join d.licenceType lt where "Car" = some elements(lt.type) and "Motorbike" = some elements(lt.type) This can also be changed to use criteria (out of characters!) – Intelligibility

Yes that's the sort of thing I want - but how do you do it with criteria? Post another answer if you like! – Mosasaur

Answer 3

I had a similar issue but fixed up using HQL, I have a class "Enterprise" that is related to class "User" and also related to class "Role", they hay a many to many relationship, when I need all the enterprises related to a specific user I do the following;

Select e from Enterprise As e inner join e.Users As u inner join u.Roles As r 
Where u.UserCode=?

I suppose that in your case you should do something like;

Select dl from LicenceClass As l inner join l.DriversLicences As dl
Where 
l.LicenseClass.Name = ? OR 
l.LicenseClass.Name=? OR 
l.LicenseClass.Name=?

Hope it helps.

Kristof

Answer 4

Another option is to chain joins (one join per each LicenseClass). I used criteria builder and predicates like this

 List<Predicate> predicates = new ArrayList<>();
 for(Integer lcId : licenceClassIdList) {
     SetJoin<DriversLicence, LicenceClass> dlClasses = dlRoot.join(DriversLicence_.licenceClasses);
     predicates.add(builder.equal(dlClasses.get(LicenseClass_.id), lcId));
 }
 Predicate predicate = builder.and(predicates.toArray(new Predicate[predicates.size()]));

Notice dlRoot is object of Root class and you can get it from CriteriaQuery class. Resulted predicate is what are you looking for...

Orthopsychiatry

Answer 5

'I don't think this going to work. I want to find all licences that have both "Car" and "Motorbike" '

User Expression.and(....) instead of Expression.or(....) in the snippet provided by Nick

Tyre

Nope that is incorrect too. I'm starting to think this seemingly simple use case is just not possible in hibernate - would anyone like to confirm that and explain why? – Mosasaur