Spring Data JPA Tutorial: Creating Database Queries With Querydsl

The previous part of this tutorial described how we can create dynamic database queries with the JPA Criteria API. Although it is easy to create simple criteria queries, the JPA Criteria API has one major flaw:

It is very hard to implement complex queries and even harder to read them.

This blog post describes how we can solve these problems by using Querydsl. We will also implement a search function that has two requirements:

• It must return todo entries whose title or description contains the given search term.
• The search must be case-insensitive.

Let’s start by getting the required dependencies.

Getting the Required Dependencies

Before we can use Querydsl with Spring Data JPA, we have to add the Querydsl JPA module into our classpath. We can do this by adding the following dependency declaration into the dependencies section of our pom.xml file:

1<dependency>
2	<groupId>com.mysema.querydsl</groupId>
3	<artifactId>querydsl-jpa</artifactId>
4	<version>3.4.3</version>
5</dependency>

After we have added the Querydsl JPA dependency into our pom.xml file, we have to ensure that Maven creates the Querydsl query types when we compile our project.

Creating the Querydsl Query Types

Querydsl query types are classes that:

• Describe the structure of our domain model classes such as entities and @Embeddable classes.
• Provide us a way to create the conditions of our database queries.

We can create these classes automatically by using the Maven APT Plugin. We can configure the Maven APT Plugin by following these steps:

1. Add the Maven APT Plugin (version 1.1.3) declaration to the plugins section of the pom.xml file.
2. Configure the dependencies of this plugin and add the querydsl-apt dependency (version 3.4.3) to the plugin’s dependencies section.
3. Create an execution that invokes the plugin’s process goal when our project is compiled.
4. Ensure that the Querydsl query types are created to the target/generated-sources/apt directory.
5. Ensure that the plugin runs only the com.mysema.query.apt.jpa.JPAAnnotationProcessor. This annotation processor scans our entities and embeddable classes, and creates the Querydsl query types.

The configuration of the Maven APT plugin looks as follows:

 1<plugin>
 2	<groupId>com.mysema.maven</groupId>
 3	<artifactId>apt-maven-plugin</artifactId>
 4	<version>1.1.3</version>
 5	<executions>
 6		<execution>
 7			<goals>
 8				<goal>process</goal>
 9			</goals>
10			<configuration>
11				<outputDirectory>target/generated-sources/apt</outputDirectory>
12				<processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
13			</configuration>
14		</execution>
15	</executions>
16	<dependencies>
17		<dependency>
18			<groupId>com.mysema.querydsl</groupId>
19			<artifactId>querydsl-apt</artifactId>
20			<version>3.4.3</version>
21		</dependency>
22	</dependencies>
23</plugin>

When we compile our project, the invoked annotation processor creates the Querydsl query types to the target/generated-sources/apt directory. Because our domain model has only one entity, the annotation processor creates only one class called QTodo. The source code of the QTodo class looks as follows:

 1package net.petrikainulainen.springdata.jpa.todo;
 2
 3import static com.mysema.query.types.PathMetadataFactory.*;
 4
 5import com.mysema.query.types.path.*;
 6
 7import com.mysema.query.types.PathMetadata;
 8import javax.annotation.Generated;
 9import com.mysema.query.types.Path;
10
11@Generated("com.mysema.query.codegen.EntitySerializer")
12public class QTodo extends EntityPathBase<Todo> {
13
14    private static final long serialVersionUID = -797939782L;
15
16    public static final QTodo todo = new QTodo("todo");
17
18    public final StringPath createdByUser = createString("createdByUser");
19
20    public final DateTimePath<java.time.ZonedDateTime> creationTime = createDateTime("creationTime", java.time.ZonedDateTime.class);
21
22    public final StringPath description = createString("description");
23
24    public final NumberPath<Long> id = createNumber("id", Long.class);
25
26    public final DateTimePath<java.time.ZonedDateTime> modificationTime = createDateTime("modificationTime", java.time.ZonedDateTime.class);
27
28    public final StringPath modifiedByUser = createString("modifiedByUser");
29
30    public final StringPath title = createString("title");
31
32    public final NumberPath<Long> version = createNumber("version", Long.class);
33
34    public QTodo(String variable) {
35        super(Todo.class, forVariable(variable));
36    }
37
38    public QTodo(Path<Todo> path) {
39        super(path.getType(), path.getMetadata());
40    }
41
42    public QTodo(PathMetadata<?> metadata) {
43        super(Todo.class, metadata);
44    }
45
46}

