About Vlad Mihalcea

Vlad Mihalcea is a software architect passionate about software integration, high scalability and concurrency challenges.

Hibernate Facts: Always check Criteria API SQL queries

Criteria API is very useful for dynamically building queries, but that’s the only use case where I’d use it. Whenever you have an UI with N filters that may arrive in any M combinations, it makes sense to have an API to construct queries dynamically, since concatenating strings is always a path I’m running away from.

The question is, are you aware of the SQL queries your Criteria API generates behind the scenes? I’ve been reviewing many such queries lately, and I’ve been struck by how easy it is to get it wrong.

Let’s start from the following entities diagram:
 
 
product

So we have a Product with a ToOne association to a WareHouseProductInfo and a ToMany association to an Image entity.

Now let’s start with this Criteria API query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> productRoot = query.from(Product.class);

query.select(productRoot)
	 .where(cb.and(cb.equal(productRoot.get(Product_.code), "tvCode"), 
				   cb.gt(productRoot.get(Product_.warehouseProductInfo)
.get(WarehouseProductInfo_.quantity), 50)));
Product product = entityManager.createQuery(query).getSingleResult();

Can you spot any issue with this previous query? Let’s check the generated SQL:

SELECT product0_.id          AS id1_14_,
       product0_.code        AS code2_14_,
       product0_.company_id  AS company_5_14_,
       product0_.importer_id AS importer6_14_,
       product0_.name        AS name3_14_,
       product0_.version     AS version4_14_
FROM   product product0_
       CROSS JOIN warehouseproductinfo warehousep1_
WHERE  product0_.id = warehousep1_.id
       AND product0_.code = ?
       AND warehousep1_.quantity > 50

I was expecting an INNER JOIN and I got a CROSS JOIN instead. The Cartesian Product is very inefficient, and that’s what you get if you forget to properly join the associations you’re interested in filtering with on your where clauses. So, writing Criteria API is not a walk in the park after all.

Luckily, this example can be fixed as follows:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> productRoot = query.from(Product.class);
Join<Product, WarehouseProductInfo> warehouseProductInfoJoin = productRoot.join(Product_.warehouseProductInfo);

query.select(productRoot)
	 .where(cb.and(cb.equal(productRoot.get(Product_.code), "tvCode"),
				   cb.gt(warehouseProductInfoJoin.get(WarehouseProductInfo_.quantity), 50)));
Product product = entityManager.createQuery(query).getSingleResult();

which yields the expected SQL query:

SELECT product0_.id          AS id1_14_,
       product0_.code        AS code2_14_,
       product0_.company_id  AS company_5_14_,
       product0_.importer_id AS importer6_14_,
       product0_.name        AS name3_14_,
       product0_.version     AS version4_14_
FROM   product product0_
       INNER JOIN warehouseproductinfo warehousep1_
               ON product0_.id = warehousep1_.id
WHERE  product0_.code = ?
       AND warehousep1_.quantity > 50

So, be careful with how you define your joins in Criteria API. Now, let’s compare the previous Criteria API query to its JPAQL counterpart:

Product product = entityManager.createQuery(
	"select p " +
	"from Product p " +
	"inner join p.warehouseProductInfo w " +
	"where " +
	"   p.code = :code and " +
	"   w.quantity > :quantity ", Product.class)
.setParameter("code", "tvCode")
.setParameter("quantity", 50)
.getSingleResult();

I’ve always found the JPAQL more descriptive than Criteria API, but there are projects where the Criteria API is the default JPA querying mechanism, so it’s not only employed for dynamic filters queries, but even for the ones with fixed where clauses.

Well, you can eventually achieve the same results, but while I can predict the SQL query out of a JPAQL one, when it comes to Criteria API I am completely clueless. Whenever I review a Criteria query, I always have to run an Integration Test to check the outputted SQL, for small changes can really make big differences.

Even if Criteria API usage is imposed, you can still work around it, considering you are extra careful and you review all your queries.

Now let’s get back to one of the most exotic joining subterfuge (yet sub-optimal) Criteria query I happened to bumped into recently. If you work on a large project with many developers, you’ll inevitably run into this type of constructs. That’s one extra reason why I prefer JPAQL over Criteria API. With JPAQL you can’t get it so off-the-rails like in the following example:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> product = query.from(Product.class);
query.select(product);
query.distinct(true);

List<Predicate> criteria = new ArrayList<Predicate>();
criteria.add(cb.like(cb.lower(product.get(Product_.name)), "%tv%"));

Subquery<Long> subQuery = query.subquery(Long.class);
Root<Image> infoRoot = subQuery.from(Image.class);
Join<Image, Product> productJoin = infoRoot.join("product");
subQuery.select(productJoin.<Long>get(Product_.id));

subQuery.where(cb.gt(infoRoot.get(Image_.index), 0));
criteria.add(cb.in(product.get(Product_.id)).value(subQuery));
query.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
return entityManager.createQuery(query).getResultList();

I find these types of queries too difficult to parse through viewing them only, but there is a sub-select smelling like trouble, so let’s see the generated SQL query:

