Writing dynamic SQL queries using Spring Data JPA repositories and EntityManager

1. OVERVIEW

You would need to write dynamic SQL queries for instance, if you need to implement a RESTful endpoint like:

/api/films?category=Action&category=Comedy&category=Horror&minRentalRate=0.5&maxRentalRate=4.99&releaseYear=2006

where the request parameters category, minRentalRate, maxRentalRate, and releaseYear might be optional.

The resulting SQL query’s WHERE clause or even the number of table joins change based on the user input.

One option to write dynamic SQL queries in your Spring Data JPA repositories is to use Spring Data JPA Specification and Criteria API.

But Criteria queries are hard to read and write, specially complex queries. You might have tried to come up with the SQL query and reverse-engineer it to implement it using the Criteria API.

There are other options to write dynamic SQL or JPQL queries using Spring Data JPA.

Tip: Read on to learn how to write dynamic SQL queries using Spring Data JPA repositories and Querydsl.

This tutorial teaches you how to extend Spring Data JPA for your repositories to access the EntityManager so that you can write dynamic native SQL or JPQL queries.

Let’s start with a partial ER diagram for the db_dvdrental relational database:

Spring Data JPA - EntityManager - Database tables

and the subject JPA entities, which you can generate using hibernate3-maven-plugin:

Tip: You might want to take a look at the recent Code and Configuration Snippets / Recipes / Tips section, which helps you with common tasks without reading through a full blog post.

Film.java:

@Entity
@Table(name = "film", schema = "public")
@SqlResultSetMapping(
  name = "FilmDtoMapping",
  classes = {
    @ConstructorResult(
      targetClass = FilmDto.class,
      columns = {
        @ColumnResult(name = "f_film_id"),
        @ColumnResult(name = "l_name"),
        @ColumnResult(name = "title"),
        @ColumnResult(name = "description", type = String.class),
        @ColumnResult(name = "rental_rate"),
        @ColumnResult(name = "rental_duration"),
        @ColumnResult(name = "length"),
        @ColumnResult(name = "release_year", type = String.class)
      }
    )
  }
)
public class Film implements Serializable {
// ...
  @Column(name = "rental_rate", nullable = false, precision = 4)
  private BigDecimal rentalRate;

  @Column(name = "release_year")
  @Lob
  private String releaseYear;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "language_id", nullable = false)
  private Language language;

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "film")
  private Set<FilmCategory> filmCategories = new HashSet<FilmCategory>(0);
// ...
}

Language.java:

@Entity
@Table(name = "language", schema = "public")
public class Language implements Serializable {
// ...
  @Column(name = "name", nullable = false, length = 20)
  private String name;

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "language")
  private Set<Film> films = new HashSet<Film>(0);
// ...
}

FilmCategory.java:

@Entity
@Table(name = "film_category", schema = "public")
public class FilmCategory implements Serializable {
// ...
  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "film_id", nullable = false, insertable = false, updatable = false)
  private Film film;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "category_id", nullable = false, insertable = false, updatable = false)
  private Category category;
// ...
}

Category.java:

@Entity
@Table(name = "category", schema = "public")
public class Category implements Serializable {
// ...
  @Column(name = "name", nullable = false, length = 25)
  private String name;

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "category")
  private Set<FilmCategory> filmCategories;
// ...
}

Let’s now extend Spring Data JPA so that you can use JPA’s EntityManager in your @Repository-annotated interfaces.

2. EXTENDING SPRING DATA JPA

Inspired by Spring Framework’s TransactionTemplate, RestTemplate, JmsTemplate, I’ll define this callback interface first.

QueryCallback.java:

@FunctionalInterface
public interface QueryCallback<T> {

  T doWithEntityManager(EntityManager entityManager);
}

This is the interface your Spring Data JPA-based repositories would need to implement to access the EntityManager and execute native SQL or JPQL queries. It’s similar to Spring’s TransactionCallback.

QueryCallbackWithoutResult.java:

public abstract class QueryCallbackWithoutResult<T> implements QueryCallback<T> {

  @Override
  public T doWithEntityManager(EntityManager entityManager) {
    doWithEntityManagerWithoutResult(entityManager);
    return null;
  }

  protected abstract void doWithEntityManagerWithoutResult(EntityManager entityManager);
}

This is convenience implementation to help you execute queries that don’t return rows or scalar values. It’s similar to Spring’s TransactionCallbackWithoutResult.

Next, let’s define a custom Spring Data JPA repository base interface.

AsimioJpaRepository.java:

@NoRepositoryBean
public interface AsimioJpaRepository {

  <T> List<T> findAll(QueryCallback<List<T>> callback);
}

This is the interface or one of the interfaces your Spring Data JPA repositories would need to extend from.

I’ll be explaining how to retrieve a list of entities to keep this blog post short.

Note:
If you need to write dynamic queries to retrieve a single JPA entity, you would need to implement:
<T> T findOne(QueryCallback<T> callback);

If you need to write dynamic queries with pagination support, you would need to implement:
<T> Page<T> findAll(Pageable pageable, QueryCallback<Page<T>> callback);

