[Advanced Spring Boot] Dynamic Queries with Spring Data JPA: Specifications, the Criteria API and Querydsl

A product list needs a search: GET /api/products?q=…&category=…&minPrice=…&maxPrice=…&tag=…&inStock=…, plus a page, a size and a sort. Every filter is optional, so a request may carry none of them, all six, or any combination in between. A derived query method is fixed when the application starts and an @Query string is fixed when it compiles, so neither can add a condition only when its parameter is present. That is what dynamic queries are for: the query is built per request, from the filters that request actually sent.

This article builds that endpoint four ways: with composable Specifications, with the Criteria API they sit on, with Querydsl, and briefly with jOOQ. Before any of them, it shows why the usual workaround, (:x is null or …) in @Query, fails on PostgreSQL and what it does to the query plan. The examples use Spring Boot 4.1.1 and Java 21 against PostgreSQL 18.

The first section sets up the catalogue and the filter record; the rest moves from the query that cannot work to the tools that can, and ends with how to choose between them.

The search endpoint and how the outputs were produced

The project came from Spring Initializr with web, data-jpa, postgresql, flyway and validation:

Bash

curl -s "https://start.spring.io/starter.zip?type=gradle-project&language=java&bootVersion=4.1.1&javaVersion=21&groupId=com.example&artifactId=demo&name=demo&packageName=com.example.demo&dependencies=web,data-jpa,postgresql,flyway,validation" -o demo.zip

The catalogue is the one from the Basics course's article on paging, without its rating column: products, categories, tags and the join table product_tags, now created by Flyway and checked by Hibernate with ddl-auto=validate:

src/main/resources/db/migration/V1__create_catalog.sql

create table categories (
    id   bigint generated by default as identity primary key,
    name varchar(60) not null unique
);
 
create table tags (
    id   bigint generated by default as identity primary key,
    name varchar(40) not null unique
);
 
create table products (
    id          bigint generated by default as identity primary key,
    name        varchar(120)   not null,
    sku         varchar(40)    not null unique,
    price       numeric(10, 2) not null,
    stock       integer        not null,
    category_id bigint         not null references categories (id)
);
 
create index products_category_id_idx on products (category_id);
create index products_price_idx on products (price);
 
create table product_tags (
    product_id bigint not null references products (id),
    tag_id     bigint not null references tags (id),
    primary key (product_id, tag_id)
);
 
create index product_tags_tag_id_idx on product_tags (tag_id);

V2__seed_catalog.sql inserts 23 products: seven keyboards, six mice, four monitors and six accessories, with the tags mechanical, wireless, rgb, bestseller and usb-c. Two details matter later. Ten products carry wireless or usb-c, and one of them, MS-05 Travel mouse, carries both. And one name contains a percent sign, Desk lamp 100% LED, for the section on LIKE.

The entity maps a lazy @ManyToOne to Category and a @ManyToMany to Tag:

src/main/java/com/example/demo/product/Product.java

@Entity
@Table(name = "products")
public class Product {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
 
    @Column(nullable = false, length = 120)
    private String name;
 
    @Column(nullable = false, unique = true, length = 40)
    private String sku;
 
    @Column(nullable = false, precision = 10, scale = 2)
    private BigDecimal price;
 
    @Column(nullable = false)
    private int stock;
 
    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "category_id", nullable = false)
    private Category category;
 
    @ManyToMany
    @JoinTable(name = "product_tags",
            joinColumns = @JoinColumn(name = "product_id"),
            inverseJoinColumns = @JoinColumn(name = "tag_id"))
    private Set<Tag> tags = new HashSet<>();
 
    protected Product() {
    }
 
    public Product(String name, String sku, BigDecimal price, int stock, Category category) {
        this.name = name;
        this.sku = sku;
        this.price = price;
        this.stock = stock;
        this.category = category;
    }
 
    // getters only
}

The six query parameters bind to one record. Spring MVC binds a record's constructor arguments from request parameters by name, so ?tag=wireless&tag=usb-c becomes a two-element list. The compact constructor turns blank strings into null, so ?category= means the same as no category at all:

src/main/java/com/example/demo/product/ProductFilter.java

package com.example.demo.product;
 
import java.math.BigDecimal;
import java.util.List;
 
import jakarta.validation.constraints.AssertTrue;
import jakarta.validation.constraints.PositiveOrZero;
import jakarta.validation.constraints.Size;
 
public record ProductFilter(
        @Size(max = 100) String q,
        @Size(max = 60) String category,
        @PositiveOrZero BigDecimal minPrice,
        @PositiveOrZero BigDecimal maxPrice,
        @Size(max = 5) List<String> tag,
        Boolean inStock) {
 
    public ProductFilter {
        q = blankToNull(q);
        category = blankToNull(category);
        tag = tag == null ? List.of() : tag.stream().filter(t -> !t.isBlank()).toList();
    }
 
    @AssertTrue(message = "minPrice must not be greater than maxPrice")
    public boolean isPriceRangeValid() {
        return minPrice == null || maxPrice == null || minPrice.compareTo(maxPrice) <= 0;
    }
 
    private static String blankToNull(String value) {
        return value == null || value.isBlank() ? null : value.strip();
    }
}

The application talks to a PostgreSQL 18 container, runs on port 8208 and logs Hibernate's SQL:

Bash

docker run -d --name sba-a8-pg -e POSTGRES_USER=demo -e POSTGRES_PASSWORD=demo -e POSTGRES_DB=demo -p 5508:5432 postgres:18

src/main/resources/application.properties

spring.application.name=demo
server.port=8208
spring.datasource.url=jdbc:postgresql://localhost:5508/demo
spring.datasource.username=demo
spring.datasource.password=demo
spring.jpa.open-in-view=false
spring.jpa.hibernate.ddl-auto=validate
logging.level.org.hibernate.SQL=DEBUG

Most repository calls ran in a CommandLineRunner behind a lab profile, which turns the web server off, sets logging.pattern.console=%m%n so each log line is only its message, and switches on org.hibernate.orm.jdbc.bind=TRACE to show the bound values. Each block starts with the call after >>>, then Hibernate's statements, then the result: products as id:SKU price, and for a Page the line numberOfElements=… totalElements=… totalPages=…. An exception prints as !!! lines, one per cause.

Why derived queries and @Query break down

Six optional filters make 2⁶ = 64 combinations of present and absent. A derived query method encodes one combination in its name: findByCategoryName, findByCategoryNameAndPriceLessThanEqual, findByCategoryNameAndPriceLessThanEqualAndStockGreaterThan, and so on, 64 methods for one endpoint, plus a switch that picks one. Nobody writes that, so the usual escape is one @Query that switches each condition off when its parameter is null.

The (:x is null or …) trick and the SQL it produces

src/main/java/com/example/demo/product/ProductRepository.java

    @Query("""
            select p from Product p
            where (:q is null or lower(p.name) like lower(concat('%', :q, '%')))
              and (:category is null or p.category.name = :category)
              and (:minPrice is null or p.price >= :minPrice)
              and (:maxPrice is null or p.price <= :maxPrice)
              and (:inStock is null or :inStock = false or p.stock > 0)
            """)
    List<Product> searchWithNullChecks(String q, String category, BigDecimal minPrice, BigDecimal maxPrice,
                                       Boolean inStock);

The mice up to 50.00 that are in stock, with no text search:

Text

