Removing SQL from your Spring Boot code

In this post, I want to share some personal notes about keeping a Spring Boot App as much as SQL agnostic as possible. Using Spring Data and Hibernate as the reference framework, I’ll discuss a couple of tips: dealing with static metadata, composing type-safe queries, and how to handle custom query hints with a hibernate dialect extension. The objective is to keep the code clean and avoid native queries while dealing with complexity and performance issues.

The aim of ORM, in general, is to simplify database programming. This is made by hiding database details ad providing a more programmer-friendly abstraction. Furthermore, Spring Data JPA provides several techniques to express both statically and dynamically composed queries. With an optimistic perspective, this will result in a better code:

As usual, there are some trade-offs: this simplification hides the details and can bring you to degraded performance as well as the impossibility to leverage specific database features with ease. The fun comes when the problems arise together: when the only way to solve the performance issue you have, is with a database-specific hint to be put in your query.

In the next paragraphs, I’ll discuss three bullet points that in my opinion help in keeping your code cleaner, safer and ready for unexpected accidents that might occur once in production.

Magic strings are bad. And that becomes worse when they are repeatedly spread all over your code. Hibernate Metamodel Generator is an annotation processor that helps in gathering all the metadata regarding your JPA Entities and generating static meta-model classes.

These classes are useful to generate type-safe queries and can work as holders of database-related constants.

The snippet above is an example of a meta-model class for a generic Author entity. It consists of a list of string constants and a list of JPA2 compliant metadata. The last ones are useful to generate type-safe Criteria queries as discussed in the next paragraph.

When things become complex Spring Data JPA offers several solutions to make your life comfortable. Among the ones that avoid query strings, three main techniques can be sorted by the level of the complexity they can handle:

While the first two options work on the domain model by reflection, the Criteria API allows you to build the JPQL query tree. This is done by mean of Java methods and thus type-safety is guaranteed even in dynamic query compositions. Criteria API is a lower level choice on which the other options are built upon. Unfortunately, it can become easily verbose even with Spring Data helpers, but this is the price to pay if we stick to avoid query strings. On the other side, it becomes a lifesaver when you need to access the lower level API offered by Hibernate and at the same time keep a type-safe query abstraction.

It happens rarely but databases can completely miss the query plan. In such cases, hints are the solution to the problem. Unfortunately, hints are usually an addition to the SQL standard and often are vendor dependent. If the specific hint you need isn’t supported, the only option is to modify the actual query, but it can take days to replace the code written on Spring Data and JPA just to add a keyword! In such cases, it is possible to extend the Hibernate dialect in use to intercept the generated SQL query and implement custom query hints.

The following naive example gives an idea of how to add a straight join hint for MySQL. First, we define a dialect extension that can apply the custom query hint to the native query:

Then we can extend a Spring Data repository with a custom implementation that:

Related posts:

Prior to the 2018 NHL trade deadline, the Rangers sent out a letter to its fans stating its intention to rebuild its roster with younger players. A little more than two weeks later, the team traded…

Blue and Pink; such simple concepts that somehow represent so much. For ages, in some way or another, one’s gender has determined one’s path in life; often represented by the colors blue for boys and…

the company that is kept affects us deep down we should be uplifted, encouraged, heartened, missed when absent not everyone is worth our company worth attention choose with self-compassion