Teaching is my way of learning

Primary Menu

Hibernate Facts: How to “assert” the SQL statement count

Introduction

Hibernate simplifies CRUD operations, especially when dealing with entity graphs. But any abstraction has its price and Hibernate is no different. I’ve already talked about the importance of fetching strategy and knowing your Criteria SQL queries, but there is more you can do to rule over JPA. This post is about controlling the SQL statement count that Hibernate calls on your behalf.

Before ORM tools got so popular, all database interactions were done through explicit SQL statements, and optimizations were mostly targeted towards slow queries.

Hibernate may give the false impression that you don’t need to worry about SQL statements. This is a wrong and dangerous assumption. Hibernate is supposed to ease domain model persistence, not to free you from any SQL interaction.

With Hibernate, you manage entity state transitions which are then translated to SQL statements. The number of generated SQL statements is affected by the current fetching strategy, Criteria queries or Collection mappings and you might not always get what you expected. Ignoring SQL statements is risky and it may eventually put a heavy toll on the overall application performance.

I’m a strong advocate of peer reviewing, but that’s not “sine qua non” for detecting bad Hibernate usage. Subtle changes may affect the SQL statement count and pass unnoticed through the reviewing process. Not in the least, when it comes to “guessing” the JPA SQL statements, I feel like I can use any extra help. I’m for as much automation as possible, and that’s why I came up with a mechanism for enforcing the SQL statement count expectations.

First, we need a way to intercept all executed SQL statements. I researched on this topic and I was lucky to find this great datasource-proxy library.

Adding an automated validator

This safeguard is meant to run only during the testing phase, so I’ll add it to the Integration Testing spring context exclusively. I’ve already talked about Spring bean aliasing and this is the right time to make use of it.

The new proxy data source decorates the already existing data source, intercepting all executed SQL statements. This library can log all SQL statements along with the actual parameter values, unlike the default Hibernate logging which only prints a placeholder instead.

Conclusion

Code reviewing is a fine technique, but it’s not enough on large scale development projects. That’s why automatic checking is of paramount importance. Once the test written, you are assured that no future change can break your assumptions.