Generate the SQL queries dynamically in spring data jpa

Working with databases is a fundamental part of developing most applications, but sometimes, static SQL queries don’t meet the dynamic requirements of real-world applications. For example, what if you need to build a search feature that filters results based on multiple criteria, where not all fields are mandatory? Enter Spring Data JPA Specifications, a powerful tool for creating dynamic and type-safe SQL queries.

In this article, we’ll explore how to use Spring Data JPA Specification to craft flexible SQL queries programmatically without getting bogged down by boilerplate code. By the end, you’ll have a clear understanding and a practical example to get started.

Why Dynamic SQL Queries?

Static SQL queries are straightforward and often sufficient for simple use cases. However, in scenarios where:

Query parameters are conditional (e.g., a search form where users can filter by name, age, or city, or none at all).
You need to construct queries dynamically at runtime based on user input.

Static queries become rigid and unmanageable, leading to cluttered, error-prone code. Dynamic SQL queries solve this by building SQL statements programmatically.

Enter Spring Data JPA Specification

Spring Data JPA’s Specification interface provides a clean and type-safe way to create dynamic queries. It allows you to define predicates (conditions) that combine dynamically at runtime, depending on the input.

Here’s the high-level approach:

Use Specification to define query conditions programmatically.
Combine these conditions dynamically based on input.
Pass the Specification to your repository to execute the query.

Step-by-Step Guide: Creating Dynamic SQL Queries

Let’s build an example of a Customer search feature where users can filter results by name, email, or city.

1. Setting Up the Project

Ensure your Spring Boot project has the following dependencies in pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

Note: We’re using the H2 database for simplicity, but you can replace it with any database of your choice.

2. Define the Entity

Here’s our Customer entity:

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class Customer {

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

    private String name;
    private String email;
    private String city;

    // Getters and Setters
}

3. Create the Repository

The repository should extend JpaSpecificationExecutor to enable Specification-based queries:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface CustomerRepository extends JpaRepository<Customer, Long>, JpaSpecificationExecutor<Customer> {
}

4. Implement Specifications

The core logic for building dynamic queries goes into the Specification implementation:

import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.StringUtils;

public class CustomerSpecifications {

    public static Specification<Customer> hasName(String name) {
        return (root, query, criteriaBuilder) -> 
            StringUtils.hasText(name) ? criteriaBuilder.equal(root.get("name"), name) : null;
    }

    public static Specification<Customer> hasEmail(String email) {
        return (root, query, criteriaBuilder) -> 
            StringUtils.hasText(email) ? criteriaBuilder.equal(root.get("email"), email) : null;
    }

    public static Specification<Customer> hasCity(String city) {
        return (root, query, criteriaBuilder) -> 
            StringUtils.hasText(city) ? criteriaBuilder.equal(root.get("city"), city) : null;
    }
}

5. Combine Specifications

Combine the conditions dynamically in the service layer based on user input:

import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class CustomerService {

    private final CustomerRepository customerRepository;

    public CustomerService(CustomerRepository customerRepository) {
        this.customerRepository = customerRepository;
    }

    public List<Customer> searchCustomers(String name, String email, String city) {
        Specification<Customer> spec = Specification
            .where(CustomerSpecifications.hasName(name))
            .and(CustomerSpecifications.hasEmail(email))
            .and(CustomerSpecifications.hasCity(city));

        return customerRepository.findAll(spec);
    }
}

6. Expose an API

Finally, expose this search functionality via a REST API:

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class CustomerController {

    private final CustomerService customerService;

    public CustomerController(CustomerService customerService) {
        this.customerService = customerService;
    }

    @GetMapping("/customers/search")
    public List<Customer> searchCustomers(
        @RequestParam(required = false) String name,
        @RequestParam(required = false) String email,
        @RequestParam(required = false) String city) {
        return customerService.searchCustomers(name, email, city);
    }
}

Testing the API

Start the application and access the H2 Console at http://localhost:8080/h2-console.
Insert some test data:

INSERT INTO CUSTOMER (NAME, EMAIL, CITY) VALUES 
('John Doe', 'john@example.com', 'New York'),
('Jane Smith', 'jane@example.com', 'Los Angeles'),
('Alice Johnson', 'alice@example.com', 'New York');

Test the API using Postman or curl:

Filter by city:
```
  GET /customers/search?city=New York
```

Filter by name and email:

  GET /customers/search?name=John Doe&email=john@example.com

Key Takeaways

Simplicity: Spring Data JPA Specification eliminates the need for writing raw SQL queries.
Flexibility: Conditions are dynamically combined, making it ideal for scenarios with optional filters.
Scalability: Add more fields or filters easily by creating new Specification methods.

Conclusion

Dynamic SQL queries can greatly simplify the handling of complex and flexible database requirements. Using Spring Data JPA Specifications, we crafted dynamic and type-safe queries programmatically. This approach not only reduces boilerplate code but also makes your application more adaptable to future changes.

Experiment with this example in your projects and see how it transforms your database interactions!

More such articles:

https://medium.com/techwasti

https://www.youtube.com/@maheshwarligade

https://techwasti.com/series/spring-boot-tutorials

https://techwasti.com/series/go-language