SELECT DISTINCT product0_.id          AS id1_14_,
                product0_.code        AS code2_14_,
                product0_.company_id  AS company_5_14_,
                product0_.importer_id AS importer6_14_,
                product0_.name        AS name3_14_,
                product0_.version     AS version4_14_
FROM   product product0_
WHERE  ( Lower(product0_.name) LIKE ? )
       AND ( product0_.id IN (SELECT product2_.id
                              FROM   image image1_
                                     INNER JOIN product product2_
                                             ON image1_.product_id =
                                                product2_.id
                              WHERE  image1_.index > 0) )

While some use-cases call for a SQL sub-query, here it’s just completely unnecessary and it only slows your query down. But this time we were actually requiring a dynamic filtering query, so JPAQL was out of question. The only way to fix it was to write the proper Criteria query.

So here it is, after a refactoring:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Image> imageRoot = query.from(Image.class);
Join<Image, Product> productJoin = imageRoot.join("product");
query.select(productJoin);
query.distinct(true);
List<Predicate> criteria = new ArrayList<Predicate>();
criteria.add(cb.like(cb.lower(productJoin.get(Product_.name)), "%tv%"));
criteria.add(cb.gt(imageRoot.get(Image_.index), 0));
query.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
return entityManager.createQuery(query).getResultList();

And now our SQL query looks much better:

SELECT DISTINCT product1_.id          AS id1_14_,
                product1_.code        AS code2_14_,
                product1_.company_id  AS company_5_14_,
                product1_.importer_id AS importer6_14_,
                product1_.name        AS name3_14_,
                product1_.version     AS version4_14_
FROM   image image0_
       INNER JOIN product product1_
               ON image0_.product_id = product1_.id
WHERE  ( Lower(product1_.name) LIKE ? )
       AND image0_.index > 0

I’d reasoned on why the developer would have chosen the sub-query in this particular context, and I believed it was because he hadn’t known that he could project a different entity than the Root one, in a similar fashion with a JPAQL query.

Now let’s do a DTO projection, since there are times when we don’t need fetching entire Entities, but just enough info to serve our business needs. This time we will create the following query:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<ImageProductDTO> query = cb.createQuery(ImageProductDTO.class);
Root<Image> imageRoot = query.from(Image.class);
Join<Image, Product> productJoin = imageRoot.join(Image_.product);
query.distinct(true);
List<Predicate> criteria = new ArrayList<Predicate>();
criteria.add(cb.like(cb.lower(productJoin.get(Product_.name)), "%tv%"));
criteria.add(cb.gt(imageRoot.get(Image_.index), 0));
query.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
query.select(cb.construct(ImageProductDTO.class, imageRoot.get(Image_.name), productJoin.get(Product_.name)))
		.orderBy(cb.asc(imageRoot.get(Image_.name)));
return entityManager.createQuery(query).getResultList();

Generating a clean SQL:

SELECT DISTINCT image0_.name   AS col_0_0_,
                product1_.name AS col_1_0_
FROM   image image0_
       INNER JOIN product product1_
               ON image0_.product_id = product1_.id
WHERE  ( Lower(product1_.name) LIKE ? )
       AND image0_.index > 0
ORDER  BY image0_.name ASC

But check the previous Criteria Query to how JOOQ builds such a query:

jooqContext
.select(IMAGE.NAME, PRODUCT.NAME)
.from(IMAGE)
.join(PRODUCT).on(IMAGE.PRODUCT_ID.equal(PRODUCT.ID))
.where(PRODUCT.NAME.likeIgnoreCase("%tv%"))
	.and(IMAGE.INDEX.greaterThan(0))
.orderBy(IMAGE.NAME.asc())
.fetch().into(ImageProductDTO.class);

This is way more readable, you don’t really have to guess what’s the output SQL query, and it even generates binding parameters, that I found extremely valuable:

SELECT "PUBLIC"."image"."name",
       "PUBLIC"."product"."name"
FROM   "PUBLIC"."image"
       JOIN "PUBLIC"."product"
         ON "PUBLIC"."image"."product_id" = "PUBLIC"."product"."id"
WHERE  ( Lower("PUBLIC"."product"."name") LIKE Lower('%tv%')
         AND "PUBLIC"."image"."index" > 0 )
ORDER  BY "PUBLIC"."image"."name" ASC

Conclusion

The first case I showed you is one of the very first mistakes I did while trying to learn the Criteria API. I discovered that I have to be extra caution when writing such queries, since you can easily get unexpected SQL queries.

If you’ve chosen to use Criteria API for all your queries, then you might be interested in checking JOOQ too. Even if you choose JPAQL, whenever you want to build advanced dynamic filtered queries, JOOQ can better help you with that.

You will still be using a fluent API, you won’t write any String and you will get more SQL features than what Hibernate currently offers. So, whenever your use cases don’t require querying for managed Entities, you can use JOOQ instead. I like it because I can predict the generated SQL much better than with Criteria API, and when an API is easier to use, there are less “surprises” waiting to “wow” you.

Code available on GitHub.
 

Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


5 × four =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close