>>> searchWithNullChecks(null, "Mice", null, 50, true)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where (? is null or lower(p1_0.name) like lower(('%'||?||'%')) escape '') and (? is null or c1_0.name=?) and (? is null or p1_0.price>=?) and (? is null or p1_0.price<=?) and (? is null or ?=false or p1_0.stock>0)
binding parameter (1:JAVA_OBJECT) <- [null]
binding parameter (2:JAVA_OBJECT) <- [null]
binding parameter (3:VARCHAR) <- [Mice]
binding parameter (4:VARCHAR) <- [Mice]
binding parameter (5:NUMERIC) <- [null]
binding parameter (6:NUMERIC) <- [null]
binding parameter (7:NUMERIC) <- [50]
binding parameter (8:NUMERIC) <- [50]
binding parameter (9:BOOLEAN) <- [true]
binding parameter (10:BOOLEAN) <- [true]
HHH000247: ErrorCode: 0, SQLState: 42883
ERROR: function lower(bytea) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 185
!!! org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [ERROR: function lower(bytea) does not exist
...
!!! caused by org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [ERROR: function lower(bytea) does not exist
...
!!! caused by org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist

The first two bindings say why. Hibernate could not infer a type for :q, which appears only in is null and inside concat, so it bound the null as JAVA_OBJECT, and PostgreSQL resolved the untyped null inside lower(...) as bytea. The same method with "mouse" bound VARCHAR twice and returned seven products. So the query passes every test that fills in the search box and fails the first time a real request leaves it empty. A cast gives the parameter a type:

src/main/java/com/example/demo/product/ProductRepository.java

            where (:q is null or lower(p.name) like lower(concat('%', :q, '%'))) 
            where (:q is null or lower(p.name) like lower(concat('%', cast(:q as String), '%')))

Text

>>> searchWithNullChecksCast(null, "Mice", null, 50, true)
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where (? is null or lower(p1_0.name) like lower(('%'||cast(? as varchar)||'%')) escape '') and (? is null or c1_0.name=?) and (? is null or p1_0.price>=?) and (? is null or p1_0.price<=?) and (? is null or ?=false or p1_0.stock>0)
binding parameter (1:JAVA_OBJECT) <- [null]
binding parameter (2:JAVA_OBJECT) <- [null]
binding parameter (3:VARCHAR) <- [Mice]
binding parameter (4:VARCHAR) <- [Mice]
binding parameter (5:NUMERIC) <- [null]
binding parameter (6:NUMERIC) <- [null]
binding parameter (7:NUMERIC) <- [50]
binding parameter (8:NUMERIC) <- [50]
binding parameter (9:BOOLEAN) <- [true]
binding parameter (10:BOOLEAN) <- [true]
    [8:MS-01 24.50, 9:MS-02 49.90, 12:MS-05 19.90, 13:MS-06 22.00] (4 rows)

The lab kept both versions side by side, the one with the cast as searchWithNullChecksCast. It works, and the SQL shows what it costs even when it works. Every condition is in every statement, each parameter is bound twice, and the join to categories is there for every request because p.category.name appears in the text, whether or not a category was asked for. The tag filter is not there at all, because a condition on a collection parameter does not fit the pattern, and the % in the text search is not escaped, as Basics 27 showed for concat in JPQL.

Does the null-check query plan badly on PostgreSQL?

To see the plan, the statement ran as a prepared statement in psql, with $1 to $10 in place of Hibernate's placeholders, against a second database, bench, which Flyway migrated and a script filled with 200,000 more products in 200 categories:

seed-bench.sql

select setseed(0.8);
insert into categories (name) select 'Category ' || g from generate_series(5, 200) as g;
insert into products (name, sku, price, stock, category_id)
select 'Product ' || g,
       'SKU-' || lpad(g::text, 6, '0'),
       round((5 + random() * 495)::numeric, 2),
       (random() * 50)::int,
       1 + (g % 200)
from generate_series(1, 200000) as g;
analyze;

The request filters on a price range and nothing else, minPrice=100&maxPrice=101. Next to it, a second prepared statement holds the SQL a Specification generates for the same request, which has only the two conditions that are present:

explain.sql

prepare nullchecks(text, varchar, varchar, varchar, numeric, numeric, numeric, numeric, boolean, boolean) as
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where ($1 is null or lower(p1_0.name) like lower(('%'||cast($2 as varchar)||'%')) escape '') and ($3 is null or c1_0.name=$4) and ($5 is null or p1_0.price>=$6) and ($7 is null or p1_0.price<=$8) and ($9 is null or $10=false or p1_0.stock>0);
prepare spec(numeric, numeric) as
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 where p1_0.price>=$1 and p1_0.price<=$2;
 
set plan_cache_mode = force_custom_plan;
explain (analyze, buffers, timing off, summary off, costs off) execute nullchecks(null, null, null, null, 100, 100, 101, 101, null, null);
set plan_cache_mode = force_generic_plan;
explain (analyze, buffers, timing off, summary off, costs off) execute nullchecks(null, null, null, null, 100, 100, 101, 101, null, null);
explain (analyze, buffers, timing off, summary off, costs off) execute spec(100, 101);

A custom plan is made for one set of values. PostgreSQL folded every null is null to true, dropped those conditions and used the price index; the join to categories stayed:

Text

 Hash Join (actual rows=411.00 loops=1)
   Hash Cond: (p1_0.category_id = c1_0.id)
   Buffers: shared hit=381
   ->  Bitmap Heap Scan on products p1_0 (actual rows=411.00 loops=1)
         Recheck Cond: ((price >= '100'::numeric) AND (price <= '101'::numeric))
         Heap Blocks: exact=374
         Buffers: shared hit=379
         ->  Bitmap Index Scan on products_price_idx (actual rows=411.00 loops=1)
               Index Cond: ((price >= '100'::numeric) AND (price <= '101'::numeric))
               Index Searches: 1
               Buffers: shared hit=5
   ->  Hash (actual rows=200.00 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 16kB
         Buffers: shared hit=2
         ->  Seq Scan on categories c1_0 (actual rows=200.00 loops=1)
               Buffers: shared hit=2

A generic plan is made once, for any values. It cannot know which parameters will be null, so every $n IS NULL OR … stays a filter evaluated row by row, and price >= $6 is no longer something an index can serve:

Text

 Nested Loop (actual rows=411.00 loops=1)
   Buffers: shared hit=200403
   ->  Seq Scan on categories c1_0 (actual rows=200.00 loops=1)
         Filter: (($3 IS NULL) OR ((name)::text = ($4)::text))
         Buffers: shared hit=2
   ->  Bitmap Heap Scan on products p1_0 (actual rows=2.06 loops=200)
         Recheck Cond: (category_id = c1_0.id)
         Filter: ((($5 IS NULL) OR (price >= $6)) AND (($7 IS NULL) OR (price <= $8)) AND (($9 IS NULL) OR (NOT $10) OR (stock > 0)) AND (($1 IS NULL) OR (lower((name)::text) ~~ like_escape(lower((('%'::text || ($2)::text) || '%'::text)), ''::text))))
         Rows Removed by Filter: 998
         Heap Blocks: exact=200001
         Buffers: shared hit=200403
         ->  Bitmap Index Scan on products_category_id_idx (actual rows=1000.12 loops=200)
               Index Cond: (category_id = c1_0.id)
               Index Searches: 200
               Buffers: shared hit=400

It visited every product through the category index to return 411 of them: 200,403 buffers against 381. The Specification's SQL, as a generic plan, still used the price index, because its conditions are plain comparisons:

Text

 Bitmap Heap Scan on products p1_0 (actual rows=411.00 loops=1)
   Recheck Cond: ((price >= $1) AND (price <= $2))
   Heap Blocks: exact=374
   Buffers: shared hit=379
   ->  Bitmap Index Scan on products_price_idx (actual rows=411.00 loops=1)
         Index Cond: ((price >= $1) AND (price <= $2))
         Index Searches: 1
         Buffers: shared hit=5

Whether the null-check query ever gets the generic plan is up to PostgreSQL's plan cache. With the default plan_cache_mode = auto, seven more executions with different price ranges all got custom plans; pg_prepared_statements reported generic_plans 1, the forced one above, and custom_plans 8. So the bad plan is a risk that depends on a cost estimate and a setting, not a certainty. What is certain is in the SQL: every request pays for every condition and for the join. A query built from only the filters that are present has neither problem, which is what the rest of this article builds.

Specifications with JpaSpecificationExecutor

A Specification<T> is a function from the pieces of a JPA Criteria query to one condition: toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb) returns a Predicate. A repository gets methods that accept one by extending JpaSpecificationExecutor<T>:

src/main/java/com/example/demo/product/ProductRepository.java

package com.example.demo.product;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
 
public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
}

In 4.1.1 that interface has findOne, findAll with and without a Sort or a Pageable, findAll(spec, countSpec, pageable) with a separate specification for the count, count, exists, update, delete and the fluent findBy, most of them in two overloads, one taking a Specification and one taking a PredicateSpecification, which the section on the Spring Data 4 API comes back to.

One small Specification per filter

Each filter becomes a static factory that returns a Specification<Product>. The first version names attributes with strings, which the section on the Criteria API replaces:

src/main/java/com/example/demo/product/ProductSpecifications.java

package com.example.demo.product;
 
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Locale;
 
import jakarta.persistence.criteria.Join;
 
import org.springframework.data.jpa.domain.Specification;
 
public final class ProductSpecifications {
 
    private ProductSpecifications() {
    }
 
    public static Specification<Product> nameContains(String text) {
        String pattern = "%" + escapeLike(text.toLowerCase(Locale.ROOT)) + "%";
        return (root, query, cb) -> cb.like(cb.lower(root.get("name")), pattern, '\\');
    }
 
    public static Specification<Product> inCategory(String categoryName) {
        return (root, query, cb) -> cb.equal(root.get("category").get("name"), categoryName);
    }
 
    public static Specification<Product> priceAtLeast(BigDecimal min) {
        return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get("price"), min);
    }
 
    public static Specification<Product> priceAtMost(BigDecimal max) {
        return (root, query, cb) -> cb.lessThanOrEqualTo(root.get("price"), max);
    }
 
    public static Specification<Product> inStock() {
        return (root, query, cb) -> cb.greaterThan(root.get("stock"), 0);
    }
 
    public static Specification<Product> hasAnyTag(Collection<String> tagNames) {
        return (root, query, cb) -> {
            Join<Product, Tag> tags = root.join("tags");
            return tags.get("name").in(tagNames);
        };
    }
 
    public static Specification<Product> matching(ProductFilter filter) {
        List<Specification<Product>> specs = new ArrayList<>();
        if (filter.q() != null) specs.add(nameContains(filter.q()));
        if (filter.category() != null) specs.add(inCategory(filter.category()));
        if (filter.minPrice() != null) specs.add(priceAtLeast(filter.minPrice()));
        if (filter.maxPrice() != null) specs.add(priceAtMost(filter.maxPrice()));
        if (!filter.tag().isEmpty()) specs.add(hasAnyTag(filter.tag()));
        if (Boolean.TRUE.equals(filter.inStock())) specs.add(inStock());
        return Specification.allOf(specs);
    }
 
    static String escapeLike(String text) {
        return text.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_");
    }
}

The factories never see null. matching decides which filters are present and adds one specification for each; the factories only describe a condition. hasAnyTag is the naive version, and the section on to-many joins shows what it does to a page.

The service passes the combined specification and the Pageable to the repository, and the controller binds both from the request:

