Spring JPA Queries

Introduction

Spring Data JPA is a powerful tool in the Spring ecosystem that simplifies database access by reducing boilerplate code and providing convenient abstractions. One of its most useful features is the ability to define database queries in multiple ways without writing extensive SQL code.

In this tutorial, we'll explore how Spring Data JPA allows us to create queries through various methods:

Method name-based query derivation
JPQL queries with @Query annotation
Native SQL queries
Query by Example
Specifications

By the end of this guide, you'll understand how to implement different query strategies in your Spring applications and choose the right approach for your specific needs.

Prerequisites

Before diving into Spring JPA queries, you should be familiar with:

Basic Java programming
Basic Spring framework concepts
Core JPA concepts (entities, repositories)

Setting Up Spring Data JPA

Let's start by setting up a simple Spring Boot application with Spring Data JPA. You'll need the following dependencies in your pom.xml:

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

For simplicity, we'll use an H2 in-memory database.

Creating Entity and Repository

Let's create a simple Product entity:

java
package com.example.demo.entity;

import jakarta.persistence.*;
import java.math.BigDecimal;

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String category;
    private BigDecimal price;
    private boolean available;
    
    // Constructors, getters and setters
    
    public Product() {}
    
    public Product(String name, String category, BigDecimal price, boolean available) {
        this.name = name;
        this.category = category;
        this.price = price;
        this.available = available;
    }
    
    // Getters and setters
    public Long getId() {
        return id;
    }
    
    public void setId(Long id) {
        this.id = id;
    }
    
    public String getName() {
        return name;
    }
    
    public void setName(String name) {
        this.name = name;
    }
    
    public String getCategory() {
        return category;
    }
    
    public void setCategory(String category) {
        this.category = category;
    }
    
    public BigDecimal getPrice() {
        return price;
    }
    
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    
    public boolean isAvailable() {
        return available;
    }
    
    public void setAvailable(boolean available) {
        this.available = available;
    }
    
    @Override
    public String toString() {
        return "Product{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", category='" + category + '\'' +
                ", price=" + price +
                ", available=" + available +
                '}';
    }
}

Now, let's create our repository interface:

java
package com.example.demo.repository;

import com.example.demo.entity.Product;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {
    // We'll add our queries here
}

Method Name-Based Query Derivation

One of the most elegant features of Spring Data JPA is its ability to generate query implementations based on method names.

Basic Query Methods

Let's add some basic query methods to our repository:

java
// Find products by category
List<Product> findByCategory(String category);

// Find products by name containing a string (case-insensitive)
List<Product> findByNameContainingIgnoreCase(String name);

// Find available products
List<Product> findByAvailableTrue();

// Find products by price less than a value
List<Product> findByPriceLessThan(BigDecimal price);

// Find products by category ordered by price
List<Product> findByCategoryOrderByPriceAsc(String category);

Complex Query Methods

Let's add some more complex methods:

java
// Find products by category and price range
List<Product> findByCategoryAndPriceBetween(String category, BigDecimal min, BigDecimal max);

// Find products by multiple categories
List<Product> findByCategoryIn(List<String> categories);

// Find available products by name starting with and price less than
List<Product> findByAvailableTrueAndNameStartingWithAndPriceLessThan(String prefix, BigDecimal price);

Using the Query Methods

Here's how you would use these methods in a service:

java
package com.example.demo.service;

import com.example.demo.entity.Product;
import com.example.demo.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.List;

@Service
public class ProductService {

    private final ProductRepository productRepository;
    
    @Autowired
    public ProductService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }
    
    public List<Product> findElectronicsUnder1000() {
        return productRepository.findByCategoryAndPriceBetween("Electronics", 
                                                             BigDecimal.ZERO, 
                                                             new BigDecimal("1000"));
    }
    
    public List<Product> findAvailableProductsStartingWithA() {
        return productRepository.findByAvailableTrueAndNameStartingWithAndPriceLessThan(
                                "A", new BigDecimal("500"));
    }
}

@Query Annotation for JPQL Queries

While derived query methods are convenient, sometimes you need more complex queries. The @Query annotation allows you to write JPQL (Java Persistence Query Language) queries.

Basic JPQL Queries

Add these methods to your repository:

java
@Query("SELECT p FROM Product p WHERE p.category = :category AND p.price < :price")
List<Product> findProductsByCategoryAndPriceLessThan(@Param("category") String category, 
                                                  @Param("price") BigDecimal price);

@Query("SELECT p FROM Product p WHERE p.available = true ORDER BY p.price DESC")
List<Product> findAvailableProductsOrderByPriceDesc();