@NoRepositoryBean prevents AsimioJpaRepository intermediate interface from being proxied. It’s used when providing a base interface with new methods for your repositories along with the custom base repository implementation.

Let’s implement this interface next.

AsimioSimpleJpaRepository.java:

public class AsimioSimpleJpaRepository<E, ID extends Serializable> extends SimpleJpaRepository<E, ID>
    implements AsimioJpaRepository {

  private final EntityManager entityManager;

  @Override
  public <T> List<T> findAll(QueryCallback<List<T>> callback) {
    return callback.doWithEntityManager(this.entityManager);
  }
// ...
}

The EntityManager attribute is passed in, and set in the AsimioSimpleJpaRepository constructor.

All the findAll() does is to execute the callback method for the caller to use the EntityManager in any possible way.

You could write and execute native queries, JPQL queries, unwrap the Hibernate Session, to name a few.

3. SPRING DATA JPA REPOSITORIES

To use the EntityManager, your Spring Data JPA repositories would now look like:

@Repository
public interface XyzRepository extends JpaRepository<Xyz, Long>, AsimioJpaRepository {

  default List<Xyz> findAll(SomeSearchCriteria criteria) {  // Or a different signature. It doesn't matter.
    return this.findAll(em -> {
      // Do something with the entity manager
      // Like:
      // Query query = em.createNativeQuery("A native query");
      // Or:
      // TypedQuery<Xyz> query = em.createQuery("A JPQL query", Xyz.class);
      // Or:
      // Session session = em.unwrap(Session.class);
      // ...
      return query.getResultList();
    });
  }
}

Java 8+ is required because it uses default interface methods.

For a specific example, let’s implement the Film repository and write a dynamic, native SQL query to retrieve Films depending on the presence of search criteria attributes.

FilmDao.java:

@Repository
public interface FilmDao extends JpaRepository<Film, Integer>, AsimioJpaRepository {

  default List<FilmDto> findAll(FilmSearchCriteria criteria) {
    return this.findAll(new QueryCallback<List<FilmDto>>() {

      @SuppressWarnings("unchecked")
      @Override
      public List<FilmDto> doWithEntityManager(EntityManager entityManager) {
        Map<String, Object> queryParams = this.buildQueryParameters();

        StringBuilder builder = new StringBuilder();
        // SELECT
        builder.append("SELECT " + System.lineSeparator());
        builder.append("  f.film_id as f_film_id, f.title, f.description, " + System.lineSeparator());
        builder.append("  f.length, " + System.lineSeparator());
        builder.append("  f.release_year, f.rental_duration, f.rental_rate, " + System.lineSeparator());

        builder.append("  l.name as l_name " + System.lineSeparator());

        // FROM
        builder.append("FROM public.film f " + System.lineSeparator());
        if (queryParams.get("categories") != null) {
          builder.append("  INNER JOIN public.film_category fc ON f.film_id = fc.film_id " + System.lineSeparator());
          builder.append("  INNER JOIN public.category c ON fc.category_id = c.category_id " + System.lineSeparator());
        }
        builder.append("  LEFT OUTER JOIN public.language l ON f.language_id = l.language_id " + System.lineSeparator());

        // WHERE
        builder.append("WHERE 1 = 1 " + System.lineSeparator());
        if (queryParams.get("minRate") != null) {
          builder.append("  AND f.rental_rate between :minRate AND :maxRate " + System.lineSeparator());
        }
        if (queryParams.get("relYear") != null) {
          builder.append("  AND f.release_year = :relYear " + System.lineSeparator());
        }
        if (CollectionUtils.isNotEmpty(criteria.getCategories())) {
          builder.append("  AND c.name IN (:categories) " + System.lineSeparator());
        }

        // Create query
        String nativeQuery = builder.toString();
        Query query = entityManager.createNativeQuery(nativeQuery, "FilmDtoMapping");

        // Set query parameters
        if (queryParams.get("categories") != null) {
          query.setParameter("categories", queryParams.get("categories"));
        }
        if (queryParams.get("minRate") != null) {
          query.setParameter("minRate", queryParams.get("minRate"));
          query.setParameter("maxRate", queryParams.get("maxRate"));
        }
        if (queryParams.get("relYear") != null) {
          query.setParameter("relYear", queryParams.get("relYear"));
        }

        return query.getResultList();
      }

      private Map<String, Object> buildQueryParameters() {
        Map<String, Object> result = Maps.newHashMap();
        if (CollectionUtils.isNotEmpty(criteria.getCategories())) {
          result.put("categories", criteria.getCategories());
        }
        if (ObjectUtils.allNotNull(criteria.getMinRentalRate(), criteria.getMaxRentalRate())) {
          result.put("minRate", criteria.getMinRentalRate());
          result.put("maxRate", criteria.getMaxRentalRate());
        }
        if (criteria.getReleaseYear() != null) {
          result.put("relYear", criteria.getReleaseYear());
        }
        return result;
      }
    });
  }
}