src/main/java/com/example/demo/product/ProductService.java

    @Transactional(readOnly = true)
    public Page<ProductResponse> search(ProductFilter filter, Pageable pageable) {
        return repository.findAll(ProductSpecifications.matching(filter), pageable)
                .map(ProductResponse::from);
    }

src/main/java/com/example/demo/product/ProductController.java

    @GetMapping
    public PageResponse<ProductResponse> search(@Valid ProductFilter filter,
                                                @PageableDefault(size = 20, sort = "id") Pageable pageable) {
        return PageResponse.from(service.search(filter, pageable));
    }

ProductResponse is a record with the id, name, SKU, price, stock and category name, and PageResponse is the record from the Basics course's paging article.

Composing specifications with allOf, and, or and not

Specification.allOf(specs) joins a list with and, and an empty list gives a specification without a condition. The same filter object with only three fields set, category Mice, maximum price 50 and in stock:

Text

>>> findAll(matching(category=Mice, maxPrice=50, inStock=true), page 0 size 3 sort price,id)
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? and p1_0.price<=? and p1_0.stock>? order by p1_0.price,p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [Mice]
binding parameter (2:NUMERIC) <- [50]
binding parameter (3:INTEGER) <- [0]
binding parameter (4:INTEGER) <- [0]
binding parameter (5:INTEGER) <- [3]
select count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? and p1_0.price<=? and p1_0.stock>?
binding parameter (1:VARCHAR) <- [Mice]
binding parameter (2:NUMERIC) <- [50]
binding parameter (3:INTEGER) <- [0]
    content [12:MS-05 19.90, 13:MS-06 22.00, 8:MS-01 24.50]
    numberOfElements=3 totalElements=4 totalPages=2

Three filters, three conditions, nothing else; Hibernate bound even the literal 0 of stock > 0 as a parameter. With every field empty, the list is empty and the query has no where at all:

Text

>>> findAll(matching(empty filter), page 0 size 3)
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:INTEGER) <- [0]
binding parameter (2:INTEGER) <- [3]
select count(p1_0.id) from products p1_0
    content [1:KB-01 89.90, 2:KB-02 59.00, 3:KB-03 45.50]
    numberOfElements=3 totalElements=23 totalPages=8

Three filters present and three absent in a request, the specifications built for the three present ones, allOf combining them into one AND predicate, and the WHERE clause Hibernate generated from it

Specifications also combine with and, or and Specification.not, and the resulting tree keeps its grouping in the SQL:

Java

repository.findAll(inCategory("Mice").or(inCategory("Keyboards")).and(Specification.not(inStock())));

Text

>>> findAll(inCategory(Mice).or(inCategory(Keyboards)).and(Specification.not(inStock())))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where (c1_0.name=? or c1_0.name=?) and p1_0.stock<=?
binding parameter (1:VARCHAR) <- [Mice]
binding parameter (2:VARCHAR) <- [Keyboards]
binding parameter (3:INTEGER) <- [0]
    [3:KB-03 45.50, 10:MS-03 39.00] (2 rows)

The or became a parenthesised group, which is exactly what a derived query name cannot express. Hibernate wrote not (stock > 0) as stock <= ?.

What changed in the Spring Data JPA 4 Specification API

Most examples of Specifications were written for Spring Data JPA 2 or 3, and they rely on null being harmless: a factory returns null when its parameter is missing, and Specification.where(a).and(b).and(c) skips it. In spring-data-jpa 3.5.0 that was still how where behaved: its bytecode returns a specification without a condition when the argument is null, and the method carries @Deprecated(since = "3.5.0", forRemoval = true). In 4.1.1 where is no longer deprecated, javap -v finds no @Deprecated on any method of Specification or JpaSpecificationExecutor, and the null tolerance is gone:

Text

>>> Specification.where((Specification<Product>) null)
!!! java.lang.IllegalArgumentException: Specification must not be null
>>> inStock().and((Specification<Product>) null)
!!! java.lang.IllegalArgumentException: Other specification must not be null
>>> Specification.allOf(inStock(), null)
!!! java.lang.IllegalArgumentException: Other specification must not be null

So null-returning factories from older code fail on the first request that leaves a filter out. The 4.x ways to say "no condition" all worked and all produced a query without a where: Specification.unrestricted(), allOf over an empty list as in matching, and a toPredicate that returns null, which the interface marks as JSpecify @Nullable.

Spring Data JPA 4 also added three siblings that 3.5.0 does not have:

Type	`toPredicate` receives	Used by
`Specification<T>`	`Root<T>`, `CriteriaQuery<?>`, `CriteriaBuilder`	`findOne`, `findAll` with and without `Sort` or `Pageable`, `count`, `exists`, `findBy`
`PredicateSpecification<T>`	`From<?, T>`, `CriteriaBuilder`	`findOne`, `findAll` without paging, `count`, `exists`, `delete`, `findBy`; also accepted by the `where`, `and` and `or` overloads of the other three
`UpdateSpecification<T>`	`Root<T>`, `CriteriaUpdate<T>`, `CriteriaBuilder`	`update`
`DeleteSpecification<T>`	`Root<T>`, `CriteriaDelete<T>`, `CriteriaBuilder`	`delete`

A PredicateSpecification has no query to touch, so it cannot add a subquery or distinct; in exchange, one condition serves a select, an update and a delete:

Java

PredicateSpecification<Product> soldOut = (from, cb) -> cb.equal(from.get(Product_.stock), 0);
UpdateSpecification<Product> restock = UpdateSpecification.<Product>update(
        (root, update, cb) -> update.set(root.get(Product_.stock), 5)).where(soldOut);
long updated = repository.update(restock);

Text

>>> repository.count(soldOut)
select count(p1_0.id) from products p1_0 where p1_0.stock=?
binding parameter (1:INTEGER) <- [0]
    returned 2 (java.lang.Long)
>>> repository.update(restock) without a transaction
update products p1_0 set stock=? where p1_0.stock=?
binding parameter (1:INTEGER) <- [5]
binding parameter (2:INTEGER) <- [0]
    returned 2 (java.lang.Long)

Unlike a @Modifying query method, which Basics 27 saw fail with "No active transaction for update or delete query", update ran without a surrounding transaction, and psql afterwards showed both rows at stock 5: the repository method committed in a transaction of its own.

Escaping % and _ in a LIKE specification

Basics 27 found that Spring Data escapes % and _ for derived Containing queries and not for hand-written JPQL. A Specification is neither: the Criteria API passes the pattern to Hibernate as given, and escaping is the factory's job. nameContains escapes the backslash, % and _, then declares \ as the escape character with the third argument of cb.like:

Text

>>> findAll(nameContains("%"))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 where lower(p1_0.name) like ? escape '\'
binding parameter (1:VARCHAR) <- [%\%%]
    [23:AC-06 45.50] (1 rows)
>>> findAll(nameContains("_"))
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 where lower(p1_0.name) like ? escape '\'
binding parameter (1:VARCHAR) <- [%\_%]
    [] (0 rows)
>>> findAll(cb.like(lower(name), "%" + "%" + "%")) without escaping
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 where lower(p1_0.name) like ? escape ''
binding parameter (1:VARCHAR) <- [%%%]
    [1:KB-01 89.90, 2:KB-02 59.00, 3:KB-03 45.50, 4:KB-04 129.00, 5:KB-05 99.00, 6:KB-06 74.00, 7:KB-07 19.90, 8:MS-01 24.50, 9:MS-02 49.90, 10:MS-03 39.00, 11:MS-04 54.00, 12:MS-05 19.90, 13:MS-06 22.00, 14:MN-01 279.00, 15:MN-02 449.00, 16:MN-03 189.00, 17:MN-04 399.00, 18:AC-01 39.00, 19:AC-02 34.90, 20:AC-03 19.90, 21:AC-04 64.00, 22:AC-05 12.50, 23:AC-06 45.50] (23 rows)

Escaped, a % found the one product with a percent sign in its name. Unescaped, Hibernate wrote escape '', which turns escaping off, and the user's % matched all 23 products. The escape method must escape the escape character first, or a user's backslash would swallow the next character.

Paging a specification: two SQL statements

findAll(spec, pageable) sends the row query with offset and fetch first, then a count query built from the same specification, as the three-filter run above showed. Spring Data skips the count when the first page is not full, since the total is then the page's own size. ProductResponse needs the category name, and open-in-view is off, so the category has to come with the product. Redeclaring the executor's method with an entity graph does that for this one query:

src/main/java/com/example/demo/product/ProductRepository.java

public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
 
    @Override
    @EntityGraph(attributePaths = "category") 
    Page<Product> findAll(Specification<Product> spec, Pageable pageable); 
}

That is where c1_0.id,c1_0.name in the row query above came from, and the count query did not get the join unless a filter needed it. When the category filter is present, Hibernate used a single join for both the fetch and the condition. Loading associations efficiently is article 5's subject; what matters here is where the fetch goes. Put root.fetch("category") inside a specification instead, and the row query runs, then the count query fails, because Spring Data applies the same specification to a query that selects count(...):

Text

>>> findAll(spec with root.fetch(category), page 0 size 3)
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where p1_0.price<=? order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:NUMERIC) <- [50]
binding parameter (2:INTEGER) <- [0]
binding parameter (3:INTEGER) <- [3]
!!! org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.query.SemanticException: Query specified join fetching, but the owner of the fetched association was not present in the select list [SqmSingularJoin(com.example.demo.product.Product(var_1).category(var_1_1) : category)]
!!! caused by java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Query specified join fetching, but the owner of the fetched association was not present in the select list [SqmSingularJoin(com.example.demo.product.Product(var_1).category(var_1_1) : category)]
!!! caused by org.hibernate.query.SemanticException: Query specified join fetching, but the owner of the fetched association was not present in the select list [SqmSingularJoin(com.example.demo.product.Product(var_1).category(var_1_1) : category)]

