1. OVERVIEW

Let’s say you need to implement a RESTful endpoint where some or all of the request parameters are optional.

An example of such endpoint looks like:

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

Let’s also assume you need to retrieve the data from a relational database.

Processing these requests will translate to dynamic SQL queries, helping you to avoid writing a specific repository method for each use case. This would be error-prone and doesn’t scale as the number of request parameters increases.

In addition to:

you could also write dynamic queries using Spring Data JPA and Querydsl.

Querydsl is a framework that helps writing type-safe queries on top of JPA and other backend technologies, using a fluent API.

Spring Data JPA provides support for your repositories to use Querydsl via the QuerydslJpaPredicateExecutor fragment.

These are some of the methods this repository fragment provides:

findOne(Predicate predicate)
findAll(Predicate predicate)
findAll(Predicate predicate, Pageable pageable)

and more.

You can combine multiple Querydsl Predicates, which generates dynamic WHERE clause conditions.

But I didn’t find support to generate a dynamic number of JOIN clauses. Adding unneeded JOIN clauses to your SQL queries will impact the performance of your Spring Boot application or database.

This blog post covers how to extend Spring Data JPA for your repositories to access Querydsl objects so that you can write dynamic SQL queries.

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

Spring Data JPA - Querydsl - Database tables

It helps us to write or generate our domain model, but first let’s discuss the Maven dependencies.

2. MAVEN DEPENDENCIES

pom.xml:

...
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-jpa</artifactId>
</dependency>
<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-apt</artifactId>
  <scope>provided</scope>
</dependency>
...
<profiles>
  <profile>
  <id>querydsl-q-classes</id>
    <activation>
      <activeByDefault>false</activeByDefault>
    </activation>
    <build>
      <plugins>
        <plugin>
          <groupId>com.mysema.maven</groupId>
          <artifactId>apt-maven-plugin</artifactId>
          <version>1.1.3</version>
          <executions>
            <execution>
              <phase>generate-sources</phase>
              <goals>
                <goal>process</goal>
              </goals>
              <configuration>
                <outputDirectory>target/generated-sources/java</outputDirectory>
                <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
              </configuration>
            </execution>
          </executions>
        </plugin>
      </plugins>
    </build>
  </profile>
</profiles>
...

  • querydsl-jpa, spring-boot-starter-data-jpa are used together in your Spring Boot applications to implement CRUD against a relation database. spring-boot-starter-data-jpa provides QuerydslJpaPredicateExecutor, a repository fragment based on querydsl-jpa’s Querydsl.

  • querydsl-apt, apt-maven-plugin are used to generate the Q-types from the JPA entities.

3. JPA ENTITIES

You can use the hibernate3-maven-plugin to generate the JPA entities.

Film.java:

@Entity
@Table(name = "film", schema = "public")
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;
// ...
}

These are the JPA entities relevant to this post with the attributes that we will search on.

Notice the @OneToMany, @ManyToOne entity associations are LAZY loaded.

4. QUERYDSL Q-CLASSES

The pom file includes a Maven profile to generate the Q-classes using this command:

mvn clean generate-sources -Pquerydsl-q-classes

ls target/generated-sources/java/com/asimio/demo/domain/Q*
target/generated-sources/java/com/asimio/demo/domain/QLanguage.java
target/generated-sources/java/com/asimio/demo/domain/QCategory.java
target/generated-sources/java/com/asimio/demo/domain/QFilm.java
target/generated-sources/java/com/asimio/demo/domain/QFilmCategory.java
...

You can now move the Q-classes from target/generated-sources/java/ to src/main/java/.

QFilm.java:

public class QFilm extends EntityPathBase<Film> {
// ...
  public static final QFilm film = new QFilm("film");

  public final SetPath<FilmCategory, QFilmCategory> filmCategories = this.<FilmCategory, QFilmCategory>createSet("filmCategories", FilmCategory.class, QFilmCategory.class, PathInits.DIRECT2);

  public final QLanguage language;

  public final StringPath releaseYear = createString("releaseYear");

  public final NumberPath<java.math.BigDecimal> rentalRate = createNumber("rentalRate", java.math.BigDecimal.class);
// ...
}

QLanguage.java:

public class QLanguage extends EntityPathBase<Language> {
// ...
  public static final QLanguage language = new QLanguage("language");

