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')?

Dinosaurian

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

Graduation

Can you please edit your post to include the relevant bits so the answer still has value when the link goes down? – Puerilism

Property.forName("pizza.pizza_order_id").eqProperty("pizzaOrder.pizza_order_id") pizza_order_id is the class property name or table column name? – Denominationalism

Hibernate 5.0.7 I cant see method sizeCriteria.add(String,int) – Denominationalism