Dynamic Queries and Querydsl JPA support in Spring
In this blog post, we will dive into some of the features provided by Querydsl and how to integrate the same into your spring project to help you simplify the data access layer. Spring Data JPA removes much boilerplate from repository code and QueryDSL can be used to create clean and reusable query specifications. This is not an introductory tutorial to spring data. I assume you are well versed with spring data and spring boot and you are looking into adding more tools to your skillset and implement more complex logic.
What is Querydsl?
The Querydsl project provides a framework that lets you write type-safe queries in Java rather than constructing them using strings. This has several advantages like code completion in your IDE, domain types and properties can be accessed in a type-safe manner reducing the probability of query syntax errors during run-time. Querydsl has modules that support JPA, JDO, SQL, MongoDB and more. In this article, we are going to concentrate on querydsl support for JPA.
Why Querydsl?
Most enterprise java projects make use of JPA to simplify mapping the objects to the relational model. While this has obvious advantages, one of the major drawbacks comes when creating queries for more complex use cases. Hibernate provides JPQL for use in complex scenarios. The major problem with JPQL is the lack of type safety. For more dynamic queries, making use of JPQL usually involves concatenation of strings which is unsafe and error-prone. JPA 2.0 proposes the use of Criteria Query API as a new and type-safe way of building queries. However, the major drawback is that the API is verbose and hard to read. Querydsl library is built based on the idea of generated metadata classes but implemented with a fluent and readable API.
The Problem
Suppose that we are building a REST API to be used to fetch order information from the database for an e-commerce site. We want to be able to filter using various parameters. e.g the order id, the order date, customer first name, customer last name, order status etc. All, some or none of these parameters can be passed to filter the orders. We are going to implement a solution to this problem using spring data and querydsl. Below is the data model.
From the above model, we can see that:
- orders consist of several order items and order status
- an order belongs to a customer and has a payment.
- payment has a payment method.
If we used pure spring data to create our filters, this is how our repository layer would look like: ( I have omitted all other entities for brevity)
@Data
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_id")
private Customer customer;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "payment_id")
private Payment payment;
private ZonedDateTime createdAt;}public interface OrderRepository extends JpaRepository<Order, Long>{
List<Order> findByCustomer_firstNameContaining(
String firstname);
List<Order> findByCustomer_lastNameContaining(String firstname);
List<Order> findByCreatedAtBetween(
ZonedDateTime from, ZonedDateTime to);
List<Order> findByPayment_paymentMethod_name(
String paymentMethod);
}
You can see the pattern, for every filter param, we have one query. To combine two filters, we have to add another query to cater for that. e.g to filter by order Id and order creation date, we will have to add a query like
List<Order> findByIdAndCreatedAtBetween(
Long orderId,ZonedDateTime from, ZonedDateTime to);Even worse, if we add a new field, we will have to add a whole lot of queries for all the possible new combinations. There will be a total of 2^n — 1 queries for n fields. Ouch!!
Luckily, the above problem of having exponential complexity can be solved on O(n) . Spring provides Specifications which is built on top of criteria api. But in this article, we are going to focus on using QueryDSL which provides an even more powerful and concise way of solving the same problem.
Adding querydsl to maven build
Add the following build dependencies to your pom.xml file for your spring boot project.
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>Querydsl fluent API is only possible through the generation of metadata classes for the entity classes called Q-Types. For example, if we have a class called Order.java marked with @Entity annotation, the corresponding generated Q-Type class will be QOrder.java. The `querydsl-apt` dependency is responsible for this and only available at build time. The `querydsl-jpa` is the querydsl itself.
To configure annotation processing plugin that takes advantage of querydsl-apt, add the following plugin configuration to your pom — inside the <project><build><plugins> element:
<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>
</plugin>This plugin generates the Q-Types during the process goal of the maven build. To generate the Q-types for your entity classes, just run mvn compile command.
To view your generated classes, go to the directory specified in the outputDirectory property of apt-maven-plugin (target/generated-sources/java in our example). You will see a package and class structure that directly mirrors your domain model, except all the classes start with letter Q. eg (QOrder in our case).
One last step before we can begin using the generated classes, add the directory to the source folder of your project if your IDE doesn’t add them automatically. (In IntelliJ, right-click the project folder > maven > Generate Source and Update folders. Consult how to do the same for other IDEs)
Querydsl + Spring data
Spring provides full QueryDSL integration. To enable this in your repository class, extend QuerydslPredicateExecutor interface. It provides the following powerful methods which accept Predicates.
Optional<T> findOne(Predicate var1);
Iterable<T> findAll(Predicate var1);
Iterable<T> findAll(Predicate var1, Sort var2);
Iterable<T> findAll(Predicate var1, OrderSpecifier<?>... var2);
Iterable<T> findAll(OrderSpecifier<?>... var1);
Page<T> findAll(Predicate var1, Pageable var2);
long count(Predicate var1);Let’s extend this in our OrderRepository class.
public interface OrderRepository extends JpaRepository<Order, Long>,
QuerydslPredicateExecutor<Order>{
}This now allows us to use simple predicate for filtering. e.g filter by order id where order id is equal to 1.
QOrder order = QOrder.order;
orderRepository.findAll(order.id.eq(1L));We can even combine predicates to create more powerful queries. e.g filter orders by id and orders where created between first date and second date.
QOrder order = QOrder.order;
orderRepository.findAll(order.id.eq(1L)
.and(order.createdAt.between(fromDate, toDate)
);This is much easier to read and we can replace all the queries we had defined earlier with just predicates to reduce the bloat in our repository layer.
If we have many filter parameters, we will end up having too much logic for creating predicates inside our service class. We can however implement our own custom repository interface. Spring provides QuerydslRepositorySupport to make this possible.
Let’s implement a more complicated filter to demonstrate this.
Create a custom repository interface.
public interface CustomOrderRepository {
List<Order> fetchAll(OrderFilter filterParams);
}The interface accepts OrderFilter class which contains all our filter parameters.
@Value
public class OrderFilter {
Long orderId;
String firstName;
String lastName;
String paymentMethod;
String orderStatus;
}@Value is a Lombok annotation to make all fields private final and also provide getters and setters.
Let’s now implement our interface by creating CustomOrderRepositoryImpl class. Note that it extends springs QuerydslRepositorySupport class.
@Repository
public class CustomOrderRepositoryImpl extends QuerydslRepositorySupport implements CustomOrderRepository {
public CustomOrderRepositoryImpl() {
super(Order.class);
} @Override
public List<Order> fetchAll(OrderFilter filterParams) {
return null;
}
}
Note that this is just an empty class with no logic. The implementation to create a query that filters by using all those variable params will be shown below. But before that, it is good to first show the modeling of all entities and the relationships for clarity purposes. Note: it is assumed that you are already familiar with JPA and modeling relationships between entity classes.
Now with the models ready, let’s provide the actual implementation of the method.
Pretty straightforward and supports our dynamic query needs. But lets me explain a few points before wrapping up since the article is already becoming too long.
We instantiate our Q-Types then join to all the entities we need. Note the from(order)... which provides the basis of our query. The order is the Q-Type of the class we passed in the constructor of our implementation. We then construct the rest of the query by appending to it based on the filter parameters. Finally, we provide an OrderSpecifier and initiate a fetch.
There is still a lot you can do with querydsl like pagination, using subqueries and even returning custom DTO objects.
Note: One problem with the above implementation is that it will trigger n+1 queries if you try to access the mapped relations data. e.g calling order.getCustomer() will initiate a query to get customer information. This can cause a big performance hit especially when returning lots of data. There are several ways to solve this problem. One way is to return a custom object that has all the information you need. You can check out the Github repository for this article which has all the code and the implementation as well. https://github.com/mumo-dev/spring-query-dsl-sample
Also, remember to check the documentation for querydsl for more examples and to learn more.
Cheers, to more coding…
References