JPQL WITH Aggregations

java
@Query("SELECT AVG(p.price) FROM Product p WHERE p.category = :category")
BigDecimal findAveragePriceByCategory(@Param("category") String category);

@Query("SELECT p.category, COUNT(p) FROM Product p GROUP BY p.category")
List<Object[]> countProductsByCategory();

Native SQL Queries

For complex queries or database-specific features, you can use native SQL:

java
@Query(value = "SELECT * FROM product WHERE category = ?1 AND price > ?2", nativeQuery = true)
List<Product> findProductsByCategoryAndPriceGreaterThan(String category, BigDecimal price);

@Query(value = "SELECT category, AVG(price) as avg_price FROM product GROUP BY category HAVING AVG(price) > ?1", 
       nativeQuery = true)
List<Object[]> findCategoriesWithAveragePriceGreaterThan(BigDecimal minAvgPrice);

Modifying Queries

Spring Data JPA also supports modifying queries:

java
@Modifying
@Transactional
@Query("UPDATE Product p SET p.available = false WHERE p.category = :category")
int markProductsAsUnavailable(@Param("category") String category);

@Modifying
@Transactional
@Query("DELETE FROM Product p WHERE p.price < :price")
int deleteProductsWithPriceLessThan(@Param("price") BigDecimal price);

Query by Example (QBE)

Query by Example is a user-friendly querying technique that allows dynamic query creation without writing query methods for every scenario.

java
public List<Product> findProductsByExample(String name, String category, BigDecimal maxPrice) {
    Product exampleProduct = new Product();
    
    if (name != null) {
        exampleProduct.setName(name);
    }
    
    if (category != null) {
        exampleProduct.setCategory(category);
    }
    
    // Create an ExampleMatcher for customizing the match criteria
    ExampleMatcher matcher = ExampleMatcher.matching()
        .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
        .withIgnoreCase();
    
    Example<Product> example = Example.of(exampleProduct, matcher);
    
    List<Product> results = productRepository.findAll(example);
    
    // Filter by price if maxPrice is provided (since QBE doesn't handle comparisons well)
    if (maxPrice != null) {
        results = results.stream()
                .filter(p -> p.getPrice().compareTo(maxPrice) <= 0)
                .collect(Collectors.toList());
    }
    
    return results;
}

To use this feature, your repository needs to extend QueryByExampleExecutor:

java
public interface ProductRepository extends JpaRepository<Product, Long>, 
                                        QueryByExampleExecutor<Product> {
    // Other methods
}

Specifications

For even more dynamic query capabilities, JPA Specifications provide a type-safe way to build queries programmatically:

First, make your repository extend JpaSpecificationExecutor:

java
public interface ProductRepository extends JpaRepository<Product, Long>, 
                                        JpaSpecificationExecutor<Product> {
    // Other methods
}

Then create specifications:

java
public List<Product> findProductsBySpecification(String namePattern, 
                                               List<String> categories, 
                                               BigDecimal minPrice,
                                               BigDecimal maxPrice,
                                               Boolean available) {
    
    Specification<Product> spec = Specification.where(null);
    
    if (namePattern != null) {
        spec = spec.and((root, query, cb) -> 
            cb.like(cb.lower(root.get("name")), "%" + namePattern.toLowerCase() + "%"));
    }
    
    if (categories != null && !categories.isEmpty()) {
        spec = spec.and((root, query, cb) -> root.get("category").in(categories));
    }
    
    if (minPrice != null) {
        spec = spec.and((root, query, cb) -> 
            cb.greaterThanOrEqualTo(root.get("price"), minPrice));
    }
    
    if (maxPrice != null) {
        spec = spec.and((root, query, cb) -> 
            cb.lessThanOrEqualTo(root.get("price"), maxPrice));
    }
    
    if (available != null) {
        spec = spec.and((root, query, cb) -> 
            cb.equal(root.get("available"), available));
    }
    
    return productRepository.findAll(spec);
}

Pagination and Sorting

Spring Data JPA makes pagination and sorting simple:

java
public Page<Product> findProductsWithPagination(String category, int page, int size) {
    Pageable pageable = PageRequest.of(page, size, Sort.by("price").ascending());
    return productRepository.findByCategory(category, pageable);
}

To support this method, add this to your repository:

java
Page<Product> findByCategory(String category, Pageable pageable);

Practical Example: Product Search Service

Let's create a complete service that combines multiple query techniques:

java
@Service
public class ProductSearchService {

    private final ProductRepository productRepository;
    
