How to intercept and modify SQL queries with the Hibernate StatementInspector
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
A very useful, yet lesser-known, Hibernate feature is the ability to intercept and modify any auto-generated SQL statement using the Hibernate StatementInspector
utility.
In this article, we are going to see how the Hibernate StatementInspector
mechanism works.
StatementInspector
The Hibernate StatementInspector
is a functional interface that looks as follows:
The inspect
method takes an SQL statement that’s about to be executed by Hibernate and allows you to modify the SQL statement and return it to the Hibernate StatementPreparer
.
To register an implementation of the StatementInspector
interface with Hibernate, you can use the hibernate.session_factory.statement_inspector
configuration property which can take a StatementInspector
Java object, a Class
or a String
object defining the class implementing the StatementInspector
interface.
Logging and modifying Hibernate SQL statements
To better identify the SQL statements generated by Hibernate, we can use enable the hibernate.use_sql_comments
configuration property:
<property name="hibernate.use_sql_comments" value="true" />
This way, when persisting a Book
entity:
entityManager.persist( new Book() .setIsbn("978-9730228236") .setTitle("High-Performance Java Persistence") .setAuthor("Vlad Mihalcea") );
Hibernate generates the following SQL statement:
/* insert com.vladmihalcea.book.hpjp.hibernate.logging.inspector.Book */ INSERT INTO book ( author, isbn, title, id ) VALUES ( 'Vlad Mihalcea', '978-9730228236', 'High-Performance Java Persistence', 1 )
Notice the SQL comment which tells you that the INSERT statement is associated with the Book
entity persist operation.
When fetching the Book
entity by its natural identifier:
Book book = entityManager .unwrap(Session.class) .bySimpleNaturalId(Book.class) .load("978-9730228236");
Hibernate generates the following SQL statements:
/* get current natural-id -> entity-id state com.vladmihalcea.book.hpjp.hibernate.logging.inspector.Book */ SELECT book_.id as id1_0_ FROM book book_ WHERE book_.isbn = '978-9730228236' SELECT book0_.id as id1_0_0_, book0_.author as author2_0_0_, book0_.isbn as isbn3_0_0_, book0_.title as title4_0_0_ FROM book book0_ WHERE book0_.id = 1
The first SQL SELECT statement is for resolving the entity identifier based on the provided natural identifier as explained by the associated SQL comment.
The second query is for fetching the Book
entity based on the resolved entity identifier.
While the SQL comments can provide useful context for auto-generated SQL queries, the comment goes to the database server, therefore increasing the network bandwidth and interfering with the SQL statement caching mechanism.
For this reason, we’d like to log the SQL statement along with the Hibernate-specific context while making sure the SQL comment is being removed prior to executing the SQL statement.
Logging and altering SQL statements with StatementInspector
The following StatementInspector
implementation allows us to log the SQL statement along with the Hibernate-specific context while also stripping out the SQL comment from the statement prior to returning it.
public class SqlCommentStatementInspector implements StatementInspector { private static final Logger LOGGER = LoggerFactory .getLogger( SqlCommentStatementInspector.class ); private static final Pattern SQL_COMMENT_PATTERN = Pattern .compile( "\\/\\*.*?\\*\\/\\s*" ); @Override public String inspect(String sql) { LOGGER.debug( "Executing SQL query: {}", sql ); return SQL_COMMENT_PATTERN .matcher(sql) .replaceAll(""); } }
The SqlCommentStatementInspector
can be provided to Hibernate via the hibernate.session_factory.statement_inspector
configuration property.
<property name="hibernate.session_factory.statement_inspector" value="com.vladmihalcea.book.hpjp.hibernate.logging.inspector.SqlCommentStatementInspector" />
Now, when persisting the Book
entity, Hibernate generates the following log entry and executes the SQL statement without the SQL comment:
-- Executing SQL query: /* insert com.vladmihalcea.book.hpjp.hibernate.logging.inspector.Book */ insert into book (author, isbn, title, id) values (?, ?, ?, ?) Query:["insert into book (author, isbn, title, id) values (?, ?, ?, ?)"], Params:[(Vlad Mihalcea, 978-9730228236, High-Performance Java Persistence, 1)]
And, the same goes for fetching the Book
entity by its natural identifier:
-- Executing SQL query: /* get current natural-id->entity-id state com.vladmihalcea.book.hpjp.hibernate.logging.inspector.Book */ select book_.id as id1_0_ from book book_ where book_.isbn=? Query:["select book_.id as id1_0_ from book book_ where book_.isbn=?"], Params:[(978-9730228236)] -- Executing SQL query: select book0_.id as id1_0_0_, book0_.author as author2_0_0_, book0_.isbn as isbn3_0_0_, book0_.title as title4_0_0_ from book book0_ where book0_.id=? Query:["select book0_.id as id1_0_0_, book0_.author as author2_0_0_, book0_.isbn as isbn3_0_0_, book0_.title as title4_0_0_ from book book0_ where book0_.id=?"], Params:[(1)]
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
The StatementInspector
is a very powerful mechanism that allows you to intercept all the Hibernate SQL statements and decide whether you want to alter the statements prior to being executed.
