Advanced Spring Data JPA Queries: Projections, Specifications, Entity Graphs and Keyset Scrolling

Spring Data JPA’s derived query methods (findByLastName) cover simple cases. Real applications soon need more: queries across associations, projections that skip loading whole entities, database features such as window functions, filters that depend on user input, efficient pagination over large tables, and bulk updates. This guide shows a technique for each, and how to avoid the N+1 query problem.

What I tested: every query below lives in one repository in a project generated with Spring Initializr (Spring Boot 4.1.1, Spring Data JPA 4.1.1, Hibernate 7.4.5, Java 21), and seven @DataJpaTest tests against an H2 2.4 database pass. I measured the N+1 problem with Hibernate statistics: loading five employees and touching their departments took 3 SQL statements; with an entity graph it took 1.

The 2024 version of this post built dynamic queries on the original Querydsl library and suggested switching associations to FetchType.EAGER to fix lazy-loading errors. The original com.querydsl artifacts have not had a release since January 2024 (an OpenFeign-maintained fork continues the project), so this version uses Spring Data’s built-in Specifications instead. And EAGER fetching usually makes performance worse, not better; fetch associations per query instead, as shown below.

The Model

Two entities: departments and employees, with a lazy many-to-one association and indexes on the columns we filter by.

package com.example.jpaqueries;

import jakarta.persistence.*;

@Entity
public class Department {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String name;

    protected Department() {}
    public Department(String name) { this.name = name; }

    public Long getId() { return id; }
    public String getName() { return name; }
}

package com.example.jpaqueries;

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

@Entity
@Table(indexes = {
    @Index(name = "idx_employee_department", columnList = "department_id"),
    @Index(name = "idx_employee_salary", columnList = "salary")
})
public class Employee {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    @Column(nullable = false, precision = 12, scale = 2)
    private BigDecimal salary;

    private LocalDate hiredOn;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    private Department department;

    protected Employee() {}
    public Employee(String name, BigDecimal salary, LocalDate hiredOn, Department department) {
        this.name = name; this.salary = salary; this.hiredOn = hiredOn; this.department = department;
    }

    public Long getId() { return id; }
    public String getName() { return name; }
    public BigDecimal getSalary() { return salary; }
    public LocalDate getHiredOn() { return hiredOn; }
    public Department getDepartment() { return department; }
}

The repository extends JpaRepository for CRUD and JpaSpecificationExecutor for dynamic queries. All the queries below are methods on it.

1. JPQL With Named Parameters

JPQL queries entities and fields, not tables and columns, so they survive column renames and work across databases. Always bind values as parameters; never concatenate strings into queries.

    // 1. JPQL with named parameters: entity and field names, not tables and columns.
    @Query("""
        select e from Employee e
        where e.department.name = :department and e.salary > :minSalary
        order by e.salary desc""")
    List<Employee> findWellPaid(@Param("department") String department, @Param("minSalary") BigDecimal minSalary);

@Test
void jpqlWithNamedParameters() {
    assertThat(employees.findWellPaid("Engineering", new BigDecimal("100000")))
        .extracting(Employee::getName).containsExactly("Ada", "Grace");
}

2. DTO Projections: Select Only What You Need

Loading full entities when you only display three fields wastes memory and keeps objects in the persistence context. A constructor expression maps rows straight into a Java record:

package com.example.jpaqueries;

import java.math.BigDecimal;

// DTO projection: only these columns are selected, no entities are managed.
public record EmployeeSummary(String name, String department, BigDecimal salary) {}

    // 2. DTO projection: select only what the caller needs.
    @Query("""
        select new com.example.jpaqueries.EmployeeSummary(e.name, d.name, e.salary)
        from Employee e join e.department d
        order by d.name, e.salary desc""")
    List<EmployeeSummary> findSummaries();

3. Native SQL for Database Features

When you need something JPQL does not offer, such as window functions, use a native query. An interface projection maps the column aliases to getters:

package com.example.jpaqueries;

import java.math.BigDecimal;

// Interface projection for the native query; getters match the column aliases.
public interface SalaryRank {
    String getName();
    String getDepartment();
    BigDecimal getSalary();
    BigDecimal getDepartmentTotal();
    Integer getRankInDepartment();
}

    // 3. Native SQL for database features JPQL lacks, such as window functions.
    @Query(value = """
        select e.name as name, d.name as department, e.salary as salary,
               sum(e.salary) over (partition by d.id) as departmentTotal,
               rank() over (partition by d.id order by e.salary desc) as rankInDepartment
        from employee e join department d on d.id = e.department_id
        order by d.name, rankInDepartment""", nativeQuery = true)
    List<SalaryRank> findSalaryRanks();

