hibernate criteria with exists clause

Question

I cannot find a solution to a problem that seems to be easy. Say there are 2 entity classes:

class A {    Set<B> bs; }  class B {    String text; } 

How to create a criteria query that returns all A's that contains at least one B entity which fulfills a given condition (like b.text = 'condition')?

Answer 1

I think this link can be useful: http://mikedesjardins.net/2008/09/22/hibernate-criteria-subqueries-exists/

It contains the following example about how create n exists criteria:

"What you’re really trying to do is to obtain all Pizza Orders where an associated small pizza exists. In other words, the SQL query that you’re trying to emulate is

SELECT *   FROM PIZZA_ORDER  WHERE EXISTS (SELECT 1                  FROM PIZZA                 WHERE PIZZA.pizza_size_id = 1                   AND PIZZA.pizza_order_id = PIZZA_ORDER.pizza_order_id) 

The way that you do that is by using an “exists” Subquery, like this:

Criteria criteria = Criteria.forClass(PizzaOrder.class,"pizzaOrder"); DetachedCriteria sizeCriteria = DetachedCriteria.forClass(Pizza.class,"pizza"); sizeCriteria.add("pizza_size_id",1); sizeCriteria.add(Property.forName("pizza.pizza_order_id").eqProperty("pizzaOrder.pizza_order_id")); criteria.add(Subqueries.exists(sizeCriteria.setProjection(Projections.property("pizza.id")))); List<pizzaOrder> ordersWithOneSmallPizza = criteria.list(); 

And voila, the result will contain two PizzaOrders!"