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.

Introduction

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:

• cleaner: without query strings and their concatenations spread everywhere
• safer: with compile-time type checks and fewer runtime errors
• easier to port on different databases (if everything is handled correctly)

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.

<dependency> 
   <groupId>org.hibernate</groupId> 
   <artifactId>hibernate-jpamodelgen</artifactId>
   <version>5.3.6.Final</version> 
   <optional>true</optional> 
</dependency>

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

Hibernate meta-model class example

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.

# 2 Prefer JPA over the other magic stuff

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:

• Query Methods

public interface AuthorRepository extends CrudRepository<Author, Long> {

  List<Author> findByNameAndCountry(String name, String country);

}

• Query by Example

Author author = new Author();                          author.setCountry("Italy");                            Example<Author> example = Example.of(author);
Iterable<Author> italianAuthors = repository.findAll(example);

• JPA 2 Criteria API and Specification

public class AuthorSpecs {
   public static Specification<Author> hasCountry(String country){
      return (root,query,cb) -> 
         root.get(Author_.name).equal(country);  
   }
   public static Specification<Author> writesGenre(String genre){
      return (root,query,cb) -> 
         root.get(Author_.name)
            .join(Author_.books)
            .get(Book_.genre)
            .equal(genre);  
   }
}
List<Author> sciFiItalianAuthors = repository.findAll(
   (root, query.cb)->
      cb.and(
         AuthorSpecs.hasCountry("Italy"),
         AuthorSpecs.writesGenre("SciFi")
      )
);

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.

# 3 Query Hints and Hibernate Dialects

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:

Hibernate dialect with custom hints

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

• takes a Specification and create a JPA Criteria query
• performs the count query
• extracts the ordering from the pageable object
• adds the custom query hint to the Hibernate Query

Custom repository with query hints

If you liked this post and you want to stay updated on the news, follow us on LinkedIn!