A specification is called once per query Spring Data builds from it, so it should describe a condition and leave the shape of the result to the repository.

Filtering on a to-many join: duplicates, distinct and exists

tag=wireless&tag=usb-c asks for products carrying either tag. Ten products match, and MS-05 matches twice, once per tag. hasAnyTag above joins the collection, and a join produces one row per matching tag.

What a plain join does to a page

Page 0 and page 1 with five products per page:

Text

>>> findAll(hasAnyTag(wireless, usb-c) with a plain join, page 0 size 5)
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join product_tags t1_0 on p1_0.id=t1_0.product_id join tags t1_1 on t1_1.id=t1_0.tag_id join categories c1_0 on c1_0.id=p1_0.category_id where t1_1.name in (?,?) order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
binding parameter (3:INTEGER) <- [0]
binding parameter (4:INTEGER) <- [5]
select count(p1_0.id) from products p1_0 join product_tags t1_0 on p1_0.id=t1_0.product_id join tags t1_1 on t1_1.id=t1_0.tag_id where t1_1.name in (?,?)
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
    content [3:KB-03 45.50, 6:KB-06 74.00, 8:MS-01 24.50, 11:MS-04 54.00, 12:MS-05 19.90]
    numberOfElements=5 totalElements=11 totalPages=3
>>> findAll(hasAnyTag plain join, page 1 size 5)
    content [12:MS-05 19.90, 14:MN-01 279.00, 15:MN-02 449.00, 16:MN-03 189.00, 18:AC-01 39.00]
    numberOfElements=5 totalElements=11 totalPages=3

Two things are already wrong. The count counted join rows, 11, not products, 10, so the pager promises three pages of a result that fits in two. And MS-05 is the last product of page 0 and the first of page 1, because its two rows fell on either side of the offset. With six per page, which the lab ran under the name joinTags, the same plain join, it gets worse:

Text

>>> findAll(joinTags(wireless, usb-c), page 0 size 6) [entity graph]
    content [3:KB-03 45.50, 6:KB-06 74.00, 8:MS-01 24.50, 11:MS-04 54.00, 12:MS-05 19.90]
    numberOfElements=5 totalElements=5 totalPages=1
>>> findAll(joinTags(wireless, usb-c), page 1 size 6) [entity graph]
    content [14:MN-01 279.00, 15:MN-02 449.00, 16:MN-03 189.00, 18:AC-01 39.00, 21:AC-04 64.00]
    numberOfElements=5 totalElements=11 totalPages=2

The database returned six rows for page 0, MS-05 twice. Hibernate 7.4 removed the repeated entity from the result list, so the page held five products. Spring Data saw five results for a page of six, concluded that this was the last page, skipped the count query and reported totalElements=5, totalPages=1. A client would stop there and never see the five products on page 1. The same specification as a plain List returned 10 products from the 11 rows psql counted for the join, so the in-memory de-duplication is Hibernate's and does not depend on paging.

query.distinct(true) and the count query

The textbook fix asks the query for distinct rows:

Java

static Specification<Product> distinctTags(List<String> tagNames) {
    return (root, query, cb) -> {
        query.distinct(true);
        Join<Product, Tag> tags = root.join("tags");
        return tags.get("name").in(tagNames);
    };
}

Text

>>> findAll(hasAnyTag with query.distinct(true), page 0 size 5)
select distinct p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join product_tags t1_0 on p1_0.id=t1_0.product_id join tags t1_1 on t1_1.id=t1_0.tag_id join categories c1_0 on c1_0.id=p1_0.category_id where t1_1.name in (?,?) order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
binding parameter (3:INTEGER) <- [0]
binding parameter (4:INTEGER) <- [5]
select distinct count(distinct p1_0.id) from products p1_0 join product_tags t1_0 on p1_0.id=t1_0.product_id join tags t1_1 on t1_1.id=t1_0.tag_id where t1_1.name in (?,?)
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
    content [3:KB-03 45.50, 6:KB-06 74.00, 8:MS-01 24.50, 11:MS-04 54.00, 12:MS-05 19.90]
    numberOfElements=5 totalElements=10 totalPages=2

The row query became select distinct, and because the specification also ran against the count query and set distinct there, Spring Data counted count(distinct p1_0.id): 10, correct. The count query came out as select distinct count(distinct …), harmless but a sign of a flag set on a query it was not meant for. distinct has a cost of its own: the database has to compare whole rows, and PostgreSQL requires every order by expression to be in the select list. With the entity graph, the category columns are selected and sort=category.name worked. Through findAll(spec, Sort), which has no entity graph, the same sort failed:

Text

>>> findAll(hasAnyTag with query.distinct(true), Sort.by(category.name)) without entity graph
select distinct p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join product_tags t1_0 on p1_0.id=t1_0.product_id join tags t1_1 on t1_1.id=t1_0.tag_id join categories c1_0 on c1_0.id=p1_0.category_id where t1_1.name in (?,?) order by c1_0.name
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
HHH000247: ErrorCode: 0, SQLState: 42P10
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
  Position: 275
!!! org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
...

An exists subquery instead of a join

The question "does this product have one of these tags?" is a yes or no per product, which is what exists asks. A correlated subquery keeps the collection out of the outer query entirely:

src/main/java/com/example/demo/product/ProductSpecifications.java

    public static Specification<Product> hasAnyTag(Collection<String> tagNames) {
        return (root, query, cb) -> {
            Join<Product, Tag> tags = root.join("tags"); 
            return tags.get("name").in(tagNames); 
            Subquery<Integer> sub = query.subquery(Integer.class); 
            Join<Product, Tag> tags = sub.correlate(root).join("tags"); 
            sub.select(cb.literal(1)).where(tags.get("name").in(tagNames)); 
            return cb.exists(sub); 
        };
    }

Text

>>> findAll(hasAnyTag as correlated exists, page 0 size 5)
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where exists(select 1 from product_tags t1_0 join tags t1_1 on t1_1.id=t1_0.tag_id where t1_1.name in (?,?) and p1_0.id=t1_0.product_id) order by p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
binding parameter (3:INTEGER) <- [0]
binding parameter (4:INTEGER) <- [5]
select count(p1_0.id) from products p1_0 where exists(select 1 from product_tags t1_0 join tags t1_1 on t1_1.id=t1_0.tag_id where t1_1.name in (?,?) and p1_0.id=t1_0.product_id)
binding parameter (1:VARCHAR) <- [wireless]
binding parameter (2:VARCHAR) <- [usb-c]
    content [3:KB-03 45.50, 6:KB-06 74.00, 8:MS-01 24.50, 11:MS-04 54.00, 12:MS-05 19.90]
    numberOfElements=5 totalElements=10 totalPages=2

One row per product, a plain count(p1_0.id) of 10, and no distinct. Page 1 of six held the remaining four products, and sort=category.name worked through both findAll methods. sub.correlate(root) is what made Hibernate write p1_0.id=t1_0.product_id against the outer query's alias; a subquery with its own from(Product.class) and an equal on the ids returned the same ten products, but its SQL put a second copy of products inside the subquery, select p2_0.id from products p2_0 join product_tags … where p2_0.id=p1_0.id and …. This version needs query.subquery, which is why hasAnyTag stays a Specification and could not be a PredicateSpecification.

The Criteria API behind Specifications

A Specification is a thin layer over the JPA Criteria API: Spring Data creates the CriteriaQuery, the Root and the CriteriaBuilder, calls toPredicate, and does the rest. Two things are worth taking from the layer below: type-safe attribute references, and queries whose shape a Specification cannot change.

The JPA static metamodel with hibernate-processor

root.get("prize") compiles. It fails when the query is built, at the first request that uses the filter, not at startup as a misspelled derived query does:

Text

>>> findAll(root.get("prize"))
!!! org.springframework.dao.InvalidDataAccessApiUsageException: Could not resolve attribute 'prize' of 'com.example.demo.product.Product'
!!! caused by org.hibernate.query.sqm.PathElementException: Could not resolve attribute 'prize' of 'com.example.demo.product.Product'

Hibernate's annotation processor generates a static metamodel class per entity at compile time. The artifact is org.hibernate.orm:hibernate-processor, and Spring Boot manages its version, which resolved to 7.4.5.Final; hibernate-jpamodelgen 7.4.5.Final still exists on Maven Central, but only as a relocation POM whose description reads "The hibernate-jpamodelgen module has been renamed hibernate-processor".

build.gradle

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    // the other starters, flyway-database-postgresql and the PostgreSQL driver as generated
    annotationProcessor 'org.hibernate.orm:hibernate-processor'
}

compileJava printed Note: Hibernate compile-time tooling 7.4.5.Final and wrote Product_, Category_ and Tag_ to build/generated/sources/annotationProcessor/java/main, which Gradle compiles with the rest. Each class has a typed attribute and a string constant per field:

build/generated/sources/annotationProcessor/java/main/com/example/demo/product/Product_.java

@StaticMetamodel(Product.class)
@Generated("org.hibernate.processor.HibernateProcessor")
public abstract class Product_ {
 
	public static final String ID = "id";
	public static final String NAME = "name";
	public static final String SKU = "sku";
	public static final String PRICE = "price";
	public static final String STOCK = "stock";
	public static final String CATEGORY = "category";
	public static final String TAGS = "tags";
 
