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.
Project setup for Querydsl with Spring Data JPA
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):
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | <?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.3.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>11</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. Furthermore, it is easy to integrate and use.
Using Querydsl you need to update your build section within your pom.xml :
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | <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 trouble and can't use the generated classes, make sure the project builds automatically. Check this within your IDE or update the Maven project (ALT + F5 within Eclipse).
JPA and Querydsl entity setup
For this quick demo, I am using the following JPA entity:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | @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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | @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"); } } |
Endpoint to filter and paginate data with Querydsl and Spring Data JPA
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | @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. You can (and maybe should) also easily extract it to a 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 Q in front of all your JPA entities. With this class, you have access to the entity's attributes and specify typesafe filters. The BooleanBuilder is used to concatenate 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)
| 1 2 3 4 5 6 7 8 9 | @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>:
| 1 2 | public interface PersonRepository extends JpaRepository<Person, Long>, QuerydslPredicateExecutor<Person> { } |
Accessing the endpoint
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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | { "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:
| 1 2 3 4 5 6 7 | 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.
Further resources on Querydsl and Spring Data JPA
Find further persistence related tips & tricks here:
- Best Practices for Flyway and Hibernate with Spring Boot
- Lazy Loading of JPA attributes with Hibernate
- Avoid repeating attributes in JPA entities
Have fun with paging your data using Querydsl and Spring Data JPA,
Phil
