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:

spring.jpa.properties.hibernate.use_sql_comments=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.

spring.jpa.properties.hibernate.session_factory.statement_inspector=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.

Insert details about how the information is going to be processed

DOWNLOAD NOW