Let’s move on and find out how we can create database queries with Querydsl.

Creating Database Queries With Querydsl

We can create database queries with Querydsl by following these steps:

1. Modify the repository interface to support queries that use Querydsl.
2. Specify the conditions of the invoked database query.
3. Invoke the database query.

Let’s get started.

Modifying the Repository Interface

The QueryDslPredicateExecutor<T> interface declares the methods that can be used to invoke database queries that use Querydsl. This interface has one type parameter T that describes the type of the queried entity.

In other words, if we need to modify our repository interface to support database queries that use Querydsl, we have to follow these steps:

1. Extend the QueryDslPredicateExecutor<T> interface.
2. Set the type of the queried entity.

Example: The only Spring Data JPA repository of our example application (TodoRepository) manages Todo objects. After we have modified this repository to support Querydsl, its source code looks as follows:

1import org.springframework.data.querydsl.QueryDslPredicateExecutor;
2import org.springframework.data.repository.Repository;
3
4interface TodoRepository extends Repository<Todo, Long>, QueryDslPredicateExecutor<Todo> {
5
6}

After we have extended the QueryDslPredicateExecutor<T> interface, the classes that use our repository interface get access to the following methods:

• The long count(Predicate predicate) method returns the number of objects that fulfil the conditions specified by the Predicate object given as a method parameter.
• The boolean exists(Predicate predicate) method checks if there are objects that fulfil the conditions specified by the Predicate object given as a method parameters. If such objects are found, this method returns true. Otherwise this method returns false.
• The Iterable<T> findAll(Predicate predicate) returns objects that fulfil the conditions specified by the Predicate object given as a method parameter.
• The T findOne(Predicate predicate) method returns an object that fulfils the conditions specified by the Predicate object given as a method parameter. If no object is found, this method returns null.

Let’s find out how we can specify the conditions of the invoked database query.

Specifying the Conditions of the Invoked Database Query

We can specify the conditions of the invoked database query by using the generated Querydsl query types. To be more specific, we have to follow these steps:

1. Get a reference to the query object that describes the queried entity.
2. Create the Predicate object that specifies the conditions of the invoked database query.

First, we can get a reference to the query object that describes the queried entity by following these steps:

1. Locate the query type that describes the queried entity.
2. Get the reference from a static field.

If we need to query Todo objects, we can the required reference from the todo field of the QTodo class. The relevant part of the QTodo class looks follows:

1public class QTodo extends EntityPathBase<Todo> {
2
3    public static final QTodo todo = new QTodo("todo");
4}

The source code of our query generation code looks as follows:

1QTodo todo = QTodo.todo;

Second, We have to create the Predicate object that specifies the conditions of the invoked database query.

We can create a condition that limits the values of a single field by following these steps:

1. Select the target field of the condition.
2. Specify the condition.

If we want to create a Predicate object that returns Todo objects whose title is ‘Foo’, we can create the Predicate object by using the following code:

1Predicate titleIs = QTodo.todo.title.eq("Foo");

We can also combine multiple Predicate objects by using the BooleanExpression class. The following examples demonstrates how we can use that class:

Example 1: If we want to select todo entries whose title is ‘Foo’ and description is ‘Bar’, we can create the Predicate object by using the following code:

1Predicate titleAndDescriptionAre = QTodo.todo.title.eq("Foo")
2				.and(QTodo.todo.description.eq("Bar"));

Example 2: If we want select todo entries whose title is ‘Foo’ or ‘Bar’, we can create the Predicate object by using the following code:

1Predicate titleIs = QTodo.todo.title.eq("Foo")
2                .or(QTodo.todo.title.eq("Bar"));

Example 3: If we want to select todo entries whose title is ‘Foo’ and description is not ‘Bar’, we can create the Predicate object by using the following code:

1Predicate titleIsAndDescriptionIsNot = QTodo.todo.title.eq("Foo")
2				.and(QTodo.todo.description.eq("Bar").not());

The obvious next question is:

Where should we create these Predicate objects?

I argue that we should create these objects by using predicate builder classes because this way we will put our query generation logic into one place. In other words, we don’t litter the source code of our service classes (or other components) with the query generation logic.

Example:

We can create a predicate builder class by following these steps:

1. Create a final TodoPredicates class. The name of this class isn’t important, but I like to use the naming convention: [The name of the queried entity class]Predicates.
2. Add a private constructor the created class. This ensures that no one can instantiate our predicate builder class.
3. Add static predicate builder methods to this class. In our case, we will add only one predicate builder method (hasTitle(String title)) to this class and implement it by returning a new Predicate object.

The source code of the TodoPredicates class looks as follows:

 1import com.mysema.query.types.Predicate;
 2
 3final class TodoPredicates {
 4
 5    private TodoPredicates() {}
 6
 7    static Predicate hasTitle(String title) {
 8        return QTodo.todo.title.eq(title);
 9    }
10}

Let’s move on and find out how we can invoke the created database query.

Invoking the Created Database Query

After we have specified the conditions of the invoked query by creating a new Predicate object, we can invoke the database query by using the methods provided by the QueryDslPredicateExecutor<T> interface.

The following examples demonstrates how we can invoke different database queries:

Example 1: If we want to get the number of Todo objects that have the title ‘foo’, we have to create and invoke our database query by using this code:

1Predicate pred = TodoPredicates.hasTitle("foo");
2long count = repository.count(pred);

Example 2: If we want to know if the database contains Todo objects that have the title ‘foo’, we have to create and invoke our database query by using this code:

1Predicate pred = TodoPredicates.hasTitle("foo");
2boolean exists = repository.exists(pred);

Example 3: If we want to the get all Todo objects that have the title ‘foo’, we have to create and invoke our database query by using this code:

1Predicate pred = TodoPredicates.hasTitle("foo");
2Iterable<Todo> todoEntries = repository.findAll(pred);

Example 4: If we want to get the Todo object whose title is ‘foo’, we have to create and invoke our database query by using this code:

1Predicate pred = TodoPredicates.hasTitle("foo");
2Todo todoEntry = repository.findOne(pred);

Let’s move on and find out how we can implement the search function.

Implementing the Search Function

We can implement our search function by following these steps:

1. Modify our repository interface to support Querydsl.
2. Create the predicate builder class that creates Predicate objects.
3. Implement the service method that uses our predicate builder class and invokes the created database query by using our repository interface.

Let’s start by modifying our repository interface.

Modifying Our Repository Interface

We can make the necessary modifications to our repository interface by following these steps:

1. Extend the QueryDslPredicateExecutor<T> interface.
2. The type of the queried entity to Todo.

The source code of our repository interface looks as follows:

 1import org.springframework.data.querydsl.QueryDslPredicateExecutor;
 2import org.springframework.data.repository.Repository;
 3
 4import java.util.List;
 5import java.util.Optional;
 6
 7interface TodoRepository extends Repository<Todo, Long>, QueryDslPredicateExecutor<Todo> {
 8
 9    void delete(Todo deleted);
10
11    List<Todo> findAll();
12
13    Optional<Todo> findOne(Long id);
14
15    void flush();
16
17    Todo save(Todo persisted);
18}

Let’s move on and create the predicate builder class.

Creating the Predicate Builder Class

We can create the predicate builder class that fulfils the requirements of our search function by following these steps:

1. Create the predicate builder class and ensure that it cannot be instantiated.
2. Add a static titleOrDescriptionContainsIgnoreCase(String searchTerm) method to the predicate builder class and set its return type to Predicate.
3. Implement the titleOrDescriptionContainsIgnoreCase(String searchTerm) method by following these rules:

• If the searchTerm is null or empty, return a Predicate object that returns all todo entries.
• If the searchTerm is not null, return a Predicate object that ignores case and returns todo entries whose title or description contains the given search term.

The source code of our predicate builder class looks as follows:

 1import com.mysema.query.types.Predicate;
 2
 3final class TodoPredicates {
 4
 5    private TodoPredicates() {}
 6
 7    static Predicate titleOrDescriptionContainsIgnoreCase(String searchTerm) {
 8        if (searchTerm == null || searchTerm.isEmpty()) {
 9            return QTodo.todo.isNotNull();
10        }
11        else {
12            return QTodo.todo.description.containsIgnoreCase(searchTerm)
13                    .or(QTodo.todo.title.containsIgnoreCase(searchTerm));
14        }
15    }
16}

