Dynamic Spring Data JPA Repository Query With Arbitrary AND Clauses

1. Overview

In developing an application with Spring Data, quite often we need to construct a dynamic query based on the selection criteria to fetch data from the database.

This tutorial explores three approaches to creating dynamic queries in the Spring Data JPA repository: query by Example, query by Specification, and query by Querydsl.

2. Scenario Setup

In our demonstration, we’ll create two entities School and Student. The association between these classes is one-to-many where one School can have many Student:

@Entity
@Table
public class School {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Long id;
    @Column
    private String name;
    @Column
    private String borough;
    @OneToMany(mappedBy = "school")
    private List<Student> studentList;
    // constructor, getters and setters
}

@Entity
@Table
public class Student {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Long id;
    @Column
    private String name;
    @Column
    private Integer age;
    @ManyToOne
    private School school;
    // constructor, getters and setters
}

In addition to the entity classes, let’s define a Spring Data repository for the Student entity:

public interface StudentRepository extends JpaRepository<Student, Long> {
}

Lastly, we’ll add some sample data to the School table:

Let’s do the same for the Student table as well:

In the subsequent sections, we’ll find the records by the following criteria with different approaches:

• Student‘s name ends with Smith, and
• Student‘s age is 20, and
• The Student‘s school is located in Ealing borough

3. Query by Example

Spring Data offers an easy way to query entities using an example. The idea is simple: we create an example entity and put the search criteria that we’re looking for in it. Then, we use this example to find the entities that match it.

To adopt this, the repository has to implement the interface QueryByExampleExecutor. In our case, the interface is already extended in the JpaRepository which we usually extend in our repositories. Therefore, it’s not necessary to implement it explicitly.

Now, let’s create a Student example to include the three selection criteria that we want to filter:

School schoolExample = new School();
schoolExample.setBorough("Ealing");
Student studentExample = new Student();
studentExample.setAge(20);
studentExample.setName("Smith");
studentExample.setSchool(schoolExample);
Example example = Example.of(studentExample);

Once we set the example, we call the repository findAll(…) method to get the result:

List<Student> studentList = studentRepository.findAll(example);

However, the example above supports exact matching only. If we want to fetch students whose name ends with “Smith“, we need to customize the matching strategy. Query by example provides the ExampleMatcher class for doing so. All we need to do is create an ExampleMatcher on the name field and apply it to the Example instance:

ExampleMatcher customExampleMatcher = ExampleMatcher.matching()
  .withMatcher("name", ExampleMatcher.GenericPropertyMatchers.endsWith().ignoreCase());
Example<Student> example = Example.of(studentExample, customExampleMatcher);

4. Query by Specification

Query by Specification in Spring Data JPA enables the creation of dynamic queries based on a set of conditions using the Specification interface.

Compared to traditional methods, such as derived query methods or custom queries with @Query, this approach is more flexible. It’s useful for complex query requirements or when the query needs to be dynamically adjusted at runtime.

Similar to the query by example, our repository method has to extend an interface to enable this functionality. This time, we need to extend JpaSpecificationExecutor:

public interface StudentRepository extends JpaRepository<Student, Long>, JpaSpecificationExecutor<Student> {
}

Next, we have to define three methods for each filter condition. The Specification doesn’t limit us to using one method for one filter condition. This is mainly for clarity and to make it more readable:

public class StudentSpecification {
    public static Specification<Student> nameEndsWithIgnoreCase(String name) {
        return (root, query, criteriaBuilder) ->
          criteriaBuilder.like(criteriaBuilder.lower(root.get("name")), "%" + name.toLowerCase());
    }
    public static Specification<Student> isAge(int age) {
        return (root, query, criteriaBuilder) -> criteriaBuilder.equal(root.get("age"), age);
    }
    public static Specification<Student> isSchoolBorough(String borough) {
        return (root, query, criteriaBuilder) -> {
            Join<Student, School> scchoolJoin = root.join("school");
            return criteriaBuilder.equal(scchoolJoin.get("borough"), borough);
        };
    }
}

From the above methods, we know that we use CriteriaBuilder to construct filtering conditions. The CriteriaBuilder helps us to build dynamic queries in JPA programmatically and gives us flexibility similar to writing SQL queries. It allows us to create predicates with methods such as equal(…) and like(…) to define conditions.

In the case of a more complex operation, such as when joining an additional table with the base table, we’d use Root.join(…). The root acts as the anchor for the FROM clause, providing access to the entity’s attributes and relationships.

Now, let’s call the repository method to get the filtered results by the Specification:

Specification<Student> studentSpec = Specification
  .where(StudentSpecification.nameEndsWithIgnoreCase("smith"))
  .and(StudentSpecification.isAge(20))
  .and(StudentSpecification.isSchoolBorough("Ealing"));
List<Student> studentList = studentRepository.findAll(studentSpec);

5. Query by QueryDsl

In order to use QueryDSL, we need to add a couple of dependencies. Let’s add the following Querydsl JPA and APT support dependencies to our pom.xml:

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
    <version>5.1.0</version>
    <classifier>jakarta</classifier>
</dependency>
<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-apt</artifactId>
    <version>5.1.0</version>
    <classifier>jakarta</classifier>
    <scope>provided</scope>
</dependency>

Notably, the package name of JPA changes from javax.persistence to jakarata.persistence in JPA 3.0. We have to put the jakarta classifier in the dependencies if we use version 3.0 and onwards.

Besides the dependencies, we have to include the following annotation processor in the plugin section of pom.xml as well:

<plugin>
    <groupId>com.mysema.maven</groupId>
    <artifactId>apt-maven-plugin</artifactId>
    <version>1.1.3</version>
    <executions>
        <execution>
            <phase>generate-sources</phase>
            <goals>
                <goal>process</goal>
            </goals>
            <configuration>
                <outputDirectory>target/generated-sources</outputDirectory>
                <processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
            </configuration>
        </execution>
    </executions>
</plugin>

This processor generates meta-model classes for our entity classes during compile time. After we incorporate these settings into our application and compile it, we’ll see Querydsl generates two query types QStudent and QSchool in the build folder.

In a similar fashion, we have to include the QuerydslPredicateExecutor this time to enable our repository to fetch the results by these query types:

public interface StudentRepository extends JpaRepository<Student, Long>, QuerydslPredicateExecutor<Student>{
}

Next, we’ll create a dynamic query based on these query types and use it in our StudentRepository for querying. These query types already include all attributes of the corresponding entity classes. Thus, we can directly refer to the fields that are needed when building the predicate:

QStudent qStudent = QStudent.student;
BooleanExpression predicate = qStudent.name.endsWithIgnoreCase("smith")
  .and(qStudent.age.eq(20))
  .and(qStudent.school.borough.eq("Ealing"));
List studentList = (List) studentRepository.findAll(predicate);

As demonstrated in the code above, defining query conditions with query types is straightforward and intuitive.

Despite the complexity of setting up the required dependencies which is a one-off task, it offers the same fluent way of the Specification which is intuitive and easy to read.

Moreover, we don’t need to explicitly define the filtering condition manually as required in the Specification class.

6. Conclusion

The post Dynamic Spring Data JPA Repository Query With Arbitrary AND Clauses first appeared on Baeldung.