How do you write a Specification that filters on a joined/associated entity, and how do you avoid duplicate rows and N+1 problems?

root.join("assoc", JoinType.LEFT)
to-many join -> duplicates -> query.distinct(true)
guard on query.getResultType() (count vs fetch)
cb.exists(subquery) avoids duplicates
@EntityGraph for N+1, not the filter join

basics

~20 s

Inside toPredicate, call root.join("association") to reach the related entity, then build a predicate on it. A to-many join can duplicate parents, so add query.distinct(true). Use a fetch join or entity graph to avoid N+1 loading.

solid answer

~40 s

Within toPredicate you navigate associations with root.join("orders") (or root.get for embedded/to-one paths), then filter on the joined attribute, e.g. cb.equal(join.get("status"), SHIPPED). A join across a to-many association can return duplicate parent rows, so set query.distinct(true) — but only for the fetch query, since count queries in a Page must not be distinct-broken. To avoid the N+1 that a filtering join leaves behind, either use a JPA @EntityGraph on the repository method or, carefully, root.fetch(...) — though fetch joins with pagination trigger in-memory paging warnings. Use the static metamodel (Order_.status) for type safety. Distinguish inner vs left joins via JoinType: root.join("orders", JoinType.LEFT) keeps parents with no matching children.

code

java · 30 lines

java

public static Specification<User> hasShippedOrder() {
    return (root, query, cb) -> {
        // Only tweak the main (non-count) query
        if (Long.class != query.getResultType() && long.class != query.getResultType()) {
            query.distinct(true);
        }
        Join<User, Order> orders = root.join("orders", JoinType.INNER);
        return cb.equal(orders.get("status"), OrderStatus.SHIPPED);
    };
}

// Cleaner: EXISTS subquery, no duplicates, no distinct needed
public static Specification<User> hasShippedOrderExists() {
    return (root, query, cb) -> {
        Subquery<Long> sub = query.subquery(Long.class);
        Root<Order> o = sub.from(Order.class);
        sub.select(o.get("id"))
           .where(cb.equal(o.get("user"), root),
                  cb.equal(o.get("status"), OrderStatus.SHIPPED));
        return cb.exists(sub);
    };
}

// Repository method carrying an entity graph to avoid N+1 on the returned users
public interface UserRepository
        extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {
    @Override
    @EntityGraph(attributePaths = "orders")
    List<User> findAll(Specification<User> spec);
}

for a junior

Aware that root.join reaches related entities.

for a middle

Can write a join predicate and knows distinct fixes duplicates.

for a senior

Handles count-query guarding, chooses join vs EXISTS subquery, and addresses N+1 with entity graphs.

for a principal

Reasons about query plans (DISTINCT-join vs EXISTS), pagination-with-fetch pitfalls, and sets team conventions for join reuse and metamodel usage.

Navigating associations.

To-one / embedded: root.get("address").get("city") walks a path.
To-many or when you need join semantics: Join<User, Order> orders = root.join("orders"). Then cb.equal(orders.get("status"), Status.SHIPPED).
Join type: root.join("orders", JoinType.LEFT) for LEFT OUTER (keep parents with no children), default is INNER.

Duplicate-rows problem. An INNER/LEFT join to a to-many association yields one result row per matching child, so the same parent appears multiple times. Fixes:

query.distinct(true) — adds SELECT DISTINCT. This is the usual answer.
Caveat: for Page results, Spring issues a separate count query; making the main query distinct doesn't corrupt the count, but distinct + joins on large sets can be slow. Consider a subquery (cb.exists/correlated subquery) instead of a join when you only need existence, which avoids duplicates entirely:

(root, query, cb) -> {
    Subquery<Long> sub = query.subquery(Long.class);
    Root<Order> o = sub.from(Order.class);
    sub.select(o.get("id"))
       .where(cb.equal(o.get("user"), root),
              cb.equal(o.get("status"), Status.SHIPPED));
    return cb.exists(sub);
}

An EXISTS subquery gives no duplicates and often better plans than DISTINCT-over-join.

N+1 vs filter join. A join used for filtering does not necessarily load the association eagerly — accessing user.getOrders() later can still fire N+1 selects. Options:

@EntityGraph(attributePaths = "orders") on the repository method (cleanest, works with Specifications since Spring Data JPA supports @EntityGraph on JpaSpecificationExecutor methods).
root.fetch("orders", JoinType.LEFT) inside the spec — but fetch returns a Fetch, and you must cast/(Join<?, ?>) it to also filter on it; and combining fetch-join with Pageable makes Hibernate page in memory (HHH000104 warning) — avoid for paged queries.

Reusing a join. Calling root.join("orders") twice creates two joins. To reuse, inspect root.getJoins() or pass the join around; a common helper caches the join in a map keyed by attribute.

Type safety via metamodel. Add the Hibernate JPA metamodel generator; then use root.join(User_.orders) and join.get(Order_.status) — compile-time-checked, refactor-safe, vs stringly-typed "orders" that fails only at runtime.