	public static volatile EntityType<Product> class_;
	public static volatile SingularAttribute<Product, Long> id;
	public static volatile SingularAttribute<Product, String> name;
	public static volatile SingularAttribute<Product, String> sku;
	public static volatile SingularAttribute<Product, BigDecimal> price;
	public static volatile SingularAttribute<Product, Integer> stock;
	public static volatile SingularAttribute<Product, Category> category;
	public static volatile SetAttribute<Product, Tag> tags;
}

The specifications switch to it:

src/main/java/com/example/demo/product/ProductSpecifications.java

        return (root, query, cb) -> cb.like(cb.lower(root.get("name")), pattern, '\\'); 
        return (root, query, cb) -> cb.like(cb.lower(root.get(Product_.name)), pattern, '\\'); 
        return (root, query, cb) -> cb.equal(root.get("category").get("name"), categoryName); 
        return (root, query, cb) -> cb.equal(root.get(Product_.category).get(Category_.name), categoryName); 
        return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get("price"), min); 
        return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get(Product_.price), min); 
        return (root, query, cb) -> cb.lessThanOrEqualTo(root.get("price"), max); 
        return (root, query, cb) -> cb.lessThanOrEqualTo(root.get(Product_.price), max); 
        return (root, query, cb) -> cb.greaterThan(root.get("stock"), 0); 
        return (root, query, cb) -> cb.greaterThan(root.get(Product_.stock), 0); 
            Join<Product, Tag> tags = sub.correlate(root).join("tags"); 
            Join<Product, Tag> tags = sub.correlate(root).join(Product_.tags); 
            sub.select(cb.literal(1)).where(tags.get("name").in(tagNames)); 
            sub.select(cb.literal(1)).where(tags.get(Tag_.name).in(tagNames));

The generated SQL did not change. The compiler's view did: a misspelled attribute and a comparison with the wrong type, which the string version accepted, both stopped the build:

Text

src/main/java/com/example/demo/product/ProductSpecifications.java:29: error: cannot find symbol
        return (root, query, cb) -> cb.greaterThanOrEqualTo(root.get(Product_.prize), min);
                                                                             ^
  symbol:   variable prize
  location: class Product_
src/main/java/com/example/demo/product/ProductSpecifications.java:37: error: no suitable method found for greaterThan(Path<Integer>,BigDecimal)
        return (root, query, cb) -> cb.greaterThan(root.get(Product_.stock), new BigDecimal("0.5"));
                                      ^

With strings, the same comparison, cb.greaterThan(root.get("stock"), new BigDecimal("0.5")), compiled and failed at its first call with JpaSystemException: Error coercing value, caused by ArithmeticException: Rounding necessary. The string constants have a use too: Product_.PRICE is the name the sort whitelist in the section on validation compares against.

The same search in a custom repository fragment

A repository fragment is an interface plus a class named after it with the Impl suffix; Spring Data finds the class and merges its methods into the repository proxy:

src/main/java/com/example/demo/product/ProductSearchRepository.java

package com.example.demo.product;
 
import java.util.List;
 
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
 
public interface ProductSearchRepository {
 
    Page<Product> search(ProductFilter filter, Pageable pageable);
 
    List<CategoryCount> countByCategory(ProductFilter filter);
}

src/main/java/com/example/demo/product/ProductRepository.java

public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> { 
public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product>, 
        ProductSearchRepository {

search does by hand what findAll(spec, pageable) did for the Specification. It reuses the same Specification, because a Specification is only a function from a root, a query and a builder to a predicate, and any Criteria query can call it:

src/main/java/com/example/demo/product/ProductSearchRepositoryImpl.java

class ProductSearchRepositoryImpl implements ProductSearchRepository {
 
    private final EntityManager entityManager;
 
    ProductSearchRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
 
    @Override
    public Page<Product> search(ProductFilter filter, Pageable pageable) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        Specification<Product> spec = ProductSpecifications.matching(filter);
 
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);
        product.fetch(Product_.category);
        query.select(product);
        Predicate where = spec.toPredicate(product, query, cb);
        if (where != null) {
            query.where(where);
        }
        query.orderBy(QueryUtils.toOrders(pageable.getSort(), product, cb));
        List<Product> content = entityManager.createQuery(query)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();
 
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<Product> counted = countQuery.from(Product.class);
        countQuery.select(cb.count(counted));
        Predicate countWhere = spec.toPredicate(counted, countQuery, cb);
        if (countWhere != null) {
            countQuery.where(countWhere);
        }
        return PageableExecutionUtils.getPage(content, pageable,
                () -> entityManager.createQuery(countQuery).getSingleResult());
    }
}

That is the list of what Specifications hide: two queries with two roots, the specification applied to each, the Sort turned into Order objects by Spring Data's QueryUtils.toOrders, the offset and limit, and PageableExecutionUtils, which runs the count supplier only when the page does not already reveal the total. Here the fetch is safe, because it is on the row query only. With category Mice, maximum price 50, both tags and in stock:

Text

>>> search(category=Mice, maxPrice=50, tag=[wireless, usb-c], inStock=true), page 0 size 3 sort price,id [Criteria]
select p1_0.id,p1_0.category_id,c1_0.id,c1_0.name,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? and p1_0.price<=? and exists(select 1 from product_tags t1_0 join tags t1_1 on t1_1.id=t1_0.tag_id where t1_1.name in (?,?) and p1_0.id=t1_0.product_id) and p1_0.stock>? order by p1_0.price,p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [Mice]
binding parameter (2:NUMERIC) <- [50]
binding parameter (3:VARCHAR) <- [wireless]
binding parameter (4:VARCHAR) <- [usb-c]
binding parameter (5:INTEGER) <- [0]
binding parameter (6:INTEGER) <- [0]
binding parameter (7:INTEGER) <- [3]
    content [12:MS-05 19.90, 8:MS-01 24.50]
    numberOfElements=2 totalElements=2 totalPages=1

Two products on a page of three, so PageableExecutionUtils never called the count. Page 1 of maxPrice=50 did need it and sent select count(p1_0.id) from products p1_0 where p1_0.price<=?: 12 products.

CriteriaQuery with its Root, the fetch join, four predicates built from metamodel attributes, the order and the page limits, each mapped to the SQL clause it produced

A count per category that a Specification cannot return

A search page usually shows counts next to the filters: how many results each category has for the current search. Every method of JpaSpecificationExecutor returns entities, a count or a boolean; none can group. A Criteria tuple query can, and it still reuses the Specification for the where:

src/main/java/com/example/demo/product/ProductSearchRepositoryImpl.java

    @Override
    public List<CategoryCount> countByCategory(ProductFilter filter) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> query = cb.createTupleQuery();
        Root<Product> product = query.from(Product.class);
        Join<Product, Category> category = product.join(Product_.category);
        Path<String> categoryName = category.get(Category_.name);
 
        query.multiselect(categoryName, cb.count(product));
        Predicate where = ProductSpecifications.matching(filter).toPredicate(product, query, cb);
        if (where != null) {
            query.where(where);
        }
        query.groupBy(categoryName).orderBy(cb.asc(categoryName));
 
        return entityManager.createQuery(query).getResultList().stream()
                .map(row -> new CategoryCount(row.get(0, String.class), row.get(1, Long.class)))
                .toList();
    }

Text

>>> countByCategory(maxPrice=50, inStock=true) [Criteria]
select c1_0.name,count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where p1_0.price<=? and p1_0.stock>? group by 1 order by 1
binding parameter (1:NUMERIC) <- [50]
binding parameter (2:INTEGER) <- [0]
    [CategoryCount[category=Accessories, products=5], CategoryCount[category=Keyboards, products=1], CategoryCount[category=Mice, products=4]]
>>> countByCategory(category=Mice) [Criteria]
select c1_0.name,count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? group by 1 order by 1
binding parameter (1:VARCHAR) <- [Mice]
    [CategoryCount[category=Mice, products=6]]

Hibernate wrote group by 1 order by 1, positions in the select list, and with the category filter present the specification's category.name reused the explicit join rather than adding a second one. The rows are mapped from Tuple by position here; selecting straight into a record or an interface, a projection, is article 5's subject. Exposed as GET /api/products/category-counts, the first call answered:

Text

[{"category":"Accessories","products":5},{"category":"Keyboards","products":1},{"category":"Mice","products":4}]

Querydsl 5.1 with Spring Boot 4 and Hibernate 7

Querydsl generates a query type per entity, QProduct for Product, and builds queries from them with a fluent API. Its last release under com.querydsl is 5.1.0, from before Hibernate 7 existed, so whether it still works is a fair question. Spring Boot 4.1.1's dependency management sets querydsl.version to 5.1.0, and spring-data-jpa 4.1.1's POM declares querydsl-jpa with the jakarta classifier as an optional dependency. Every step below was run on Hibernate 7.4.5, and none of them failed.

Setup: the jakarta classifier and the Q-classes

build.gradle

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    // ...
    implementation 'com.querydsl:querydsl-jpa::jakarta'
    annotationProcessor 'com.querydsl:querydsl-apt::jakarta'
    annotationProcessor 'jakarta.persistence:jakarta.persistence-api'
    annotationProcessor 'org.hibernate.orm:hibernate-processor'
}

