Building dynamic queries is a common requirement in modern applications, especially when filtering data based on flexible and user-driven criteria. Spring Data JPA provides several mechanisms to achieve this, enabling us to construct queries dynamically without writing complex and repetitive SQL statements. In this article, we delve into three key approaches for creating dynamic queries in Spring Data JPA with arbitrary AND clauses: QueryDSL, Query by Example, and Query by Specification.
1. Scenario
This guide demonstrates constructing dynamic queries using QueryDSL with two related entities in a one-to-many relationship. We will dynamically build queries based on arbitrary conditions to filter data across these entities. We will use the following entities:
Department: Represents a company department.Employee: Represents an employee belonging to a department (oneDepartmentcan have manyEmployees).
Users will query employees with filters on both Employee and Department fields.
2. Setting Up QueryDSL in Your Project
To begin, we need to configure QueryDSL in our project. QueryDSL is a powerful library that allows us to build type-safe SQL-like queries using Java. It integrates smoothly with Spring Data JPA and supports dynamic query creation.
Maven Dependencies
Add the following dependencies to your pom.xml file:
<dependencies>
<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>
</dependencies>
<build>
<plugins>
<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.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
The first dependency here is querydsl-jpa. This is the core QueryDSL JPA module that provides support for building type-safe queries with JPA entities. The version 5.1.0 is specified, and the jakarta classifier ensures compatibility with the Jakarta Persistence API (javax.persistence was renamed to jakarta.persistence starting from Jakarta EE 9).
The second dependency is querydsl-apt, which is responsible for generating the QueryDSL meta-classes (e.g., QEmployee, QDepartment) during the build process. The scope is set to provided because this dependency is needed only at compile time and doesn’t need to be included in the final packaged artifact. Like the previous dependency, it also uses the jakarta classifier to ensure compatibility with Jakarta Persistence.
Build Plugin
This plugin, apt-maven-plugin, is used to process annotations in the Java source code and generate additional classes. In this case, it generates the QueryDSL JPA meta-model classes from the JPA entities. The plugin’s version is 1.1.3.
The plugin is configured to run in the generate-sources phase of the Maven build lifecycle, ensuring that the meta-model classes are generated before compilation. The goals section specifies the process goal, which triggers annotation processing. The configuration includes:
outputDirectory: Specifies where the generated source files should be placed, in this case,target/generated-sources.processor: Defines the annotation processor to use, which in this case iscom.querydsl.apt.jpa.JPAAnnotationProcessor. This processor reads the JPA annotations from your entities and generates QueryDSL classes based on them.
3. Defining Entities
In our scenario, we have two entities: Department and Employee. A Department has many Employees, establishing a one-to-many relationship.
Department Entity
@Entity
public class Department {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@OneToMany(mappedBy = "department", cascade = CascadeType.ALL)
private List<Employee> employees;
// Getters and Setters
}
The Department entity represents a company department. It includes:
- A primary key
id. - A
namefield for the department name. - A
List<Employee>field annotated with@OneToManyto establish the one-to-many relationship. ThemappedByattribute indicates that thedepartmentfield in theEmployeeentity owns the relationship.
Employee Entity
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstName;
private String lastName;
private Integer age;
@ManyToOne
@JoinColumn(name = "department_id")
private Department department;
// Getters and Setters
}
The Employee entity represents an employee within a department. It includes:
- A primary key
id. - Fields for
firstName,lastName, andage. - A
departmentfield annotated with@ManyToOneto establish the relationship back to theDepartmententity. The@JoinColumnannotation specifies the foreign key column asdepartment_id.
4. Generating QueryDSL Meta-Model
After defining the entities, compile your project using Maven. QueryDSL will generate meta-model classes (QEmployee and QDepartment). These classes allow you to reference entity fields like employee.firstName or department.name in a type-safe manner when building queries.
4.1 Using QuerydslPredicateExecutor for Simplified Dynamic Queries
A powerful feature of QueryDSL is its integration with Spring Data JPA through the QuerydslPredicateExecutor interface. This interface allows us to execute dynamic queries by combining predicates programmatically. In this section, we’ll demonstrate how to apply QuerydslPredicateExecutor to the Employee and Department entities in our application.
4.1.1 Defining the Employee Repository
To enable QuerydslPredicateExecutor, update the EmployeeRepository interface to extend it alongside JpaRepository. This provides built-in support for dynamic query execution using QueryDSL predicates.
public interface EmployeeRepository extends JpaRepository, QuerydslPredicateExecutor {
}
The JpaRepository<Employee, Long> offers basic CRUD operations and pagination support for the Employee entity, while the QuerydslPredicateExecutor<Employee> allows the direct execution of QueryDSL predicates using the findAll method.
4.1.2 Building and Executing Predicates
With the repository configured, we can now dynamically use QueryDSL to build predicates. Here’s an example of querying employees who satisfy multiple criteria:
@Service
public class EmployeeQueryService {
private final EmployeeRepository employeeRepository;
public EmployeeQueryService(EmployeeRepository employeeRepository) {
this.employeeRepository = employeeRepository;
}
public List<Employee> findEmployeesByMandatoryCriteria(String lastName, Integer age, String departmentName) {
QEmployee qEmployee = QEmployee.employee;
// Build the predicate with AND clauses
BooleanExpression predicate = qEmployee.lastName.endsWithIgnoreCase(lastName)
.and(qEmployee.age.eq(age))
.and(qEmployee.department.name.eq(departmentName));
// Execute the query and return results
return (List<Employee>) employeeRepository.findAll(predicate);
}
}
This code defines a service class, EmployeeQueryService, which uses QueryDSL to construct dynamic queries for filtering employees based on various criteria. It demonstrates the flexibility of QueryDSL in building predicates dynamically and highlights the use of .and() clauses to combine multiple conditions logically.
The above code dynamically constructs a query using QueryDSL predicates without any conditional checks. The BooleanExpression object predicate starts with the first condition, qEmployee.lastName.endsWithIgnoreCase(lastName), filtering employees whose last name ends with the specified string (case-insensitive). Additional filters are appended to the predicate using the .and() method, which combines conditions logically to ensure all must be true. For example, the second condition qEmployee.age.eq(age) restricts the results to employees with an exact age match, while the third condition, qEmployee.department.name.eq(departmentName), filters by department name.
The .and() method plays a pivotal role by chaining these conditions into a single predicate, forming a cohesive query that can be executed in one call to the repository. By passing the combined predicate to the findAll method of QuerydslPredicateExecutor, the repository efficiently retrieves only those employees meeting all specified criteria. This code is ideal for scenarios where all filters are mandatory, eliminating the need for conditional checks.
4.2 Exposing the Service through a REST Endpoint
To make this query accessible, you can expose it through a REST controller:
@RestController
public class EmployeeController {
private final EmployeeQueryService employeeQueryService;
public EmployeeController(EmployeeQueryService employeeQueryService) {
this.employeeQueryService = employeeQueryService;
}
@GetMapping("/employees")
public List<Employee> getEmployees(
@RequestParam String lastName,
@RequestParam Integer age,
@RequestParam String departmentName) {
return employeeQueryService.findEmployeesByMandatoryCriteria(lastName, age, departmentName);
}
}
The code below shows how to add sample data to the database for testing the endpoint:
@SpringBootApplication
public class DynamicJpaQuerydslApplication implements CommandLineRunner {
private final EmployeeRepository employeeRepository;
private final DepartmentRepository departmentRepository;
public DynamicJpaQuerydslApplication(EmployeeRepository employeeRepository, DepartmentRepository departmentRepository) {
this.employeeRepository = employeeRepository;
this.departmentRepository = departmentRepository;
}
public static void main(String[] args) {
SpringApplication.run(DynamicJpaQuerydslApplication.class, args);
}
@Override
public void run(String... args) {
Department sales = new Department(1L, "Sales", null);
Department marketing = new Department(2L, "Marketing", null);
departmentRepository.save(sales);
departmentRepository.save(marketing);
Employee johnSmith = new Employee(1L, "Thomas", "Smith", 25, sales);
Employee aliceJohnson = new Employee(2L, "Alice", "Franklin", 30, marketing);
Employee bobSmith = new Employee(3L, "Bob", "Fish", 25, sales);
employeeRepository.save(johnSmith);
employeeRepository.save(aliceJohnson);
employeeRepository.save(bobSmith);
}
}
4.3 Testing the Endpoint
Once the application is running, we can test the endpoint using a tool like Postman or a browser. For example, the following request on a browser (http://localhost:8080/employees?lastName=Smith&age=25&departmentName=Sales) retrieves employees matching the criteria:
Response (JSON):
5. Query by Example (QBE)
Spring Data JPA supports Query by Example (QBE), allowing us to dynamically construct queries based on a sample entity (example). This is particularly useful for building filters without explicitly writing queries. To support Query by Example, the repository interface must extend both JpaRepository and QueryByExampleExecutor.
Here is how we can integrate QBE into the application:
public interface EmployeeRepository extends JpaRepository<Employee, Long>, QueryByExampleExecutor<Employee> {
}
Implement the Service Layer
@Service
public class EmployeeQueryByExampleService {
@Autowired
EmployeeRepository employeeRepository;
public List<Employee> findEmployeesByExample(String lastName, Integer age, String departmentName) {
Employee probe = new Employee();
probe.setLastName(lastName);
probe.setAge(age);
Department department = new Department();
department.setName(departmentName);
probe.setDepartment(department);
// Create an Example with matching rules
ExampleMatcher matcher = ExampleMatcher.matching()
.withIgnorePaths("id") // Ignore ID field
.withIgnoreNullValues() // Ignore null values
.withStringMatcher(ExampleMatcher.StringMatcher.ENDING); // Match strings by ending
Example<Employee> example = Example.of(probe, matcher);
return employeeRepository.findAll(example);
}
}
Query by Example works by creating a probe, which is a sample entity with fields set to the desired query values. An ExampleMatcher is used to define matching rules, such as ignoring null values and specifying how string comparisons are handled. The probe and matcher are combined into an Example object, which serves as a query definition. This Example is then passed to the findAll method, allowing dynamic query execution without the need to write explicit SQL.
6. Query by Specification
Spring Data JPA also supports Specifications, a more advanced and flexible way to build dynamic queries programmatically using the Specification interface. To enable Specifications, update the repository by extending the interface with JpaSpecificationExecutor in addition to JpaRepository to support building and executing Specifications. This enables dynamic query construction based on flexible criteria.
Update the Repository and add JpaSpecificationExecutor to the repository to enable Specifications.
public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> {
}
Next, create a custom Specification class for building predicates dynamically.
public class EmployeeSpecifications {
public static Specification<Employee> hasLastName(String lastName) {
return (root, query, criteriaBuilder) ->
criteriaBuilder.like(root.get("lastName"), "%" + lastName);
}
public static Specification<Employee> hasAge(Integer age) {
return (root, query, criteriaBuilder) ->
criteriaBuilder.equal(root.get("age"), age);
}
public static Specification<Employee> hasDepartmentName(String departmentName) {
return (root, query, criteriaBuilder) ->
criteriaBuilder.equal(root.join("department").get("name"), departmentName);
}
}
The EmployeeSpecifications class provides reusable methods to build dynamic query predicates using Spring Data JPA’s Specification interface and the CriteriaBuilder. The hasLastName method uses criteriaBuilder.like to generate a predicate for matching employee last names with a wildcard pattern. The hasAge method leverages criteriaBuilder.equal to create a condition for exact age matching. Similarly, the hasDepartmentName method uses criteriaBuilder.equal in conjunction with root.join to filter employees based on their department name by performing a join between the Employee and Department entities. Each method dynamically constructs criteria for flexible query building.
Next, Use the Specification class to combine multiple dynamic conditions with .and() or .or() like this:
@Service
public class EmployeeQueryBySpecificationService {
@Autowired
EmployeeRepository employeeRepository;
public List<Employee> findEmployeesBySpecification(String lastName, Integer age, String departmentName) {
Specification<Employee> spec = Specification.where(null);
// Add dynamic specifications
spec = spec.and(EmployeeSpecifications.hasLastName(lastName));
spec = spec.and(EmployeeSpecifications.hasAge(age));
spec = spec.and(EmployeeSpecifications.hasDepartmentName(departmentName));
return employeeRepository.findAll(spec);
}
}
These two approaches – Query by Example and Query by Specification, offer alternatives to dynamic query building, complementing QueryDSL for different use cases in our Spring Data JPA application.
7. Conclusion
In this article, we explored dynamic query building in Spring Data JPA using a variety of approaches, including QueryDSL, Query by Example, and Query by Specification. Each method provides a unique and effective way to construct queries dynamically, catering to different use cases. We demonstrated how to use the .and operator in QueryDSL for combining multiple conditions seamlessly, enabling type-safe and expressive queries. Similarly, Query by Example and Query by Specification offer declarative and reusable ways to build complex filtering criteria without writing explicit SQL.
8. Download the Source Code
This article focused on building a Spring Data JPA query with arbitrary AND clauses for dynamic and flexible filtering.
You can download the full source code of this example here: spring data jpa query arbitrary and clauses
