Hibernate Criteria Subqueries: Exists

While working on a recent project, I came into a situation where I needed to do an “exists” query, using a Criteria-style query. The online documentation for this feature is a little sparse, so I thought I’d share what I did.

The Pizza-shop Data Model (Again)

I keep reusing a data model for a Pizza shop in my posts, and this post will be no different. This data model first appeared in my JPA mapping tutorial. Here’s an ERD of the model again:

Find me Orders with Small Pizzas!

Given this model, what if we needed to find each order that contained a small pizza? Suppose your database had the following data:

As with [my earlier posting][1], the object model has a PizzaOrder class that contains a Set of Pizza objects which correspond to each customer order. Your first inclination might be to do a criteria-within-a-criteria, like this:

Criteria criteria = Criteria.forClass(PizzaOrder.class);
criteria.createCriteria("pizza").add("pizza_size_id",1);
List
 ordersWithOneSmallPizza = criteria.list();

You’d be in for a bit of a surprise, though. While you might expect only two Pizza orders to be returned (namely, orders #1 and #2), you’ll actually have three orders in the result set; because order #2 has two small pizzas in it, order #2 will appear twice in your results!

The reason why this happens is pretty simple, and it becomes clear if you enable Hibernate’s SQL output feature. To locate all of the pizza orders which contain a small pizza, Hibernate needs to do an inner join to the PIZZA table. This is true regardless of whether you’ve mapped the Pizza objects to be fetched lazily; the join is required because of your query criteria, not because of your mappings. Note: it’d be really nice if Hibernate were clever enough to identify from the result set that it had duplicate PIZZA_ORDER records, and build the Set of Pizza objects accordingly, but I suspect that this would be a very difficult thing to do, so I’m not holding my breath.

The Right Way to Do It
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!
[1]: http://mikedesjardins.net/posts/2008-01-61-new-jpa-tutorial-pizza-shop.html