Count-query pitfall with Pageable + joins. For findAll(spec, pageable) Spring builds a count query from the same predicate. If your spec calls query.distinct(true) or manipulates query (e.g. adds orderBy/select), that can leak into or break the count query. Guard by checking query.getResultType() — apply distinct/fetch only when it isn't the count query:

if (Long.class != query.getResultType() && long.class != query.getResultType()) {
    query.distinct(true);
    root.fetch("orders", JoinType.LEFT);
}

When to use joins vs subqueries. Join when you need to select/return child data or sort by it; EXISTS subquery when you only test presence of a matching child — cleaner, no distinct, no duplicate rows.

**Navigating associations.** - To-one / embedded: `root.get("address").get("city")` walks a path. - To-many or when you need join semantics: `Join<User, Order> orders = root.join("orders")`. Then `cb.equal(orders.get("status"), Status.SHIPPED)`. - Join type: `root.join("orders", JoinType.LEFT)` for LEFT OUTER (keep parents with no children), default is INNER. **Duplicate-rows problem.** An INNER/LEFT join to a to-many association yields one result row per matching child, so the same parent appears multiple times. Fixes: - `query.distinct(true)` — adds SELECT DISTINCT. This is the usual answer. - Caveat: for `Page` results, Spring issues a separate count query; making the main query distinct doesn't corrupt the count, but distinct + joins on large sets can be slow. Consider a subquery (`cb.exists`/correlated subquery) instead of a join when you only need existence, which avoids duplicates entirely: ``` (root, query, cb) -> { Subquery<Long> sub = query.subquery(Long.class); Root<Order> o = sub.from(Order.class); sub.select(o.get("id")) .where(cb.equal(o.get("user"), root), cb.equal(o.get("status"), Status.SHIPPED)); return cb.exists(sub); } ``` An EXISTS subquery gives no duplicates and often better plans than DISTINCT-over-join. **N+1 vs filter join.** A join used for *filtering* does not necessarily *load* the association eagerly — accessing `user.getOrders()` later can still fire N+1 selects. Options: - `@EntityGraph(attributePaths = "orders")` on the repository method (cleanest, works with Specifications since Spring Data JPA supports `@EntityGraph` on `JpaSpecificationExecutor` methods). - `root.fetch("orders", JoinType.LEFT)` inside the spec — but `fetch` returns a `Fetch`, and you must cast/`(Join<?, ?>)` it to also filter on it; and combining fetch-join with `Pageable` makes Hibernate page in memory (HHH000104 warning) — avoid for paged queries. **Reusing a join.** Calling `root.join("orders")` twice creates two joins. To reuse, inspect `root.getJoins()` or pass the join around; a common helper caches the join in a map keyed by attribute. **Type safety via metamodel.** Add the Hibernate JPA metamodel generator; then use `root.join(User_.orders)` and `join.get(Order_.status)` — compile-time-checked, refactor-safe, vs stringly-typed `"orders"` that fails only at runtime. **Count-query pitfall with Pageable + joins.** For `findAll(spec, pageable)` Spring builds a count query from the same predicate. If your spec calls `query.distinct(true)` or manipulates `query` (e.g. adds `orderBy`/`select`), that can leak into or break the count query. Guard by checking `query.getResultType()` — apply distinct/fetch only when it isn't the count query: ``` if (Long.class != query.getResultType() && long.class != query.getResultType()) { query.distinct(true); root.fetch("orders", JoinType.LEFT); } ``` **When to use joins vs subqueries.** Join when you need to select/return child data or sort by it; EXISTS subquery when you only test presence of a matching child — cleaner, no distinct, no duplicate rows.

Why prefer cb.exists with a subquery over a join for a to-many filter?
An EXISTS subquery tests presence of a matching child without multiplying parent rows, so you avoid duplicate results and the need for DISTINCT, and it often yields a better query plan than DISTINCT over a join.
Why is query.getResultType() checked before calling distinct/fetch?
findAll(spec, Pageable) runs a separate count query reusing the same specification. Applying distinct or fetch joins there can break or slow the count. Guarding on the result type (Long/long) applies those tweaks only to the real fetch query.

saying these in an interview costs you the question

Adding query.distinct(true) unconditionally, which can break/slow the auto-generated count query for Pageable.
Assuming a filtering join eagerly loads the association and prevents N+1 — it doesn't; you need @EntityGraph or a fetch join.
Calling root.join("orders") multiple times and creating duplicate joins.
Using fetch join together with Pageable and ignoring the in-memory pagination warning (HHH000104).

How do you write a Specification that filters on a joined/associated entity, and how do you avoid duplicate rows and N+1 problems?

answer

basics

solid answer

code

go deeper

context

How do you write a Specification that filters on a joined/associated entity, and how do you avoid duplicate rows and N+1 problems?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~2 min

▾common follow-ups2 questions

▾red flags4 flags

§ context

▾related questions5 questions

▾related topics3 topics

answer

go deeper

context