Detached queries and subqueries by R4R Team

The DetachedCriteria class allows to create a query outside the scope of a session and then execute it using an arbitrary Session.

DetachedCriteria query = DetachedCriteria.forClass(Student.class)

    .add( Property.forName("sex").eq('M') );

Session session = new Configuration().configure().buildSessionFactory();

Transaction txn = session.beginTransaction();

List results = query.getExecutableCriteria(session).setMaxResults(100).list();

txn.commit();

session.close();

A DetachedCriteria can also be used to express a subquery. Criterion instances involving subqueries can be obtained via Subqueries or Property.

DetachedCriteria avgWeight = DetachedCriteria.forClass(Student.class)

    .setProjection( Property.forName("weight").avg() );

    session.createCriteria(Student.class)

    .add( Property.forName("weight").gt(avgWeight) )

    .list();

DetachedCriteria weights = DetachedCriteria.forClass(Student.class)

    .setProjection( Property.forName("weight") );

    session.createCriteria(Student.class)

    .add( Subqueries.geAll("weight", weights) )

    .list();

Correlated subqueries are also possible:

DetachedCriteria avgWeightForSex = DetachedCriteria.forClass(Student.class, "student2")

    .setProjection( Property.forName("weight").avg() )

    .add( Property.forName("student2.sex").eqProperty("student.sex") );

     session.createCriteria(Student.class, "student")

    .add( Property.forName("weight").gt(avgWeightForSex) )

    .list();