  public final SetPath<Film, QFilm> films = this.<Film, QFilm>createSet("films", Film.class, QFilm.class, PathInits.DIRECT2);

  public final StringPath name = createString("name");
// ...
}

QFilmCategory and QCategory Q-types are similar. Source code available in a Git repo.

They help to write type-safe queries.

Let’s now extend Spring Data JPA so that you can use a Querydsl, or JPAQueryFactory instance, in your @Repository-annotated interfaces.

5. EXTENDING SPRING DATA JPA

Influenced by Spring Framework’s Template design pattern implementations such as TransactionTemplate, which uses TransactionCallback; let’s define a callback interface first.

JPAQueryFactoryCallback.java:

@FunctionalInterface
public interface JPAQueryFactoryCallback<T> {

    JPAQuery<T> doWithJPAQueryFactory(JPAQueryFactory queryFactory);
}

This is the interface your Spring Data JPA-based repositories would need to implement to access a JPAQueryFactory instance to build Querydsl Predicates and execute JPAQuery-based queries.

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

AsimioQuerydslRepository.java:

@NoRepositoryBean
public interface AsimioQuerydslRepository<E> extends QuerydslPredicateExecutor<E> {

      Page<E> findAll(Pageable pageable, JPAQueryFactoryCallback<E> callback);
// ...
}

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

I’ll cover only Querydsl pagination to keep this blog post short.

@NoRepositoryBean prevents AsimioQuerydslRepository 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.

AsimioQuerydslJpaRepositoryImpl.java:

public class AsimioQuerydslJpaRepositoryImpl<E, ID extends Serializable> extends QuerydslJpaPredicateExecutor<E>
    implements AsimioQuerydslRepository<E> {

  private final Querydsl querydsl;
  private final JPAQueryFactory queryFactory;
  private final CrudMethodMetadata metadata;

  public AsimioQuerydslJpaRepositoryImpl(JpaEntityInformation<E, ID> entityInformation, EntityManager entityManager,
      EntityPathResolver resolver, CrudMethodMetadata metadata) {

    super(entityInformation, entityManager, resolver, metadata);

    EntityPath<E> path = resolver.createPath(entityInformation.getJavaType());
    PathBuilder<E> builder = new PathBuilder<>(path.getType(), path.getMetadata());
    this.querydsl = new Querydsl(entityManager, builder);
    this.queryFactory = new JPAQueryFactory(entityManager);
    this.metadata = metadata;
  }

  @Override
  public Page<E> findAll(Pageable pageable, JPAQueryFactoryCallback<E> callback) {
    JPAQuery<E> jpaQuery = callback.doWithJPAQueryFactory(this.queryFactory);
    return this.findPage(jpaQuery, pageable);
  }

  private Page<E> findPage(AbstractJPAQuery<E, ?> jpaQuery, Pageable pageable) {
    JPQLQuery<E> query = this.querydsl.applyPagination(pageable, jpaQuery);
    return PageableExecutionUtils.getPage(query.fetch(), pageable, query::fetchCount);
  }
// ...
}
  • querydsl attribute is used for pagination purposes.
  • queryFactory attribute is passed through the callback implementation for a @Repository method to create dynamic JPAQuery queries.
  • findAll() method first executes the callback for a @Repository to create a JPAQuery-based query. It then applies pagination.

6. SPRING DATA JPA REPOSITORIES

Let’s implement the Film repository and write a dynamic Querydsl query to retrieve Films using pagination based on the presence of search criteria attributes.

FilmRepository.java:

@Repository
public interface FilmRepository extends JpaRepository<Film, Integer>, AsimioQuerydslRepository<Film> {

  default Page<Film> findAll(FilmSearchCriteria searchCriteria, Pageable pageable) {
    return this.findAll(pageable, queryFactory ->            
      new FilmQueryBuilder(queryFactory).build(searchCriteria)
    );
  }
}

  • Requires Java 8+ because it uses default interface methods.

  • @Repository-annotated interface extends from AsimioQuerydslRepository interface. It allows you to execute findAll() method passing an implementation of the JPAQueryFactoryCallback interface, either using Lambda or an anonymous inner implementation.

  • The callback implementation delegates building dynamic Querydsl JPAQuery queries to FilmQueryBuilder, and discussed next.

FilmQueryBuilder.java:

public class FilmQueryBuilder {

  private final JPAQueryFactory queryFactory;