    @Autowired
    public ProductSearchService(ProductRepository productRepository) {
        this.productRepository = productRepository;
    }
    
    public Page<Product> searchProducts(String keyword, List<String> categories, 
                                     BigDecimal minPrice, BigDecimal maxPrice,
                                     Boolean available, int page, int size) {
        
        Specification<Product> spec = Specification.where(null);
        
        // Search by name if keyword is provided
        if (keyword != null && !keyword.trim().isEmpty()) {
            spec = spec.and((root, query, cb) -> 
                cb.like(cb.lower(root.get("name")), "%" + keyword.toLowerCase() + "%"));
        }
        
        // Filter by categories
        if (categories != null && !categories.isEmpty()) {
            spec = spec.and((root, query, cb) -> root.get("category").in(categories));
        }
        
        // Price range filter
        if (minPrice != null) {
            spec = spec.and((root, query, cb) -> 
                cb.greaterThanOrEqualTo(root.get("price"), minPrice));
        }
        
        if (maxPrice != null) {
            spec = spec.and((root, query, cb) -> 
                cb.lessThanOrEqualTo(root.get("price"), maxPrice));
        }
        
        // Availability filter
        if (available != null) {
            spec = spec.and((root, query, cb) -> 
                cb.equal(root.get("available"), available));
        }
        
        // Create pageable object with sorting by price (ascending)
        Pageable pageable = PageRequest.of(page, size, Sort.by("price").ascending());
        
        // Execute the query with specification and pagination
        return productRepository.findAll(spec, pageable);
    }
    
    public List<String> findPopularCategories() {
        List<Object[]> results = productRepository.countProductsByCategory();
        
        // Convert and sort by count (descending)
        return results.stream()
            .sorted((a, b) -> ((Long) b[1]).compareTo((Long) a[1]))
            .map(row -> (String) row[0])
            .collect(Collectors.toList());
    }
    
    public List<Product> findFeaturedProducts() {
        // Use method name query
        List<Product> availableProducts = productRepository.findByAvailableTrue();
        
        // Additional business logic to determine featured products
        // Here we just take the 5 most expensive available products
        return availableProducts.stream()
            .sorted((p1, p2) -> p2.getPrice().compareTo(p1.getPrice()))
            .limit(5)
            .collect(Collectors.toList());
    }
}

Best Practices for Spring JPA Queries

Choose the right query method based on complexity:
- Use method name queries for simple queries
- Use @Query with JPQL for moderate complexity
- Use native queries only when necessary
- Use Specifications for dynamic queries with many optional parameters
Optimize queries by:
- Fetching only the data you need
- Using pagination for large result sets
- Adding appropriate indexes in your database
Handle null parameters properly in dynamic queries
Use DTOs (Data Transfer Objects) for complex queries that don't map directly to entities:

java
@Query("SELECT new com.example.demo.dto.ProductSummary(p.id, p.name, p.price) " +
       "FROM Product p WHERE p.category = :category")
List<ProductSummary> findProductSummariesByCategory(@Param("category") String category);

Consider performance implications of different query types, especially with large datasets

Summary

In this tutorial, we've covered:

Method name-based query derivation for simple queries
Using @Query annotation for JPQL queries
Writing native SQL queries for database-specific features
Query by Example for dynamic queries with simple matching logic
JPA Specifications for complex, dynamic queries
Pagination and sorting for efficient data retrieval
A practical example of combining multiple query approaches

Spring Data JPA provides a rich set of options for accessing your data, from simple to complex queries. By understanding these different query strategies, you can write cleaner, more maintainable code while still having the flexibility to perform complex database operations.

Additional Resources

Exercises

Create a method that finds all products in a specific category that have been created in the last 30 days.
Write a JPQL query that calculates the total value of inventory (sum of price * quantity).
Create a Specification that filters products by multiple categories, a price range, and availability.
Implement pagination for a product search function with sorting options.
Create a native SQL query that returns the top 5 products by sales count using a JOIN to a hypothetical sales table.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Setting Up Spring Data JPA​

Creating Entity and Repository​

Method Name-Based Query Derivation​

Basic Query Methods​

Complex Query Methods​

Using the Query Methods​

@Query Annotation for JPQL Queries​

Basic JPQL Queries​

JPQL WITH Aggregations​

Native SQL Queries​

Modifying Queries​

Query by Example (QBE)​

Specifications​

Pagination and Sorting​

Practical Example: Product Search Service​

Best Practices for Spring JPA Queries​

Summary​

Additional Resources​

Exercises​