Guide to Dynamic Queries in Spring Boot: Using JpaSpecificationExecutor, Specification & Criteria API for Advanced Filtering

Spring Data JPA provides a powerful way to build dynamic queries using the JpaSpecificationExecutor<T> interface along with the Specification API and Criteria API. This guide will walk you through how to use these features from scratch.

---

1. What is JpaSpecificationExecutor<T>?

JpaSpecificationExecutor<T> is an interface provided by Spring Data JPA that allows us to write dynamic and complex queries without manually crafting JPQL or native SQL. It works alongside the Specification API, which enables filtering data based on multiple criteria dynamically.

Key Benefits:

• Dynamic Filtering: Build queries at runtime based on user input.
• Flexible Querying: Combine multiple conditions using AND / OR logic.
• Better Performance: Avoid fetching unnecessary data.
• Easier Maintenance: No need to write complex JPQL or native SQL.

---

2. Setting Up Spring Boot with Spring Data JPA

Step 1: Add Dependencies

Make sure you have the required dependencies in your pom.xml:

<dependencies>
    <!-- Spring Boot Starter Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <!-- H2 Database (for in-memory testing) -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

---

Step 2: Configure the Database in application.properties

For testing purposes, we’ll use H2 Database:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.h2.console.enabled=true

---

3. Creating the Entity and Repository

Let's create a User entity to demonstrate filtering.

Entity: User.java

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String firstName;
    private String lastName;
    private String email;
    private Integer age;

    // Constructors, Getters, and Setters
}

Repository with JpaSpecificationExecutor<T>

To enable dynamic querying, extend both JpaRepository and JpaSpecificationExecutor<T>:

@Repository
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
}

---

4. Building Dynamic Queries Using Specification

Step 1: Create UserSpecification.java

The Specification interface allows us to create dynamic filtering conditions.

public class UserSpecification {

    public static Specification<User> hasFirstName(String firstName) {
        return (root, query, criteriaBuilder) -> 
            firstName == null ? null : criteriaBuilder.equal(root.get("firstName"), firstName);
    }

    public static Specification<User> hasLastName(String lastName) {
        return (root, query, criteriaBuilder) -> 
            lastName == null ? null : criteriaBuilder.equal(root.get("lastName"), lastName);
    }

    public static Specification<User> hasAgeGreaterThan(Integer age) {
        return (root, query, criteriaBuilder) -> 
            age == null ? null : criteriaBuilder.greaterThan(root.get("age"), age);
    }
}

---

5. Using Criteria API for Complex Filtering

If you need more advanced queries, you can use the Criteria API directly.

public static Specification<User> hasEmailContaining(String email) {
    return (root, query, criteriaBuilder) -> 
        email == null ? null : criteriaBuilder.like(root.get("email"), "%" + email + "%");
}

---

6. Service Layer to Combine Filters

Now, let's combine multiple filters dynamically based on user input.

@Service
public class UserService {

    @Autowired
    private UserRepository userRepository;

    public List<User> searchUsers(String firstName, String lastName, Integer minAge, String email) {
        Specification<User> spec = Specification.where(UserSpecification.hasFirstName(firstName))
            .and(UserSpecification.hasLastName(lastName))
            .and(UserSpecification.hasAgeGreaterThan(minAge))
            .and(UserSpecification.hasEmailContaining(email));

        return userRepository.findAll(spec);
    }
}

---

7. REST Controller to Expose Filtering

@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/search")
    public ResponseEntity<List<User>> searchUsers(
        @RequestParam(required = false) String firstName,
        @RequestParam(required = false) String lastName,
        @RequestParam(required = false) Integer age,
        @RequestParam(required = false) String email) {
        
        List<User> users = userService.searchUsers(firstName, lastName, age, email);
        return ResponseEntity.ok(users);
    }
}

---

8. Testing the API

You can test the API using Postman or a browser:

GET http://localhost:8080/users/search?firstName=John&age=25

It will return users with the given filters.

---

9. Summary

✅ Implemented Spring Boot with Spring Data JPA
✅ Used JpaSpecificationExecutor<T> for dynamic queries
✅ Created flexible filters with Specification and Criteria API
✅ Built a REST API to fetch users based on dynamic conditions