  public JPAQuery<Film> build(FilmSearchCriteria searchCriteria) {
    JPAQuery<Film> filmQuery = this.queryFactory
      .select(QFilm.film)
      .from(QFilm.film)
      .innerJoin(QFilm.film.filmActors, QFilmActor.filmActor).fetchJoin()
      .innerJoin(QFilmActor.filmActor.actor, QActor.actor).fetchJoin()
      .innerJoin(QFilm.film.language, QLanguage.language).fetchJoin();

    this.addCategoryJoin(filmQuery, searchCriteria.getCategories());

    Predicate rentalRateBetween = this.rentalRateBetween(searchCriteria.getMinRentalRate(), searchCriteria.getMaxRentalRate());
    Predicate releaseYearEqualTo = this.releaseYearEqualTo(searchCriteria.getReleaseYear());

    // Same as ExpressionUtils.and(rentalRateBetween, releaseYearEqualTo)
    Predicate where = ExpressionUtils.allOf(rentalRateBetween, releaseYearEqualTo);
    return filmQuery.where(where);
  }

  private Predicate rentalRateBetween(Optional<BigDecimal> minRate, Optional<BigDecimal> maxRate) {
    return Optionals.mapIfAllPresent(minRate, maxRate,
      (min, max) -> QFilm.film.rentalRate.between(min, max))
    .orElseGet(() -> null);        
  }

  private Predicate releaseYearEqualTo(Optional<Long> releaseYear) {
    return releaseYear.map(relYear -> QFilm.film.releaseYear.eq(String.valueOf(relYear))).orElseGet(() -> null);
  }

  private void addCategoryJoin(JPQLQuery<Film> filmQuery, Set<String> categories) {
    if (CollectionUtils.isEmpty(categories)) {
      return;
    }
    filmQuery
      .innerJoin(QFilm.film.filmCategories, QFilmCategory.filmCategory)
      .innerJoin(QFilmCategory.filmCategory.category, QCategory.category)
        .on(QCategory.category.name.in(categories));
  }
}

You first have a common select(), from() and innerJoin() clauses added to the query, no matter what the FilmSearchCriteria attributes’ values are.

It also adds the rentalRateBetween() Querydsl predicate when both values are not null. Same for the releaseYearEqualTo() predicate. But that’s how the vanilla QuerydslPredicateExecutor behaves.

The difference is that addCategoryJoin() method might add an extra couple of JOIN clauses when the categories argument is not empty. I don’t think you can accomplish adding a dynamic number of JOIN clauses with the existing QuerydslJpaPredicateExecutor’s findAll(Predicate predicate) method.

An extra couple of SQL JOIN clauses might have a performance impact on your database and Spring Boot application.

6.1. CONFIGURATION

You still need to configure your Spring Boot application to use the custom Querydsl Spring Data JPA repository class AsimioQuerydslJpaRepositoryImpl.

@EnableJpaRepositories(repositoryBaseClass = CustomJpaRepository.class) has been enough for Spring Boot applications to use custom Spring Data JPA repositories that extend from SimpleJpaRepository as done in:

That is not the case for custom Querydsl Spring Data repositories.

Setting @EnableJpaRepositories(repositoryBaseClass = AsimioQuerydslJpaRepositoryImpl.class) in Application.java fails with exception:

java.lang.IllegalArgumentException: Object of class [com.asimio.demo.repository.support.AsimioQuerydslJpaRepositoryImpl] must be an instance of interface org.springframework.data.jpa.repository.support.JpaRepositoryImplementation

A fix to this exception is to implement a JpaRepositoryFactoryBean that delegates to a JpaRepositoryFactory that in turn accesses the custom Querydsl JPA repository AsimioQuerydslJpaRepositoryImpl.

AsimioJpaRepositoryFactoryBean.java:

public class AsimioJpaRepositoryFactoryBean<T extends JpaRepository<S, I>, S, I>
    extends JpaRepositoryFactoryBean<T, S, I> {
// ...
  @Override
  protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
    return new AsimioJpaRepositoryFactory(entityManager);
  }
}

AsimioJpaRepositoryFactory.java:

