How does Hibernate handle JPA Criteria API literals
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
The JPA specification is like a Java interface, However, when it comes to performance, implementation details matter a lot. That’s why, even if you use the JPA standard, you still need to know how the underlying provider implements the standard specification.
For instance, if we take this tweet from Gareth Western:
JPA Critera doesn't use bind variables for literal values? really?? SMH https://t.co/R0eFaAIhdX *sigh* wish we had @JavaOOQ
— Gareth Western (@gareth) May 25, 2017
We can clearly see that there is an issue in the way literals might be handled by Hibernate when executing a Criteria API query.
Therefore, in this article, we are going to see how literals are handled by Hibernate and explain what we have changed in Hibernate 5.2.12.
Domain Model
Let’s assume we have the following JPA entity:
@Entity(name = "Book") @Table(name = "book") public class Book { @Id private Long id; private String name; @NaturalId private long isbn; //Getters and setters omitted for brevity }
For more details about the
@NaturalId
annotation, check out this article.
And, we have the following Book
entry in our database:
Book book = new Book(); book.setId(1L); book.setName("High-Performance Java Persistence"); book.setIsbn(978_9730228236L); entityManager.persist(book);
Default literal handling mode
When executing the following Criteria API query:
CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Book> cq = cb.createQuery(Book.class); Root<Book> root = cq.from(Book.class); cq.select(root); cq.where( cb.equal( root.get("isbn"), 978_9730228236L ) ); Book book = entityManager.createQuery(cq).getSingleResult(); assertEquals( "High-Performance Java Persistence", book.getName() );
Writing JPA Criteria API queries is not very easy. The Codota IDE plugin can guide you on how to write such queries, therefore increasing your productivity.
For more details about how you can use Codota to speed up the process of writing Criteria API queries, check out this article.
Hibernate generates the following SQL query:
SELECT b.id AS id1_0_, b.isbn AS isbn2_0_, b.name AS name3_0_ FROM book b WHERE b.isbn = 9789730228236
As expected, the literal value was inlined in the generated SQL query.
However, when using a String
literal:
CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Book> cq = cb.createQuery(Book.class); Root<Book> root = cq.from(Book.class); cq.select(root); cq.where( cb.equal( root.get("name"), "High-Performance Java Persistence" ) ); Book book = entityManager.createQuery(cq).getSingleResult(); assertEquals( 978_9730228236L, book.getIsbn() );
Hibernate generates the following SQL query:
SELECT b.id AS id1_0_, b.isbn AS isbn2_0_, b.name AS name3_0_ FROM book b WHERE b.name = ?
The literal is gone! Instead, we now got a PreparedStatement
bind parameter.
Now, depending on the use case, you either want to use inlined literal values or substitute them with bind parameters. The advantage of using bind parameters is that the query Parse Tree and the Execution Plan (e.g. Oracle, SQL Server) could be cached.
However, sometimes caching the Execution Plan can cause more harm than good, especially if the literal values are skewed or there is a lot of contention on the Execution Plan Cache.
For this purpose, the HHH-9576 Jira issue was created.
Confguring the literal handling mode
Since Hibernate 5.2.12, you can use the LiteralHandlingMode
to define the strategy used for handling literals in Criteria API queries. This enumeration takes three values:
AUTO
, which works exactly as you’ve just seen. Numeric values are inlined while String-based ones are substituted with bind parameters.INLINE
, which will inline both numeric and String-based values.BIND
, which will substitute both numeric and String-based literals with bind parameters.
So, if we provide the INLINE
handling mode via the application.properties
configuration file:
spring.jpa.properties.hibernate.criteria.literal_handling_mode=inline
And, rerun the String-based example, Hibernate will now execute the following SQL query:
SELECT b.id AS id1_0_, b.isbn AS isbn2_0_, b.name AS name3_0_ FROM book b WHERE name = 'High-Performance Java Persistence'
Or, if we change it to BIND
:
spring.jpa.properties.hibernate.criteria.literal_handling_mode=bind
And rerun the numeric literal example, you will now get the following SQL query:
SELECT b.id AS id1_0_, b.isbn AS isbn2_0_, b.name AS name3_0_ FROM book b WHERE b.isbn = ?
That’s it!
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Specifying the expected Criteria API literal handling mode is actually a very nice enhancement. While the default AUTO
mode might work just fine for many data access layers, in case you need to change the way literals are being handled, just provide the LiteralHandlingMode
strategy you want to use, and Hibernate will switch to using that one instead.
