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?"

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.