Let’s find out how we can implement the service method that creates and invokes our database query.

Implementing the Service Method

The first thing that we have to do is to create an interface called TodoSearchService. This interface declares one method called findBySearchTerm(). This method takes the search term as a method parameter and returns a list of TodoDTO objects. The source code of the TodoSearchService interface looks as follows:

1import java.util.List;
2
3public interface TodoSearchService {
4
5    List<TodoDTO> findBySearchTerm(String searchTerm);
6}

We can implement this interface by following these steps:

1. Create a RepositoryTodoSearchService class, implement the TodoSearchService interface, and annotate the class with the @Service annotation.
2. Add a private final TodoRepository field to the created class.
3. Create a constructor that injects a TodoRepository object to the created field by using constructor injection.
4. Override the findBySearchTerm() method. Annotate the method with the @Transactional annotation and ensure that the transaction is read-only.
5. Implement the findBySearchTerm() method by following these steps:
6. Get the Predicate object by invoking the static titleOrDescriptionContainsIgnoreCase() method of the TodoPredicates class.
7. Get the todo entries whose title or description contains the given search term by invoking the findAll() method of the QueryDslPredicateExecutor<T> interface. Pass the created Predicate object as a method parameter.
8. Transform the Iterable<Todo> object into a list of TodoDTO objects and return the created list.

The source code of our service class looks as follows:

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.stereotype.Service;
 3import org.springframework.transaction.annotation.Transactional;
 4
 5import java.util.List;
 6
 7import static net.petrikainulainen.springdata.jpa.todo.TodoPredicates.titleOrDescriptionContainsIgnoreCase;
 8
 9@Service
10final class RepositoryTodoSearchService implements TodoSearchService {
11
12    private final TodoRepository repository;
13
14    @Autowired
15    public RepositoryTodoSearchService(TodoRepository repository) {
16        this.repository = repository;
17    }
18
19    @Transactional(readOnly = true)
20    @Override
21    public List<TodoDTO> findBySearchTerm(String searchTerm) {
22		Predicate searchPred = titleOrDescriptionContainsIgnoreCase(searchTerm);
23        Iterable<Todo> searchResults = repository.findAll(searchPred);
24        return TodoMapper.mapEntitiesIntoDTOs(searchResults);
25    }
26}

Let’s move on and find out when we should create our database queries by using Querydsl.

When Should We Use Querydsl?

The previous parts of this tutorial described how we can create static database queries by using the method names of our query methods, the @Query annotation, and named queries. Although these query generation methods are very useful, and they help us to get rid of boilerplate code, we cannot use them if we have to create dynamic queries (i.e queries that don’t have a constant number of conditions).

If we have to create dynamic database queries, we can use either the JPA Criteria API or Querydsl. The pros of using Querydsl are:

• It supports dynamic queries.
• It has a very clean API. In other words, it is easy to create complex queries with Querydsl, and the query generation code is easy to read.
• It also supports JDO, Lucene, and MongoDB.

The only “problem” of Querydsl is that it isn’t the standard way to create dynamic queries with the Java Persistence API. In my opinion this isn’t a real problem, but if you can use only standard APIs, it is definitely a problem to you.

If you read my the previous part of this tutorial, you probably remember that we should use the JPA Criteria API only when we don’t have a choice. That is why my opinion shouldn’t be a surprise to you.

If we need to create dynamic database queries, we should use Querydsl.

Let’s summarize what we learned from this blog post.

Summary

This blog post has taught us five things:

• We can create Querydsl query types by using the Maven APT Plugin.
• If we want to invoke queries that use Querydsl, our repository interface must extend the QueryDslPredicateExecutor<T> interface.
• We can specify the conditions of our database queries by creating new Predicate objects.
• We should create our Predicate objects by using predicate builder classes.
• If we have to create dynamic database queries, we should use Querydsl.

The next part of this tutorial describes how we can sort the query results of our database queries.

P.S. You can get the example application of this blog post from Github.