#HOWTO: Dynamic SQL Querying & Pagination with Querydsl and Spring Data JPA

Returning all the data in a table within a REST call can be slow if your table grows. Imagine a simple HTML <table></table> to show all the registered persons in your database. Even if you could return the whole dataset within one HTTP request, the user wouldn’t be able to read them all at once as he has to scroll down.

In addition, the user sometimes wants to filter the dataset to return only a part of the entire table. Writing code to fulfill these requirements (pagination & dynamic querying) from scratch can be quite cumbersome. Luckily Spring Data provides different approaches to address this and is easy-to-use. In this blog post, I’ll show you how to do dynamic SQL querying with paginated data with Querydsl and Spring Data JPA for a simple REST endpoint.

Starting with the pom.xml , I’ve bootstrapped a simple Spring Boot application and added the dependency for Querydsl (the version is managed by Spring Boot and you don’t have to specify it explicitly):

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.0.RELEASE</version>
    <relativePath /> <!-- lookup parent from repository -->
  </parent>

  <groupId>de.rieckpil.tutorials</groupId>
  <artifactId>dynamic-sql-querying-with-pagination</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>
  <name>dynamic-sql-querying-with-pagination</name>
  <description>Quick sample for dynamic SQL querying with pagination</description>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>com.querydsl</groupId>
      <artifactId>querydsl-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>com.h2database</groupId>
      <artifactId>h2</artifactId>
      <scope>runtime</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <optional>true</optional>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
  </dependencies>
          
  <!-- build section is explained later in the post -->
</project>

Pagination is already built-in Spring Data as you can pass an instance of PageRequest to the query methods of your JpaRepository and specify the page number, page size, and sorting with this object. To achieve dynamic SQL queries, there are several approaches within Spring Data as you can use JPA’s Criteria API, use query by example and the Querydsl integration support (for more information, have a look at the Spring Data documentation). I’ve tried all of them and picked Querydsl as it covered most of my use cases and is easy to integrate and use.

Using Querydsl you need to update your build section within your pom.xml :

<build>
    <finalName>${project.artifactId}</finalName>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
      <plugin>
        <groupId>com.mysema.maven</groupId>
        <artifactId>apt-maven-plugin</artifactId>
        <version>1.1.3</version>
        <executions>
          <execution>
            <goals>
              <goal>process</goal>
            </goals>
            <configuration>
              <outputDirectory>target/generated-sources/java</outputDirectory>
              <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
          </execution>
        </executions>
        <dependencies>
          <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>${querydsl.version}</version>
          </dependency>
        </dependencies>
      </plugin>
    </plugins>
  </build>

The apt-maven-plugin will generate Querydsl specific source code out of your JPA entities for you. These generated classes are required to query the database as you will see later on. If you have troubles and can’t use the generated classes, make sure the project builds automatically within your IDE or update the Maven project (ALT + F5 within Eclipse).

For this quick demo, I am using the following JPA entity:

@Data
@Entity
@QueryEntity
public class Person {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  private String firstname;
  private String lastname;
  private Instant dob;
  private Integer budget;

}

To populate some random data to effectively use pagination and dynamic queries, I am adding 10.000 persons to the in-memory H2 database during the application startup:

@Service
public class PersonLoader implements CommandLineRunner {

  @Autowired
  private PersonRepository personRepository;

  @Override
  public void run(String... args) throws Exception {

    System.out.println("Loading entries...");

    String[] firstnames = { "Tom", "Max", "Anna", "Hanna", "Mike", "Duke", "Fred",
       "Tim", "Paul", "Luke", "Tobias", "Timi", "Michelle", "Thomas", "Andrew" };
    String[] lastnames = { "Smith", "Taylor", "Williams", "Hammer", "Lewis", "Jones", 
        "Evans", "Harris", "Mayer", "Schmid" };

    LocalDateTime initDate = LocalDateTime.of(1990, 12, 12, 12, 12);

    for (int i = 0; i < 10_000; i++) {
      Person p = new Person();
      p.setBudget(ThreadLocalRandom.current().nextInt(10000));
      p.setDob(Instant.ofEpochSecond(initDate.plusDays(i).toEpochSecond(ZoneOffset.UTC)));
      p.setFirstname(firstnames[ThreadLocalRandom.current().nextInt(0, firstnames.length)]);
      p.setLastname(lastnames[ThreadLocalRandom.current().nextInt(0, lastnames.length)]);
      personRepository.save(p);
    }

    System.out.println("...Finished loading 10.000 entities");
  }

}

Within the HTTP request, I want to be able to specify the page number, the page size and filter the data by searching for exact matches of some attributes. The REST controller for this looks like the following:

@RestController
@RequestMapping("/persons")
public class PersonController {

  @Autowired
  private PersonRepository personRepository;