Three details in four lines. The empty version between :: lets Boot's dependency management supply 5.1.0 while the coordinates still name the jakarta classifier. Without the classifier you get the build for the old javax.persistence API: javap on its JPAQueryFactory shows a constructor taking javax.persistence.EntityManager, where the jakarta jar's takes jakarta.persistence.EntityManager, the type Hibernate 7 provides. The map form many guides use, implementation(group: 'com.querydsl', name: 'querydsl-jpa', classifier: 'jakarta'), still works on Gradle 9.7.1 but logs "Declaring dependencies using multi-string notation has been deprecated. This will fail with an error in Gradle 10. Please use single-string notation ("com.querydsl:querydsl-jpa::jakarta")". And querydsl-apt needs the persistence API on the processor path: with only the Querydsl processor there, compileJava failed with java.lang.NoClassDefFoundError: jakarta/persistence/Entity. hibernate-processor brings the API along transitively, so in this build the third line is redundant, but it keeps Querydsl working if the metamodel processor goes.

Both processors ran in one compile: QProduct, QCategory and QTag appeared beside Product_, Category_ and Tag_ in the same generated sources directory.

The search as a BooleanBuilder with QuerydslPredicateExecutor

BooleanBuilder is a mutable predicate that starts empty, and and on it appends a condition:

src/main/java/com/example/demo/product/ProductPredicates.java

package com.example.demo.product;
 
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Predicate;
 
public final class ProductPredicates {
 
    private ProductPredicates() {
    }
 
    public static Predicate matching(ProductFilter filter) {
        QProduct product = QProduct.product;
        BooleanBuilder where = new BooleanBuilder();
        if (filter.q() != null) where.and(product.name.containsIgnoreCase(filter.q()));
        if (filter.category() != null) where.and(product.category.name.eq(filter.category()));
        if (filter.minPrice() != null) where.and(product.price.goe(filter.minPrice()));
        if (filter.maxPrice() != null) where.and(product.price.loe(filter.maxPrice()));
        if (!filter.tag().isEmpty()) where.and(product.tags.any().name.in(filter.tag()));
        if (Boolean.TRUE.equals(filter.inStock())) where.and(product.stock.gt(0));
        return where;
    }
}

QuerydslPredicateExecutor<Product> on the repository adds findAll(Predicate, Pageable) and its siblings:

src/main/java/com/example/demo/product/ProductRepository.java

public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product>,
        ProductSearchRepository { 
        QuerydslPredicateExecutor<Product>, ProductSearchRepository {

A predicate prints as something close to the query it stands for:

Text

    predicate: product.category.name = Mice && product.price <= 50 && any(product.tags).name in [wireless, usb-c] && product.stock > 0
>>> findAll(ProductPredicates.matching(category=Mice, maxPrice=50, tag=[wireless, usb-c], inStock=true), page 0 size 3 sort price,id) [Querydsl]
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where c1_0.name=? and p1_0.price<=? and exists(select 1 from product_tags t1_0 join tags t1_1 on t1_1.id=t1_0.tag_id where t1_1.name in (?,?) and p1_0.id=t1_0.product_id) and p1_0.stock>? order by p1_0.price,p1_0.id offset ? rows fetch first ? rows only
binding parameter (1:VARCHAR) <- [Mice]
binding parameter (2:NUMERIC) <- [50]
binding parameter (3:VARCHAR) <- [wireless]
binding parameter (4:VARCHAR) <- [usb-c]
binding parameter (5:INTEGER) <- [0]
binding parameter (6:INTEGER) <- [0]
binding parameter (7:INTEGER) <- [3]
    content [12:MS-05 19.90, 8:MS-01 24.50]
    numberOfElements=2 totalElements=2 totalPages=1

The same where clause as the Criteria search, and the same two products. The category columns are missing from the select list because the entity graph sits on the Specification method, not on this one. Page 1 of maxPrice=50 sent select count(p1_0.id) from products p1_0 where p1_0.price<=?, exactly like the other two. containsIgnoreCase escapes on its own, with ! as the escape character:

Text

>>> findAll(ProductPredicates.matching(q=%)) [Querydsl]
select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 where lower(p1_0.name) like ? escape '!'
binding parameter (1:VARCHAR) <- [%!%%]
    [23:AC-06 45.50] (1 rows)

_ found nothing, and an empty BooleanBuilder gave a query without where and a total of 23.

Where Querydsl reads better: any() and JPAQueryFactory

The tag filter shows the difference. The Criteria version needs a subquery, a correlation, a join, a select and an exists, four lines after a first version with a plain join that got the counts wrong. Querydsl says it in one expression, product.tags.any().name.in(filter.tag()), and generated the same correlated exists. Nothing in that line can produce duplicate rows.

For queries that are not "entities matching a predicate", JPAQueryFactory writes JPQL through the Q-types. It needs an EntityManager, and one bean is enough:

src/main/java/com/example/demo/common/QuerydslConfig.java

@Configuration
class QuerydslConfig {
 
    @Bean
    JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }
}

The count per category, with the same predicate:

src/main/java/com/example/demo/product/ProductQueries.java

    public List<CategoryCount> countByCategory(ProductFilter filter) {
        return queryFactory
                .select(category.name, product.count())
                .from(product)
                .join(product.category, category)
                .where(ProductPredicates.matching(filter))
                .groupBy(category.name)
                .orderBy(category.name.asc())
                .fetch()
                .stream()
                .map(row -> new CategoryCount(row.get(category.name), row.get(product.count())))
                .toList();
    }

Text

>>> countByCategory(maxPrice=50, inStock=true) [Querydsl]
select c1_0.name,count(p1_0.id) from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where p1_0.price<=? and p1_0.stock>? group by c1_0.name order by c1_0.name
binding parameter (1:NUMERIC) <- [50]
binding parameter (2:INTEGER) <- [0]
    [CategoryCount[category=Accessories, products=5], CategoryCount[category=Keyboards, products=1], CategoryCount[category=Mice, products=4]]

Same numbers as the Criteria version, in code that reads in the order SQL does: select, from, join, where, group by, order by. The tuple's values are read by the expression that selected them, row.get(category.name), rather than by position.

@QuerydslPredicate binds every property by default

Spring Data's web support can build the predicate from the request itself:

Java

    @GetMapping("/qdsl-binding")
    public List<String> qdslBinding(@QuerydslPredicate(root = Product.class) Predicate predicate) {
        List<String> skus = new ArrayList<>();
        repository.findAll(predicate).forEach(p -> skus.add(p.getSku()));
        return skus;
    }

With no configuration, every property path of Product became a filter with equality:

Request	Predicate	Result
`?sku=MS-02`	`product.sku = MS-02`	`["MS-02"]`
`?category.name=Mice&stock=0`	`product.category.name = Mice && product.stock = 0`	`["MS-03"]`
`?name=mouse`	`product.name = mouse`	`[]`
`?tags.name=rgb`	`any(product.tags).name = rgb`	four products
`?id=1&id=2`	`product.id in [1, 2]`	`["KB-01","KB-02"]`

That is a public query language over the whole entity, including paths nobody meant to expose. Implementing QuerydslBinderCustomizer<QProduct> on the repository restricts it:

Java

    @Override
    default void customize(QuerydslBindings bindings, QProduct product) {
        bindings.including(product.name, product.category.name, product.price);
        bindings.excludeUnlistedProperties(true);
        bindings.bind(product.name).first(StringExpression::containsIgnoreCase);
    }

After that, ?name=mouse became containsIc(product.name,mouse) and found seven products, and ?sku=MS-02 was ignored: the predicate was an empty BooleanBuilder and the response listed all 23 products. Ignored is not rejected, which the section on empty filters comes back to. This article's endpoint keeps the explicit ProductFilter record, where the parameters are the ones the code names.

jOOQ: when the query is the point

jOOQ is not a JPA tool. It generates Java classes from the database schema, one per table with a typed field per column, and builds SQL with them; results come back as records or DTOs, never as managed entities, so there is no persistence context, no lazy loading and no dirty checking. Spring Boot manages jOOQ 3.21.7 and auto-configures a DSLContext on the application's DataSource with spring-boot-starter-jooq. The official Gradle plugin generated the classes from the running PostgreSQL:

build.gradle

plugins {
    // ...
    id 'org.jooq.jooq-codegen-gradle' version '3.21.7'
}
 
dependencies {
    // ...
    implementation 'org.springframework.boot:spring-boot-starter-jooq'
    jooqCodegen 'org.postgresql:postgresql'
}
 
jooq { 
    configuration { 
        jdbc { 
            driver = 'org.postgresql.Driver'
            url = 'jdbc:postgresql://localhost:5508/demo'
            user = 'demo'
            password = 'demo'
        } 
        generator { 
            database { 
                inputSchema = 'public'
                excludes = 'flyway_schema_history'
            } 
            target { 
                packageName = 'com.example.demo.jooq'
            } 
        } 
    } 
} 
 
sourceSets.main.java.srcDir(tasks.named('jooqCodegen'))

The last line matters: without it, ./gradlew jooqCodegen wrote Products, Categories, Tags and ProductTags to build/generated-sources/jooq, and compileJava still failed with error: package com.example.demo.jooq does not exist. With it, jooqCodegen ran before every compileJava, including builds where nothing had changed, so every build needs the database to be reachable. The same search, with DSL.noCondition() as the empty starting point:

src/main/java/com/example/demo/product/ProductJooqSearch.java

    public List<ProductResponse> search(ProductFilter filter, int page, int size) {
        return dsl.select(PRODUCTS.ID, PRODUCTS.NAME, PRODUCTS.SKU, PRODUCTS.PRICE, PRODUCTS.STOCK, CATEGORIES.NAME)
                .from(PRODUCTS)
                .join(CATEGORIES).on(CATEGORIES.ID.eq(PRODUCTS.CATEGORY_ID))
                .where(conditions(filter))
                .orderBy(PRODUCTS.PRICE, PRODUCTS.ID)
                .limit(size)
                .offset(page * size)
                .fetch(r -> new ProductResponse(r.value1(), r.value2(), r.value3(), r.value4(), r.value5(), r.value6()));
    }
 
    private static Condition conditions(ProductFilter filter) {
        Condition where = noCondition();
        if (filter.q() != null) where = where.and(PRODUCTS.NAME.containsIgnoreCase(filter.q()));
        if (filter.category() != null) where = where.and(CATEGORIES.NAME.eq(filter.category()));
        if (filter.minPrice() != null) where = where.and(PRODUCTS.PRICE.ge(filter.minPrice()));
        if (filter.maxPrice() != null) where = where.and(PRODUCTS.PRICE.le(filter.maxPrice()));
        if (!filter.tag().isEmpty()) {
            where = where.and(exists(selectOne()
                    .from(PRODUCT_TAGS)
                    .join(TAGS).on(TAGS.ID.eq(PRODUCT_TAGS.TAG_ID))
                    .where(PRODUCT_TAGS.PRODUCT_ID.eq(PRODUCTS.ID))
                    .and(TAGS.NAME.in(filter.tag()))));
        }
        if (Boolean.TRUE.equals(filter.inStock())) where = where.and(PRODUCTS.STOCK.gt(0));
        return where;
    }

With logging.level.org.jooq.tools.LoggerListener=DEBUG, jOOQ logged the statement it sent:

Text

Executing query          : select "public"."products"."id", "public"."products"."name", "public"."products"."sku", "public"."products"."price", "public"."products"."stock", "public"."categories"."name" from "public"."products" join "public"."categories" on "public"."categories"."id" = "public"."products"."category_id" where ("public"."categories"."name" = ? and "public"."products"."price" <= ? and exists (select 1 as "one" from "public"."product_tags" join "public"."tags" on "public"."tags"."id" = "public"."product_tags"."tag_id" where ("public"."product_tags"."product_id" = "public"."products"."id" and "public"."tags"."name" in (?, ?))) and "public"."products"."stock" > ?) order by "public"."products"."price", "public"."products"."id" offset ? rows fetch next ? rows only
Version                  : Database version is supported by dialect SQLDialect.POSTGRES: 18.6 (Debian 18.6-1.pgdg13+2)

It returned MS-05 and MS-01, like the other three. The SQL is the Java code, clause for clause, and the select list holds only the six columns the response needs. containsIgnoreCase("100%") escaped the input inside the SQL itself, replace(replace(replace(lower(?), '!', '!!'), '%', '!%'), '_', '!_') with escape '!', and found the desk lamp. The first query also printed jOOQ's ASCII-art logo and a "tip of the day" to the log.

jOOQ is the better fit when the SQL is the point: reports, window functions, common table expressions, vendor features, queries across tables no entity maps, or a service with no JPA at all. For a search that returns entities an application then modifies, the JPA tools above keep one model. Using both in one application is possible, since they share the DataSource, at the cost of two ways to describe the same tables.

Validation, sort whitelisting and empty filters

Dynamic queries move decisions from compile time to the request, so the request has to be checked.

A 400 ProblemDetail for minPrice greater than maxPrice

The constraints on ProductFilter run because of @Valid, and a failure arrives as MethodArgumentNotValidException. The advice extends ResponseEntityExceptionHandler and puts the field errors into the ProblemDetail:

src/main/java/com/example/demo/common/GlobalExceptionHandler.java

@RestControllerAdvice
public class GlobalExceptionHandler extends ResponseEntityExceptionHandler {
 
    @Override
    protected ResponseEntity<Object> handleMethodArgumentNotValid(
            MethodArgumentNotValidException ex, HttpHeaders headers, HttpStatusCode status, WebRequest request) {
        Map<String, String> errors = new LinkedHashMap<>();
        for (ObjectError error : ex.getBindingResult().getAllErrors()) {
            String name = error instanceof FieldError field ? field.getField() : error.getObjectName();
            errors.put(name, error.getDefaultMessage());
        }
        ProblemDetail problem = ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, "Invalid search parameters");
        problem.setProperty("errors", errors);
        return handleExceptionInternal(ex, problem, headers, HttpStatus.BAD_REQUEST, request);
    }
}

