QueryDSL for JPA

If we could say Hibernate provides a way to map database tables to classes, QueryDSL does the same for queries. QueryDSL lets you leverage the same entities defined for Hibernate and uses them to make legible queries that closely resemble native SQL queries. The result is queries written in native Java with IDE code completion and inline error detection.

QueryDSL closes the gap between your Java code and the database by letting you define queries in the most natural way you could expect, as in fruit.taste.eq("sweet") or if Taste was an Enum, fruit.taste.in(Taste.SWEET, Taste.SOUR). QueryDSL does this by generating what it calls Q-classes from entities. Each Q-class exports a static field that represents the entity and can be used several times to make complex queries.

My goal in this article is to quickly get you started with QueryDSL. I will create a database with two entities; Fruit and Batch. I will then demonstrate how easy it is to build queries with Q-classes.

Setup

The setup I used for this project was:

Platform: Archlinux 6.4.1
Java: 11.0.19
Spring Boot: 2.7.13
Build Tool: Maven 3.8.7

I used the https://start.spring.io/ tool to generate a Spring Boot project with "Spring Data JPA", the "H2 Database" and "Lombok" as dependencies. Then manually added the QueryDSL dependencies to pom.xml:

<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-jpa</artifactId>
  <version>5.0.0</version>
</dependency>
<dependency>
  <groupId>com.querydsl</groupId>
  <artifactId>querydsl-apt</artifactId>
  <version>5.0.0</version>
  <classifier>jpa</classifier>
</dependency>

Define Entities and Repositories

Define two Enums Category and Taste and the Fruit and Batch entities.

public enum Category {
  PIT, CORE, CITRUS, BERRY, MELON, TROPICAL
}

public enum Taste {
  SWEET, SOUR, SALTY, BITTER, UMAMI
}

@Data
@Entity
@NoArgsConstructor
@RequiredArgsConstructor
public class Fruit {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @NonNull
  private String name;
  @NonNull
  private String color;
  private String description;
  @NonNull
  private String region;
  @NonNull
  @Enumerated(EnumType.STRING)
  private Category category;
  @NonNull
  @Enumerated(EnumType.STRING)
  private Taste taste;

  @ManyToMany
  private Set<Batch> batches;
}

@Data
@Entity
@NoArgsConstructor
@RequiredArgsConstructor
public class Batch {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @NonNull
  private Long amountKilo;
  @NonNull
  private ZonedDateTime arrivalDate;
  @NonNull
  private Double cost;
  @NonNull
  private Double shipping;
  @NonNull
  private String orderNumber;

  @NonNull
  @ManyToMany
  private Set<Fruit> fruits;
}

The @Data annotation will tell Lombok to generate getter and setter methods for the entities. Lombok will also add an empty constructor for @NoArgsConstructor and a constructor for fields annotated with @NoArgsConstructor. There is also a many-to-many relationship between the Fruit and Batch tables.

There are many ways to query the database, and one of them is by getting an instance of EntityManager and using it with a Q-class to fetch entities. An easier way is to define a repository that extends both JpaRepository and QueryDslPredicateExecutor:

public interface FruitRepository extends JpaRepository<Fruit, Long>, QuerydslPredicateExecutor<Fruit> {
}

public interface BatchRepository extends JpaRepository<Batch, Long>, QuerydslPredicateExecutor<Batch> {
}

This way, in addition to the default find*, delete, and save operations provided by JpaRepository, we have access to QueryDSL methods.

Generate Q-classes

Since we are using Maven, we will build our application using the following command to generate the Q-classes:

# append -Dmaven.test.skip=true to save time on compilation
mvn clean install

Our directory structure before the Q-classes were generated will look something like this:

.
├── pom.xml
└── src
    └── main
        ├── java
        │   └── com
        │       └── brainoffloaded
        │           └── querydsl
        │               ├── entity
        │               │   ├── Batch.java
        │               │   ├── Category.java
        │               │   ├── Fruit.java
        │               │   └── Taste.java
        │               ├── BatchRepository.java
        │               ├── FruitRepository.java
        │               └── QuerydslIntroApplication.java
        └── resources
            └── application.properties

After a successful build, the Q-classes will appear under <project root>/target/generated-sources:

.
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── brainoffloaded
    │   │           └── querydsl
    │   │               ├── entity
    │   │               │   ├── Batch.java
    │   │               │   ├── Category.java
    │   │               │   ├── Fruit.java
    │   │               │   └── Taste.java
    │   │               ├── BatchRepository.java
    │   │               ├── FruitRepository.java
    │   │               └── QuerydslIntroApplication.java
    │   └── resources
    │       └── application.properties
    └── target
        └── generated-sources
            └── annotations
                └── com
                    └── brainoffloaded
                        └── querydsl
                            └── entity
                                ├── QBatch.java
                                └── QFruit.java

You IDE should recognize and add the generated-sources path to the list of project sources. In JetBrains' IntelliJ IDEA, you might need to do a simple build (Ctrl + F9). However, if you use Eclipse, have a look at the end of this article to check what worked for me.

Writing Queries

Now that we have defined our Entities and repositories and the Q-classes are generated by QueryDSL and recognized by our IDE, we can start writing queries. We will query our database inside a test case. But first, we need to tell Spring how to connect to our in-memory H2 database when running our test. We can do that by adding a properties file at the path src/test/resources/application-test.properties:

spring.datasource.url             = jdbc:h2:mem:querydsl-intro
spring.datasource.driverClassName = org.h2.Driver
spring.datasource.username        = test
spring.datasource.password        = test
spring.jpa.database-platform      = org.hibernate.dialect.H2Dialect

We can now write a test class annotated with @DataJpaTest that will load the database context but not the entire Spring application context:

@DataJpaTest
public class FruitServiceTest {
  @Autowired
  private FruitRepository fruitRepository;
  @Autowired
  private BatchRepository batchRepository;

  private QFruit fruit = QFruit.fruit;
  private QBatch batch = QBatch.batch;

  @Test
  void testSaveFruits() {
    Fruit apple = fruitRepository.save(new Fruit("apple", "yellow", "everywhere", Category.CORE, Taste.SWEET));
    Fruit banana = fruitRepository.save(new Fruit("banana", "yellow", "tropical areas", Category.TROPICAL, Taste.SWEET));
    Fruit blueberries = fruitRepository.save(new Fruit("blueberries", "blue", "humid northern areas", Category.BERRY, Taste.SWEET));
    fruitRepository.flush();

    Set<Fruit> batch1 = new HashSet<>();
    batch1.add(apple);
    batch1.add(banana);
    batchRepository.save(new Batch(100L, ZonedDateTime.now(), 200.0, 18.0, "AKO-02838", batch1));
    batchRepository.save(new Batch(40L, ZonedDateTime.now(), 360.0, 26.0, "WDF-08021", Collections.singleton(blueberries)));
    batchRepository.flush();

    Pageable pageable = PageRequest.of(0, 10, Sort.by("name"));
    Predicate predicate = fruit.taste.eq(Taste.SWEET)
        .and(fruit.color.eq("yellow"))
        .and(fruit.category.in(Category.CORE, Category.TROPICAL));

    Page<Fruit> result = fruitRepository.findAll(predicate, pageable);
    assertEquals(2, result.getTotalElements());

    Page<Batch> batchResult = batchRepository.findAll(batch.fruits.any().name.eq("apple"), PageRequest.of(0, 10));
    assertEquals(1, batchResult.getTotalElements());
    assertEquals("AKO-02838", batchResult.getContent().get(0).getOrderNumber());
  }
}

Now let's break down the example.

private QFruit fruit = QFruit.fruit;
private QBatch batch = QBatch.batch;

I've defined QFruit.fruit and QBatch.batch as class fields since they immutable and can be used in as many queries as we want. So it makes sense to define them only once. However, there might be cases where you need to refer to the same table twice. In those situations, you can use new QFruit("fruit1") to create a new reference to the table.

Fruit apple = fruitRepository.save(new Fruit("apple", "yellow", "everywhere", Category.CORE, Taste.SWEET));
Fruit banana = fruitRepository.save(new Fruit("banana", "yellow", "tropical areas", Category.TROPICAL, Taste.SWEET));
Fruit blueberries = fruitRepository.save(new Fruit("blueberries", "blue", "humid northern areas", Category.BERRY, Taste.SWEET));
fruitRepository.flush();