public class AsimioJpaRepositoryFactory extends JpaRepositoryFactory {
// ...
  @Override
  protected RepositoryFragments getRepositoryFragments(RepositoryMetadata metadata, EntityManager entityManager,
      EntityPathResolver resolver, CrudMethodMetadata crudMethodMetadata) {

    boolean isAsimioQueryDslRepository = QUERY_DSL_PRESENT
      && AsimioQuerydslRepository.class.isAssignableFrom(metadata.getRepositoryInterface());

    if (isAsimioQueryDslRepository) {
      if (metadata.isReactiveRepository()) {
        throw new InvalidDataAccessApiUsageException(
          "Cannot combine Querydsl and reactive repository support in a single interface");
      }

      AsimioQuerydslJpaRepositoryImpl<?, ?> queryDslRepository = new AsimioQuerydslJpaRepositoryImpl<>(
        getEntityInformation(metadata.getDomainType()),
        entityManager,
        resolver,
        crudMethodMetadata
      );
      return RepositoryFragments.just(queryDslRepository);
    }

    return super.getRepositoryFragments(metadata, entityManager, resolver, crudMethodMetadata);
  }
}

And the resulting configuration:

Application.java:

@SpringBootApplication
@EnableJpaRepositories(
  repositoryFactoryBeanClass = AsimioJpaRepositoryFactoryBean.class
)
@EnableTransactionManagement
@EnableSpringDataWebSupport
public class Application {
// ...
}

In addition to the @EnableJpaRepositories repositoryFactoryBeanClass configuration:

  • @EnableTransactionManagement provides support to wrap in a DB transaction the execution of methods annotated with @Transactional.

7. SERVICE CLASS

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

  private final FilmRepository filmRepository;

  @Override
  public Page<Film> retrieveFilms(FilmSearchCriteria searchCriteria, Pageable page) {
    return this.filmRepository.findAll(searchCriteria, page);
  }
// ...
}

The searchCriteria and page arguments instantiated in the REST Controller are passed to the Film repository method.

FilmSearchCriteria.java:

public class FilmSearchCriteria {

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

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

8. 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?releaseYear=2006&minRentalRate=0.99&maxRentalRate=4.99&page=10&size=5

...
Hibernate: 
  select
    film0_.film_id as film_id1_8_0_,
    filmactors1_.actor_id as actor_id1_9_1_,
    filmactors1_.film_id as film_id2_9_1_,
    actor2_.actor_id as actor_id1_0_2_,
    language3_.language_id as language1_13_3_,
    film0_.description as descript2_8_0_,
    film0_.fulltext as fulltext3_8_0_,
    film0_.language_id as languag13_8_0_,
    film0_.last_update as last_upd4_8_0_,
    film0_.length as length5_8_0_,
    film0_.rating as rating6_8_0_,
    film0_.release_year as release_7_8_0_,
    film0_.rental_duration as rental_d8_8_0_,
    film0_.rental_rate as rental_r9_8_0_,
    film0_.replacement_cost as replace10_8_0_,
    film0_.special_features as special11_8_0_,
    film0_.title as title12_8_0_,
    filmactors1_.last_update as last_upd3_9_1_,
    filmactors1_.film_id as film_id2_9_0__,
    filmactors1_.actor_id as actor_id1_9_0__,
    actor2_.first_name as first_na2_0_2_,
    actor2_.last_name as last_nam3_0_2_,
    actor2_.last_update as last_upd4_0_2_,
    language3_.last_update as last_upd2_13_3_,
    language3_.name as name3_13_3_ 
  from
    public.film film0_ 
  inner join
    public.film_actor filmactors1_ 
      on film0_.film_id=filmactors1_.film_id 
  inner join
    public.actor actor2_ 
      on filmactors1_.actor_id=actor2_.actor_id 
  inner join
    public.language language3_ 
      on film0_.language_id=language3_.language_id 
  where
    (
      film0_.rental_rate between ? and ?
    ) 
    and film0_.release_year=?
...
Hibernate: 
  select
    count(film0_.film_id) as col_0_0_ 
  from
    public.film film0_ 
  inner join
    public.film_actor filmactors1_ 
      on film0_.film_id=filmactors1_.film_id 
  inner join
    public.actor actor2_ 
      on filmactors1_.actor_id=actor2_.actor_id 
  inner join
    public.language language3_ 
      on film0_.language_id=language3_.language_id 
  where
    (
      film0_.rental_rate between ? and ?
    ) 
    and film0_.release_year=?
...

This request includes the releaseYear, minRentalRate and maxRentalRate parameters, along with pagination’s page and size .

Notice the WHERE clause includes:

  where
    (
      film0_.rental_rate between ? and ?
    ) 
    and film0_.release_year=?

but no pagination’s offset, limit.

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

curl http://localhost:8080/api/films?releaseYear=2006&category=Action&category=Horror&minRentalRate=0.99&maxRentalRate=4.99&page=10&size=5

Hibernate: 
  select
    film0_.film_id as film_id1_8_0_,
    filmactors1_.actor_id as actor_id1_9_1_,
    filmactors1_.film_id as film_id2_9_1_,
    actor2_.actor_id as actor_id1_0_2_,
    language3_.language_id as language1_13_3_,
    film0_.description as descript2_8_0_,
    film0_.fulltext as fulltext3_8_0_,
    film0_.language_id as languag13_8_0_,
    film0_.last_update as last_upd4_8_0_,
    film0_.length as length5_8_0_,
    film0_.rating as rating6_8_0_,
    film0_.release_year as release_7_8_0_,
    film0_.rental_duration as rental_d8_8_0_,
    film0_.rental_rate as rental_r9_8_0_,
    film0_.replacement_cost as replace10_8_0_,
    film0_.special_features as special11_8_0_,
    film0_.title as title12_8_0_,
    filmactors1_.last_update as last_upd3_9_1_,
    filmactors1_.film_id as film_id2_9_0__,
    filmactors1_.actor_id as actor_id1_9_0__,
    actor2_.first_name as first_na2_0_2_,
    actor2_.last_name as last_nam3_0_2_,
    actor2_.last_update as last_upd4_0_2_,
    language3_.last_update as last_upd2_13_3_,
    language3_.name as name3_13_3_ 
  from
    public.film film0_ 
  inner join
    public.film_actor filmactors1_ 
      on film0_.film_id=filmactors1_.film_id 
  inner join
    public.actor actor2_ 
      on filmactors1_.actor_id=actor2_.actor_id 
  inner join
    public.language language3_ 
      on film0_.language_id=language3_.language_id 
  inner join
    public.film_category filmcatego4_ 
      on film0_.film_id=filmcatego4_.film_id 
  inner join
    public.category category5_ 
      on filmcatego4_.category_id=category5_.category_id 
      and (
        category5_.name in (
          ? ,
        ?)) 
      where
        (
          film0_.rental_rate between ? and ?
        ) 
        and film0_.release_year=?
...
Hibernate: 
  select
    count(film0_.film_id) as col_0_0_ 
  from
    public.film film0_ 
  inner join
    public.film_actor filmactors1_ 
      on film0_.film_id=filmactors1_.film_id 
  inner join
    public.actor actor2_ 
      on filmactors1_.actor_id=actor2_.actor_id 
  inner join
    public.language language3_ 
      on film0_.language_id=language3_.language_id 
  inner join
    public.film_category filmcatego4_ 
      on film0_.film_id=filmcatego4_.film_id 
  inner join
    public.category category5_ 
      on filmcatego4_.category_id=category5_.category_id 
      and (
        category5_.name in (
          ? ,
        ?)) 
      where
        (
          film0_.rental_rate between ? and ?
        ) 
        and film0_.release_year=?
...

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

  inner join
    public.film_category filmcatego4_ 
      on film0_.film_id=filmcatego4_.film_id 
  inner join
    public.category category5_ 
        on filmcatego4_.category_id=category5_.category_id 
        and (
          category5_.name in (
            ? ,
          ?)) 
        where
          (
            film0_.rental_rate between ? and ?
          ) 
          and film0_.release_year=?

And again, no pagination’s offset, limit, but this is logged instead:

HH000104: firstResult/maxResults specified with collection fetch; applying in memory!

9. CONCLUSION

Another option to write dynamic SQL queries using Spring Data JPA Specification and Criteria, and to write dynamic SQL queries using Spring Data JPA repositories and EntityManager, is to use Querydsl and Spring Data JPA repositories.

You can extend Spring Data JPA Querydsl built-in support to write type-safe SQL queries when you need to include a dynamic number of JOIN clauses.

Be aware of

HH000104: firstResult/maxResults specified with collection fetch; applying in memory!

when implementing pagination using Spring Data JPA and Querydsl.

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.

10. SOURCE CODE

Accompanying source code for this blog post can be found at:

11. REFERENCES