Bash

curl -i 'http://localhost:8208/api/products?minPrice=100&maxPrice=50'

Text

HTTP/1.1 400
Content-Type: application/problem+json
 
{"detail":"Invalid search parameters","instance":"/api/products","status":400,"title":"Bad Request","errors":{"priceRangeValid":"minPrice must not be greater than maxPrice"}}

@AssertTrue on isPriceRangeValid() reported under the property name priceRangeValid. The other checks answered the same way: ?minPrice=-5 with a 101-character q listed both fields, "minPrice":"must be greater than or equal to 0" and "q":"size must be between 0 and 100", and ?maxPrice=abc gave a 400 whose maxPrice entry was the conversion message, "Failed to convert value of type 'java.lang.String' to required type 'java.math.BigDecimal'…". Without the check, minPrice=100&maxPrice=50 would be a valid query that always returns nothing, which looks like "no products" rather than "wrong request".

Whitelisting sort properties

Basics 29 mapped an unknown sort property to a 400 and noted that anything else that resolves passes, collections included. With a search that avoids joins on purpose that matters: sorting the tag search by tags.name put left join product_tags t1_0 on p1_0.id=t1_0.product_id left join tags t1_1 on t1_1.id=t1_0.tag_id back into the row query, the kind of join that multiplied rows in the previous section. An explicit list fixes what can be sorted, and the metamodel's string constants keep the names tied to the entity:

src/main/java/com/example/demo/product/ProductSort.java

final class ProductSort {
 
    private static final Set<String> SORTABLE = Set.of(Product_.ID, Product_.NAME, Product_.PRICE, Product_.STOCK);
 
    private ProductSort() {
    }
 
    static Pageable checked(Pageable pageable) {
        for (Sort.Order order : pageable.getSort()) {
            if (!SORTABLE.contains(order.getProperty())) {
                throw new ResponseStatusException(HttpStatus.BAD_REQUEST,
                        "Cannot sort by '" + order.getProperty() + "'; allowed: id, name, price, stock");
            }
        }
        Sort sort = pageable.getSort().getOrderFor(Product_.ID) == null
                ? pageable.getSort().and(Sort.by(Product_.ID))
                : pageable.getSort();
        return PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), sort);
    }
}

src/main/java/com/example/demo/product/ProductController.java

        return PageResponse.from(service.search(filter, pageable)); 
        return PageResponse.from(service.search(filter, ProductSort.checked(pageable)));

It also appends id as the last sort key, the tie-breaker Basics 29 recommended. ?sort=category.name and ?sort=tags.name both answered:

Text

HTTP/1.1 400
Content-Type: application/problem+json
 
{"detail":"Cannot sort by 'category.name'; allowed: id, name, price, stock","instance":"/api/products","status":400,"title":"Bad Request"}

ResponseStatusException is an ErrorResponseException, so the advice's base class turned it into the ProblemDetail without a handler of its own. And the three-filter request from the section on composing specifications, over HTTP:

Bash

curl 'http://localhost:8208/api/products?category=Mice&maxPrice=50&inStock=true&sort=price,asc&size=3'

Text

{"content":[{"id":12,"name":"Travel mouse","sku":"MS-05","price":19.90,"stock":22,"category":"Mice"},{"id":13,"name":"Silent mouse","sku":"MS-06","price":22.00,"stock":40,"category":"Mice"},{"id":8,"name":"Wireless mouse","sku":"MS-01","price":24.50,"stock":3,"category":"Mice"}],"page":0,"size":3,"totalElements":4,"totalPages":2,"hasNext":true}

The SQL it logged ended in order by p1_0.price,p1_0.id. Every value in it travelled as a bound parameter; none of the four approaches in this article put a filter value into the SQL text, which is the property Basics 27 showed string concatenation losing.

Empty filters: everything on purpose, or by accident

An empty filter returning the whole catalogue, paged, is this endpoint's design: GET /api/products?size=2 answered "totalElements":23, and so did ?category=&q=%20&size=2, because the compact constructor turned both blank values into null. The same answer also came from ?categroy=Mice&size=2, a misspelled parameter: Spring MVC binds the parameters it knows and ignores the rest, so the typo removed the filter instead of failing. @QuerydslPredicate with excludeUnlistedProperties did the same with ?sku=. For a search page that is usually acceptable. Where "no filter" must never mean "everything", for an export, a bulk update or a delete driven by the same filters, reject it explicitly: check that at least one field of the record is set, or refuse unknown parameter names against the record's component names. The same applies to the Spring Data 4 write methods: an update whose condition was PredicateSpecification.unrestricted() sent update products p1_0 set stock=? and changed all 23 rows, in a transaction this run rolled back.

Query by Example: where it fits

Query by Example builds the condition from a probe entity: JpaRepository inherits findAll(Example) from QueryByExampleExecutor, and every non-null property of the probe becomes an equality or, with an ExampleMatcher, a string match. The mice whose name contains "mouse":

Java

Product probe = new Product("mouse", null, null, 0, new Category("Mice"));
ExampleMatcher contains = ExampleMatcher.matching()
        .withStringMatcher(ExampleMatcher.StringMatcher.CONTAINING)
        .withIgnoreCase();
repository.findAll(Example.of(probe, contains));