@Test
void nativeWindowFunction() {
    List<SalaryRank> ranks = employees.findSalaryRanks();
    SalaryRank linus = ranks.stream().filter(r -> r.getName().equals("Linus")).findFirst().orElseThrow();
    assertThat(linus.getRankInDepartment()).isEqualTo(3);
    assertThat(linus.getDepartmentTotal()).isEqualByComparingTo("380000");
}

Native queries tie you to a database’s SQL dialect, so keep them few and test them against the same database you run in production. H2 supported these window functions; PostgreSQL and MySQL 8 do too.

4. Avoid N+1 Queries With Entity Graphs

With a lazy association, loading a list of employees and then reading each one’s department triggers an extra query per department: the N+1 problem. An @EntityGraph fetches the association in the same query with a join:

    // 4. Fetch the association in the same query to avoid N+1 selects.
    @EntityGraph(attributePaths = "department")
    @Query("select e from Employee e order by e.id")
    List<Employee> findAllWithDepartment();

@Test
void entityGraphAvoidsNPlusOne() {
    Statistics s = stats();
    employees.findAll(Sort.by("id")).forEach(e -> e.getDepartment().getName()); // lazy loads per department
    long naive = s.getPrepareStatementCount();
    em.clear();

    s = stats();
    employees.findAllWithDepartment().forEach(e -> e.getDepartment().getName());
    long withGraph = s.getPrepareStatementCount();

    System.out.printf("Statements: findAll + lazy access = %d, entity graph = %d%n", naive, withGraph);
    assertThat(naive).isEqualTo(3);       // 1 for employees + 1 per distinct department
    assertThat(withGraph).isEqualTo(1);   // one query with a join
}

Statements: findAll + lazy access = 3, entity graph = 1

With five employees in two departments the difference is 3 versus 1; with 500 employees across 50 departments it would be 51 versus 1. Keep associations lazy by default and fetch what each query needs with an entity graph or join fetch. Turning on spring.jpa.properties.hibernate.generate_statistics=true in tests, as I did here, makes these problems visible.

5. Dynamic Filters With Specifications

Search screens combine optional filters. Specifications are small reusable conditions; returning null means “no condition”, so missing filters simply drop out:

package com.example.jpaqueries;

import java.math.BigDecimal;
import org.springframework.data.jpa.domain.Specification;

// Reusable query conditions, combined at runtime from whatever filters the user sent.
public final class EmployeeSpecs {
    private EmployeeSpecs() {}

    public static Specification<Employee> inDepartment(String department) {
        return (root, query, cb) -> department == null ? null
            : cb.equal(root.get("department").get("name"), department);
    }

    public static Specification<Employee> salaryAtLeast(BigDecimal min) {
        return (root, query, cb) -> min == null ? null : cb.ge(root.get("salary"), min);
    }

    public static Specification<Employee> nameContains(String text) {
        return (root, query, cb) -> text == null || text.isBlank() ? null
            : cb.like(cb.lower(root.get("name")), "%" + text.toLowerCase() + "%");
    }

    // A null condition is ignored, so missing filters simply drop out.
    public static Specification<Employee> search(String department, BigDecimal minSalary, String name) {
        return Specification.allOf(inDepartment(department), salaryAtLeast(minSalary), nameContains(name));
    }
}

@Test
void specificationsCombineOptionalFilters() {
    assertThat(employees.findAll(EmployeeSpecs.search("Engineering", new BigDecimal("100000"), null)))
        .extracting(Employee::getName).containsExactlyInAnyOrder("Ada", "Grace");
    assertThat(employees.findAll(EmployeeSpecs.search(null, null, "GRA")))
        .extracting(Employee::getName).containsExactly("Grace");
    assertThat(employees.findAll(EmployeeSpecs.search(null, null, null))).hasSize(5);
}

If you prefer a fluent, type-safe query DSL with generated query classes, the OpenFeign fork of Querydsl (io.github.openfeign.querydsl) is actively released; Spring Data’s QuerydslPredicateExecutor works with it.

6. Keyset Scrolling for Large Tables

Offset pagination (LIMIT 20 OFFSET 100000) gets slower as the offset grows and can skip or repeat rows when data changes. Spring Data’s Window and ScrollPosition support keyset scrolling, which continues from the last row seen:

    // 5. Keyset (cursor) scrolling: stable and index-friendly for large tables.
    Window<Employee> findTop3ByOrderByIdAsc(ScrollPosition position);

