How to use subquery in hibernate criteria

How to use subquery in hibernate criteria

For example, think of a Cart system where we have another table for Items. A cart can have multiple items, so here we have one to many mapping. From below example we will find all Cart which have Items quantity 5.

SQL Query : select * from Cart where Cart.cart_id in (

    select Items.cart_id from Items where Items.quantity = 5)

We can get same result by using DetachedCriteria in Criteria.

DetachedCriteria userSubquery = DetachedCriteria.forClass(Items.class, "items")

    // Filter the Subquery

    .add(Restrictions.eq(Items.quantity, 5))

    // SELECT the Cart Id  

    .setProjection(Projections.property("items.cart_id") );

And the main query:

Criteria query = session.createCriteria(Cart.class, "cart")

    .add(Subqueries.propertyIn("cart.cart_id", userSubquery) );