Text

select p1_0.id,p1_0.category_id,p1_0.name,p1_0.price,p1_0.sku,p1_0.stock from products p1_0 join categories c1_0 on c1_0.id=p1_0.category_id where lower(c1_0.name) like ? escape '\' and lower(p1_0.name) like ? escape '\' and p1_0.stock=?
binding parameter (1:VARCHAR) <- [%mice%]
binding parameter (2:VARCHAR) <- [%mouse%]
binding parameter (3:INTEGER) <- [0]
    [10:MS-03 39.00] (1 rows)

One product instead of six: stock is a primitive int, never null, so its 0 became p1_0.stock=?, and only the out-of-stock mouse matched. withIgnorePaths("stock") removed it and returned all six. The matcher also applied CONTAINING to the nested category name, and escaped 100% to %100\%%. Query by Example fits a form whose fields map one to one onto entity properties with equality or string matching. ExampleMatcher has no range matchers, so minPrice and maxPrice cannot be expressed. A tag in the probe's tags set was ignored: the query had no where and returned all 23 products. And the properties combine all with and, or with ExampleMatcher.matchingAny() all with or, which turned the probe above into where lower(c1_0.name) like ? escape '\' or lower(p1_0.name) like ? escape '\'; there is no mixing the two.

Choosing between Specifications, Criteria, Querydsl and jOOQ

The same category and tag filter written as a derived query, @Query with null checks, a Specification, the Criteria API, Querydsl and jOOQ, each with the SQL it generated and when its mistakes surface

Approach	Type safety	Composability	SQL control	Setup cost	When to use
Derived query	Property names checked at startup	None: one method per combination	Generated, fixed	None	One to three filters that are always present
`@Query` with `(:x is null or …)`	JPQL checked at startup; a null `String` failed on PostgreSQL at run time without a cast	None: all conditions always sent	Every condition and join in every statement	None	Two or three optional filters on a small table, knowingly
Specification	Strings fail at the first call; the metamodel moves that to compile time	`allOf`, `and`, `or`, `not`; reusable in Criteria queries	Conditions only; the select, count and paging are Spring Data's	`JpaSpecificationExecutor`; the metamodel processor for type safety	The default for search endpoints that return entities
Criteria API fragment	Metamodel attributes, checked at compile time	Reuses Specifications	Full: tuples, `group by`, subqueries, two queries of your own	A fragment interface and class	Aggregates, facets, anything a Specification cannot shape
Querydsl 5.1.0	Q-classes, checked at compile time	`BooleanBuilder`, predicates as values	Full JPQL through `JPAQueryFactory`	The `jakarta` classifier, a processor, a bean	Heavy dynamic querying where readability matters, such as `any()` for collections
jOOQ 3.21.7	Generated from the schema, checked at compile time	`Condition` values from `noCondition()`	Full SQL, vendor features, DTOs directly	Code generation against the database on every build	SQL-first reads, reports, services without JPA

For this endpoint the Specifications won: the search returns entities, the composition was a dozen lines, the metamodel made the attribute names compile-time checked, and the one query they could not express, the count per category, reused them from a Criteria fragment. Querydsl produced the same SQL with less ceremony for the collection filter, and it worked on Hibernate 7.4.5 and Spring Data JPA 4.1.1 without a single error; the cost is a dependency whose com.querydsl line has not had a release since 5.1.0.

FAQ

How do I build a dynamic query with optional parameters in Spring Data JPA?

Extend JpaSpecificationExecutor<Product>, write one small Specification per filter, add only the ones whose parameter is present to a list, and pass Specification.allOf(list) with a Pageable to findAll. An empty list gives a query without a where. Avoid a single @Query with (:x is null or …): on PostgreSQL a null String parameter failed with "function lower(bytea) does not exist", and the SQL always carries every condition.

Is Specification.where deprecated in Spring Data JPA?

It was in spring-data-jpa 3.5.0, marked @Deprecated(since = "3.5.0", forRemoval = true), and it accepted null. In 4.1.1 it is not deprecated, but it rejects null with IllegalArgumentException: Specification must not be null, and so do and, or and allOf. Use Specification.unrestricted() or allOf over a list for "no condition".

Why does my Specification return duplicate results or a wrong total?

A join to a collection returns one row per matching element. In this run, a product with two matching tags made the count 11 for 10 products, and Hibernate's in-memory de-duplication shrank a page of six to five, which made Spring Data report a single page. query.distinct(true) fixes the rows and the count but breaks order by on columns outside the select list on PostgreSQL; an exists subquery with sub.correlate(root) fixes all three.

Why does the count query fail when my Specification uses fetch?

Spring Data applies the same specification to the count query, which selects count(...), and Hibernate refuses a fetch join whose owner is not selected: "Query specified join fetching, but the owner of the fetched association was not present in the select list". Keep fetching out of specifications; redeclare findAll(Specification, Pageable) in the repository with @EntityGraph, or fetch in a Criteria query of your own.

Does Querydsl work with Spring Boot 4 and Hibernate 7?

Querydsl 5.1.0 did, on Spring Boot 4.1.1, Spring Data JPA 4.1.1 and Hibernate ORM 7.4.5: Q-class generation, QuerydslPredicateExecutor with paging, JPAQueryFactory tuple queries, any() as an exists subquery and @QuerydslPredicate all ran without an error. Use the jakarta classifier on both querydsl-jpa and querydsl-apt, and keep jakarta.persistence-api on the annotation processor path.

Should I use Specifications or Querydsl?

Specifications need nothing beyond Spring Data JPA, and with the Hibernate metamodel they are type-safe too. Querydsl reads better for complex conditions and collection filters, and JPAQueryFactory covers the queries a Specification cannot shape, at the cost of an extra dependency and a second code generator. For a handful of filters on one entity, Specifications are enough.

Conclusion

A search with optional filters needs a query built per request. Derived queries cannot do it without one method per combination, and the (:x is null or …) @Query does it badly: on PostgreSQL 18.6 a null String parameter failed with function lower(bytea) does not exist, the SQL carried every condition and the join for every request, and its generic plan read 200,403 buffers where the SQL built from the present filters read 379. Specifications build exactly that SQL. In Spring Data JPA 4.1.1 they compose with allOf, and, or and not, reject null where 3.5 accepted it, and gained PredicateSpecification, UpdateSpecification and DeleteSpecification, whose update committed without a surrounding transaction. Two traps sit in them: a join to a collection inflated the count to 11, and in-memory de-duplication made a page of six report itself as the only page, which distinct fixed partly and an exists subquery fully; and a fetch inside a specification broke the count query.

Under Specifications is the Criteria API: the hibernate-processor metamodel turned a misspelled attribute from a runtime PathElementException into a compile error, and a repository fragment reused the same Specification for a paged search and for a count per category grouped in SQL. Querydsl 5.1.0 worked on Hibernate 7.4.5 with the jakarta classifier, wrote the tag filter in one line, and bound every entity property through @QuerydslPredicate until restricted. jOOQ 3.21.7 generated its classes from the database and wrote SQL clause for clause. Around all of them, the request needs rules: a 400 ProblemDetail for minPrice above maxPrice, a sort whitelist, and a decision about what an empty filter means.

The next article is about caching: the Spring Cache abstraction, Caffeine as a local cache, Redis as a distributed one, and invalidation strategies.

[Advanced Spring Boot] Dynamic Queries with Spring Data JPA: Specifications, the Criteria API and Querydsl

The search endpoint and how the outputs were produced

Why derived queries and @Query break down

The (:x is null or …) trick and the SQL it produces

Does the null-check query plan badly on PostgreSQL?

Specifications with JpaSpecificationExecutor

One small Specification per filter

Composing specifications with allOf, and, or and not

What changed in the Spring Data JPA 4 Specification API

Escaping % and _ in a LIKE specification

Paging a specification: two SQL statements

Filtering on a to-many join: duplicates, distinct and exists

What a plain join does to a page

query.distinct(true) and the count query

An exists subquery instead of a join

The Criteria API behind Specifications

The JPA static metamodel with hibernate-processor

The same search in a custom repository fragment

A count per category that a Specification cannot return

Querydsl 5.1 with Spring Boot 4 and Hibernate 7

Setup: the jakarta classifier and the Q-classes

The search as a BooleanBuilder with QuerydslPredicateExecutor

Where Querydsl reads better: any() and JPAQueryFactory

@QuerydslPredicate binds every property by default

jOOQ: when the query is the point

Validation, sort whitelisting and empty filters

A 400 ProblemDetail for minPrice greater than maxPrice

Whitelisting sort properties

Empty filters: everything on purpose, or by accident

Query by Example: where it fits

Choosing between Specifications, Criteria, Querydsl and jOOQ

FAQ

How do I build a dynamic query with optional parameters in Spring Data JPA?

Is Specification.where deprecated in Spring Data JPA?

Why does my Specification return duplicate results or a wrong total?

Why does the count query fail when my Specification uses fetch?

Does Querydsl work with Spring Boot 4 and Hibernate 7?

Should I use Specifications or Querydsl?

Conclusion

Related Posts

[Advanced Spring Boot] NoSQL with Spring Data: MongoDB and Redis

[Advanced Spring Boot] Locking and Concurrency in Spring Boot: Optimistic @Version, Pessimistic Locks and Race Conditions

[Advanced Spring Boot] Caching in Spring Boot: the Cache Abstraction, Caffeine, Redis and Invalidation

[Advanced Spring Boot] Your Own Authorization Server: Spring Authorization Server and Keycloak