@Test
void keysetScrolling() {
    Window<Employee> first = employees.findTop3ByOrderByIdAsc(ScrollPosition.keyset());
    assertThat(first).extracting(Employee::getName).containsExactly("Ada", "Grace", "Linus");
    assertThat(first.hasNext()).isTrue();
    Window<Employee> second = employees.findTop3ByOrderByIdAsc(first.positionAt(first.size() - 1));
    assertThat(second).extracting(Employee::getName).containsExactly("Mary", "Tom");
    assertThat(second.hasNext()).isFalse();
}

7. Paging With a Cheap Count Query, and Bulk Updates

When you need page numbers, give complex queries a separate, simpler countQuery. For changes to many rows, a single @Modifying update avoids loading every entity:

    // 6. Paging with a separate, cheaper count query.
    @Query(value = "select e from Employee e where e.department.name = :department",
           countQuery = "select count(e) from Employee e where e.department.name = :department")
    List<Employee> findByDepartmentPaged(@Param("department") String department, Pageable pageable);

    // 7. Bulk update in one statement instead of loading and saving each entity.
    @Transactional
    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query("update Employee e set e.salary = e.salary * :factor where e.department.name = :department")
    int raiseSalaries(@Param("department") String department, @Param("factor") BigDecimal factor);

@Test
void pagingAndBulkUpdate() {
    assertThat(employees.findByDepartmentPaged("Engineering", PageRequest.of(0, 2, Sort.by("salary").descending())))
        .extracting(Employee::getName).containsExactly("Ada", "Grace");
    assertThat(employees.raiseSalaries("Sales", new BigDecimal("1.10"))).isEqualTo(2);
    assertThat(employees.findWellPaid("Sales", new BigDecimal("85000")))
        .extracting(Employee::getName).containsExactly("Mary");   // 80,000 * 1.10 = 88,000
}

Bulk updates bypass the persistence context, so clearAutomatically makes sure later reads do not return stale entities. They also skip entity lifecycle callbacks and optimistic locking, so use them for data-wide changes, not per-entity business logic.

Testing Queries

@DataJpaTest starts only the JPA layer with an embedded database and rolls back each test. The test class seeds data with the EntityManager and clears it, so queries hit the database rather than returning cached entities:

@DataJpaTest
class EmployeeQueriesTest {

    @Autowired EmployeeRepository employees;
    @Autowired EntityManager em;
    @Autowired EntityManagerFactory emf;

    @BeforeEach
    void seed() {
        var eng = new Department("Engineering");
        var sales = new Department("Sales");
        em.persist(eng);
        em.persist(sales);
        em.persist(new Employee("Ada", new BigDecimal("150000.00"), LocalDate.of(2020, 1, 6), eng));
        em.persist(new Employee("Grace", new BigDecimal("140000.00"), LocalDate.of(2021, 3, 1), eng));
        em.persist(new Employee("Linus", new BigDecimal("90000.00"), LocalDate.of(2023, 7, 3), eng));
        em.persist(new Employee("Mary", new BigDecimal("80000.00"), LocalDate.of(2022, 5, 9), sales));
        em.persist(new Employee("Tom", new BigDecimal("70000.00"), LocalDate.of(2024, 2, 12), sales));
        em.flush();
        em.clear();
    }
    // ... tests shown above
}

$ ./mvnw test
Statements: findAll + lazy access = 3, entity graph = 1
Tests run: 7, Failures: 0, Errors: 0, Skipped: 0

Note the Spring Boot 4 package: @DataJpaTest now lives in org.springframework.boot.data.jpa.test.autoconfigure, from the spring-boot-starter-data-jpa-test starter that Spring Initializr adds. H2 is convenient but not identical to production databases; for native queries, run the same tests against a real PostgreSQL or MySQL instance, for example with Testcontainers.

Troubleshooting

Spring Boot enables spring.jpa.open-in-view by default (the default is still true in Boot 4.1); I set it to false so lazy-loading mistakes show up in tests instead of silently running extra queries during view rendering.

Conclusion

Use JPQL with parameters for most custom queries, DTO projections for read-only views, native SQL when you need database-specific features, entity graphs to stop N+1 queries, Specifications for optional filters, keyset scrolling for large lists, and bulk updates for data-wide changes. Measure statement counts in tests so performance problems are caught before production. To expose these queries over HTTP, see building a RESTful API with Spring Boot.

FAQs