@Repository-annotated interface extends from AsimioJpaRepository interface. It allows you to execute findAll() method passing an implementation of the QueryCallback interface, either using Lambda or an anonymous inner implementation.
The callback implementation builds a dynamic, native query depending on the presence of FilmSearchCriteria attributes.
The WHERE clause might include a film rental rate condition, a release year condition or even extra JOINs to the film_category and category tables to filter films based on categories.
Includes a ResultSet mapping name set to FilmDtoMapping that maps Film JPA entities to FilmDto objects (Data Transfer Object). It matches @SqlResultSetMapping annotation’s name value. Source code available in a Git repo.

Note: Stay tuned and sign up to the newsletter. I might cover how to implement Spring Data JPA Projections in another blog post.

You still need to let know Spring Data JPA to use your custom repository class: AsimioSimpleJpaRepository.

Application.java:

@SpringBootApplication
@EnableJpaRepositories(
  repositoryBaseClass = AsimioSimpleJpaRepository.class
)
@EnableTransactionManagement
public class Application {
// ...
}

4. SERVICE CLASS

@Service
@Transactional(readOnly = true)
public class DefaultDvdRentalService implements DvdRentalService {

  private final FilmDao filmDao;

  @Override
  public List<FilmDto> retrieveFilms(FilmSearchCriteria searchCriteria) {
    return this.filmDao.findAll(searchCriteria);
  }
}

The search criteria argument instantiated in the REST Controller from the request parameters is passed through to the Film repository method.

FilmSearchCriteria.java:

public class FilmSearchCriteria {

  private BigDecimal minRentalRate;
  private BigDecimal maxRentalRate;
  private Long releaseYear;
  private Set<String> categories;

  // Getters, Setters
}

FilmSearchCriteria is a wrapper class to hold the request parameters passed in the request endpoint.

5. DYNAMIC QUERIES IN ACTION

Let’s send a couple of GET requests to the /api/films endpoint and check the Hibernate logs:

curl "http://localhost:8080/api/films?minRentalRate=0.99&maxRentalRate=4.99"

Hibernate: 
  SELECT
    f.film_id as f_film_id,
    f.title,
    f.description,
    f.length,
    f.release_year,
    f.rental_duration,
    f.rental_rate,
    l.name as l_name  
  FROM
    public.film f    
  LEFT OUTER JOIN
    public.language l 
      ON f.language_id = l.language_id  
  WHERE
    1 = 1    
    AND f.rental_rate between ? AND ?  
2021-07-21 15:44:05.167 TRACE 34537 --- [io-8080-exec-10] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [NUMERIC] - [0.99]
2021-07-21 15:44:05.167 TRACE 34537 --- [io-8080-exec-10] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [NUMERIC] - [4.99]

This request included the minRentalRate and maxRentalRate parameters only.

Notice it added only one condition to the WHERE clause:

AND f.rental_rate between ? AND ? 

Let’s send another request with all the supported parameters:

curl "http://localhost:8080/api/films/?category=Action&category=Comedy&category=Horror&minRentalRate=0.99&maxRentalRate=4.99&releaseYear=2005"

Hibernate: 
  SELECT
    f.film_id as f_film_id,
    f.title,
    f.description,
    f.length,
    f.release_year,
    f.rental_duration,
    f.rental_rate,
    l.name as l_name  
  FROM
    public.film f    
  INNER JOIN
    public.film_category fc 
      ON f.film_id = fc.film_id    
  INNER JOIN
    public.category c 
      ON fc.category_id = c.category_id    
  LEFT OUTER JOIN
    public.language l 
      ON f.language_id = l.language_id  
  WHERE
    1 = 1    
    AND f.rental_rate between ? AND ?    
    AND f.release_year = ?    
    AND c.name IN (
      ?, ?, ?
    )  
2021-07-21 15:45:43.539 TRACE 34537 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [NUMERIC] - [0.99]
2021-07-21 15:45:43.540 TRACE 34537 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [NUMERIC] - [4.99]
2021-07-21 15:45:43.540 TRACE 34537 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [2005]
2021-07-21 15:45:43.540 TRACE 34537 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [Action]
2021-07-21 15:45:43.540 TRACE 34537 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [VARCHAR] - [Comedy]
2021-07-21 15:45:43.540 TRACE 34537 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [VARCHAR] - [Horror]

Notice that in addition to the rental rate condition, it also includes two JOINs, and release year, and category name conditions that were not included in the previous query.

  INNER JOIN
    public.film_category fc 
      ON f.film_id = fc.film_id    
  INNER JOIN
    public.category c 
      ON fc.category_id = c.category_id    
...
    AND f.rental_rate between ? AND ?    
    AND f.release_year = ?    
    AND c.name IN (
      ?, ?, ?
    )  

6. CONCLUSION

This blog post covered how to to write dynamic native SQL or JPQL queries in your Spring Data JPA repositories using the EntityManager.

In addition to writing dynamic SQL queries using Spring Data JPA Specification and Criteria and writing dynamic SQL queries using Spring Data JPA repositories and Querydsl, you can also extend Spring Data JPA to access JPA’s EntityManager or Hibernate’s Session to write dynamic queries.

Thanks for reading and as always, feedback is very much appreciated. If you found this post helpful and would like to receive updates when content like this gets published, sign up to the newsletter.