Set<Fruit> batch1 = new HashSet<>();
batch1.add(apple);
batch1.add(banana);
batchRepository.save(new Batch(100L, ZonedDateTime.now(), 200.0, 18.0, "AKO-02838", batch1));
batchRepository.save(new Batch(40L, ZonedDateTime.now(), 360.0, 26.0, "WDF-08021", Collections.singleton(blueberries)));
batchRepository.flush();

Before we can query the database, we need some data in our tables. Here, we have saved a few fruits and the batches they arrived in and we call .flush() to persist the changes to the database.

Pageable pageable = PageRequest.of(0, 10, Sort.by("name"));
Predicate predicate = fruit.taste.eq(Taste.SWEET)
    .and(fruit.color.eq("yellow"))
    .and(fruit.category.in(Category.CORE, Category.TROPICAL));

Page<Fruit> result = fruitRepository.findAll(predicate, pageable);
assertEquals(2, result.getTotalElements());

This is our first query. Because we need to test the results returned by the database rather than just return them, we use a variation of findAll provided by QuerydslPredicateExecutor that accepts a Predicate and a Pageable.

A predicate can be created by an expression as simple as fruit.taste.eq(Taste.SWEET) or as we have done in our example, multiple expressions can be joined together using .and(). Other connective methods include .or(), .in(), .notIn(), .coalese(), .andAnyOf(), .orAllOf() and so on. You can find the full list on the QueryDSL API documentation website.

A Pageable limits the amount of data returned by the query and accepts a page number, a start index, and an optional Sort parameter. The return data is of type Page which contains our data in the content field and the total number of pages and elements in the result set. Also, the Page class implements the Iterable interface which means you can map your result to DTOs if you wanted.

Page<Batch> batchResult = batchRepository.findAll(batch.fruits.any().name.eq("apple"), PageRequest.of(0, 10));
assertEquals(1, batchResult.getTotalElements());
assertEquals("AKO-02838", batchResult.getContent().get(0).getOrderNumber());

In our second query, we have used QBatch.batch to get any batch that has a fruit named "apple" in it. As you can see, the predicate batch.fruits.any().name.eq("apple") is almost as readable as natural language. To give you a comparison, check the equivalent for our first query written using JpaRepository method expressions:

@Query(value = "select f from Fruit f where f.category in ('CORE', 'TROPICAL') and f.taste = :taste and f.color = :color")
List<Fruit> findCoreOrTropicalFruitsByTasteAndColor(String taste, String color);

// OR
@Query
List<Fruit> findAllByCategory_CoreOrCategory_TropicalAndTasteEqualsAndColorEquals(Taste taste, String color);

It is quite obvious that the QueryDSL version is easier to understand and modify further in the future. Also, since with the QueryDSL version you get compile-time errors rather than runtime ones, you can probably get away from writing a test case just to test the correctness of your query.

Wrap Up

QueryDSL lets you construct type-safe and expressive SQL queries using native Java. This not only improves readability and maintainability but also reduces the chances of errors that can occur from writing native SQL statements. By leveraging Q-classes and Predicates, QueryDSL seamlessly integrates with existing JPA and Hibernate entities, making it an essential addition to any Java developer's toolkit.

If you liked this article, please share it with others. If you find any errors in this article, please let me know in the comments. The project source for this article is available on GitHub. I've made branches for Maven and Gradle and different versions of Java (11 and 17) to help you get started with QueryDSL in no time.

Extra Tip: Making Eclipse work with QueryDSL and Lombok

The version of Eclipse I used at the time of writing this article was 2023-03 (4.27.0). The first item in the following list (plus a restart) is enough to make QueryDSL work in Eclipse. To make Lombok work, I had to:

1. Select an "Annotation Processing Mode" under "Window > Preferences > Maven > Annotation Processing".

2. Download the Lombok .jar file.

3. Run the .jar file using java -jar lombok.jar and point it to my Eclipse installation directory. If you choose the correct directory, it will be listed on the screen with a ticked checkbox.

4. Add the following lines to eclipse.ini in the Eclipse installation directory after the -vmargs line (add the line if it doesn't exist):
   -Xbootclasspath/a:lombok.jar
   -javaagent:lombok.jar

5. Quit Eclipse, then start it with --clean. You will only need to use this argument once and not in subsequent runs.

You might also need to run mvn eclipse:eclipse, clean (F5), and refresh (Alt + F5) the project to make things work.