  @GetMapping
  public Page<Person> persons(
      @RequestParam(name = "page", defaultValue = "0") int page,
      @RequestParam(name = "size", defaultValue = "500") int size,
      @RequestParam(name = "firstname", required = false) String firstname,
      @RequestParam(name = "lastname", required = false) String lastname,
      @RequestParam(name = "budget", required = false) Integer budget,
      @RequestParam(name = "dobLimit", required = false) Long dobLimit) {

    BooleanBuilder booleanBuilder = new BooleanBuilder();

    if (firstname != null && !firstname.isEmpty()) {
      booleanBuilder.and(QPerson.person.firstname.eq(firstname));
    }

    if (lastname != null && !lastname.isEmpty()) {
      booleanBuilder.and(QPerson.person.lastname.eq(lastname));
    }

    if (budget != null && budget != 0) {
      booleanBuilder.and(QPerson.person.budget.goe(budget));
    }

    if (dobLimit != null && dobLimit != 0) {
      booleanBuilder.and(
          QPerson.person.dob.before(Instant.ofEpochSecond(dobLimit)));
    }

    return personRepository.findAll(booleanBuilder.getValue(),
        PageRequest.of(page, size, Sort.by(Sort.Direction.ASC, "id")));
  }

}

I’ve added several @RequestParam to the endpoint to be able to filter the dataset. For demo purposes, the code is part of the controller class but could be easily extracted to an own service class. The first thing you may notice is the usage of the class QPerson for building the requested SQL statements. This class is generated by the apt-maven-plugin and Querydsl will add the letter in front of all your JPA entities. With this class, you have access to the entity’ attributes and specify typesafe filters. The BooleanBuilder is used to concat the filter criteria via SQL’s AND operator.

If your queried domain object is quite bigger then this example and if you want to reduce the if statements, you can use Spring Data’s Querydsl predicate argument resolving within a controller method. The default behavior uses eq for binding simple properties of your domain object. For detailed documentation and further configuration, have a look at the following links: Spring Data Querydsl Web Support and this running example on GitHub (thanks to @odrotbohm for this hint)

@GetMapping("/simplified")
public Page<Person> getPersonsSimplified(
      @QuerydslPredicate(root = Person.class) Predicate predicate, 
      @RequestParam(name = "page", defaultValue = "0") int page,
      @RequestParam(name = "size", defaultValue = "500") int size) {
  
    return personRepository.findAll(predicate, 
         PageRequest.of(page, size, Sort.by(Sort.Direction.ASC, "id")));
}

In this example, I am directly returning the Page<T> object as this contains important meta-data about the dataset for the frontend (for e.g. displaying paging buttons and min/max page indicators in your table) but you could also get only the data by calling .getContent() to return the list of objects.

To be able to pass the BooleanBuilder object to the .findAll() method of your repository, your interface has to extend the interface QuerydslPredicateExecutor<Person>:

public interface PersonRepository extends JpaRepository<Person, Long>, QuerydslPredicateExecutor<Person> {
}

You can now access the endpoint within your browser or REST client and play around with the filter e.g. http://localhost:8080/persons?firstname=Max&page=0&size=2 will result in:

{
  "content": [
    {
      "id": 30,
      "firstname": "Max",
      "lastname": "Harris",
      "dob": "1991-01-10T12:12:00Z",
      "budget": 8017
    },
    {
      "id": 43,
      "firstname": "Max",
      "lastname": "Lewis",
      "dob": "1991-01-23T12:12:00Z",
      "budget": 2644
    }
  ],
  "pageable": {
    "sort": {
      "sorted": true,
      "unsorted": false,
      "empty": false
    },
    "offset": 0,
    "pageNumber": 0,
    "pageSize": 2,
    "unpaged": false,
    "paged": true
  },
  "totalPages": 334,
  "totalElements": 667,
  "last": false,
  "size": 2,
  "number": 0,
  "sort": {
    "sorted": true,
    "unsorted": false,
    "empty": false
  },
  "numberOfElements": 2,
  "first": true,
  "empty": false
}

The result object contains not only the requested persons, but also the meta-data I already mentioned. With this data your client is able to get the amount of totalPages and totalElements and can easily navigate further.

Some other possible requests might be:

http://localhost:8080/persons?firstname=Max&page=0&size=5
http://localhost:8080/persons?firstname=Max&lastname=Schmid&page=0&size=5
http://localhost:8080/persons?firstname=Duke&lastname=Schmid&page=0&size=50
http://localhost:8080/persons?firstname=Duke&lastname=Schmid&budget=1337&page=0&size=50
http://localhost:8080/persons?firstname=Duke&lastname=Schmid&budget=1337&page=0&size=50
http://localhost:8080/persons?firstname=Duke&lastname=Schmid&budget=1337&dobLimit=976579200&page=0&size=50
http://localhost:8080/persons?page=42&size=42

A running example can be found in my GitHub repository. Therefore have a look at the README.md and follow the instructions.

Have fun with paging your data,

Phil

2 Comments

    1. rieckpil December 19, 2018 at 11:43 am

      Thank you for the comment Ollie,

      this will reduce my small “if-hell”. I’ll add a section for this simplified solution.

      PS: You are doing great work with your Spring Data team!

      Kind regards,
      Philip

Leave a comment

Your email address will not be published. Required fields are marked *