…and why you shouldn’t!
👋 Introduction
When working with an ORM, it is essential that you inspect your generated queries. Often when doing so, you will find they are not entirely what you expected. The CriteriaBuilder API can be very useful for building dynamic queries, but you must take great care – especially if you let your UI generate many wonderful queries to fulfil all of its filtering/sorting/etc.
If you aren’t aware of what queries are being generated by your application, perhaps this post will show you that you should be – and also how easy it can be to get wrong.
🕵🏻♂️ Criteria Query
The problem
Below is an innocent looking CriteriaQuery; Find all Cars that are in a Garage, filtering to those matching the user input of searchTerm – in this case “Browns”.
String searchTerm = "Browns";
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Car> query = cb.createQuery(Car.class);
Root<Car> carRoot = query.from(Car.class);
query.select(carRoot)
.where(cb.and(
cb.equal(carRoot.get("garage").get("name"), searchTerm),
cb.equal(carRoot.get("make"), make)
));
return entityManager.createQuery(query)
.getResultList();
Can you spot the issue? Let’s take a look at the generated query;
select car0_.id as id1_0_,
car0_.garage_id as garage_i4_0_,
car0_.make as make2_0_,
car0_.model as model3_0_
from car car0_
cross join car_garage cargarage1_
where car0_.garage_id=cargarage1_.id and
cargarage1_.name=? and
car0_.make=?
I was expecting an INNER JOIN based on the code, but instead a CROSS JOIN was generated instead. This join type produces a cartesian product between all Car entities and all Garage entities – this is very inefficient.
The fix
Luckily, these can easily be updated like so;
String searchTerm = "Browns";
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Car> query = cb.createQuery(Car.class);
Root<Car> carRoot = query.from(Car.class);
query.select(carRoot)
.where(cb.and(
cb.equal(carRoot.join("garage").get("name"), searchTerm),
cb.equal(carRoot.get("make"), make)
));
return entityManager.createQuery(query)
.getResultList();
Note this use of .join("garage") instead of .get("garage")!
With the updated code, the generated query is now like so;
select car0_.id as id1_0_,
car0_.garage_id as garage_i4_0_,
car0_.make as make2_0_,
car0_.model as model3_0_
from car car0_
inner join car_garage cargarage1_ on car0_.garage_id=cargarage1_.id
where cargarage1_.name=? and
car0_.make=?
Easy, right?
🏎 Conclusion
In this simple example, you can see how easy it is to neglect queries that are generated by your application – and how they can have a crippling effect on your application performance. Often when using the Criteria API to generate queries, the requirement is a lot more complicated than this example – so it is crucial that you are always aware of what is being generated by your application.
