How to access multiple tables in a single query using Hibernate Criteria?

Question

I have two tables to query, and so far I've come up with the following HQL-query:

From DriverEntity d 
where exists (
    From LicenceEntity l 
    where driverId = d.Id 
        and l.licenceType.id = '3'
        and l.validFrom > TO_DATE('2014-01-01', 'YYYY-MM-DD')
        and l.validFrom < TO_DATE('2014-04-17', 'YYYY-MM-DD')
        and l.validTo > TO_DATE('2014-07-02', 'YYYY-MM-DD')
        and l.validTo < TO_DATE('2095-07-12', 'YYYY-MM-DD')))

I'm querying two tables; one with licences and one with drivers. Each driver can have many licences.

The query works perfectly, but I would like to use Criteria instead to make it easier to edit when I add more search options.

What would this query look like using Criteria? I've looked into DetachedCriteria, but I don't understand it completely.

Answer 1

you can add a the subquery with help of DetachedCriteria:

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
Criteria criteria = Criteria.forClass(DriverEntity.class,"driver");
DetachedCriteria dc = DetachedCriteria.forClass(LicenceEntity.class,"licence");
dc.add(Property.forName("driver.id").eqProperty("licence.driverId"));
dc.add(Restrictions.between("validFrom", df.parse("2014-01-01"), df.parse("2014-04-17")));
dc.add(Restrictions.between("validTo", df.parse("2014-07-02"), df.parse("2095-07-12")));

criteria.add(Subqueries.exists(dc.setProjection(Projections.id())));