Custom Queries, Projections and Specifications

Derived query methods like findByEmail cover the simple cases, but real screens need more: reports with aggregates, searches with optional filters, bulk updates, database-specific SQL, and responses that contain only a few columns instead of whole entities.

This lesson goes beyond the basics of Spring Data JPA: JPQL and native queries with @Query, modifying queries, interface and DTO (record) projections, dynamic filtering with Specifications, Query by Example, and scrolling through large result sets.

JPQL with @Query

JPQL looks like SQL but queries entities and their fields, not tables and columns. It is portable across databases and checked by Hibernate at startup — a typo in a JPQL query fails fast when the application starts. Use named parameters (:status) with @Param or rely on compiled parameter names. Never concatenate values into queries.

Native Queries

nativeQuery = true sends raw SQL to the database. Use it for database-specific features — window functions, PostgreSQL JSON operators, full-text search, CTEs — that JPQL does not support. The trade-off is portability and weaker startup validation.

Modifying Queries

Bulk UPDATE and DELETE queries need @Modifying and must run inside a transaction. They bypass the persistence context, so entities already loaded in memory become stale; set clearAutomatically = true to clear the context after the update.

Projections

Loading full entities for a list screen wastes memory and bandwidth. A projection returns only the fields you need. Interface projections declare getters matching entity properties; Spring generates a proxy. DTO projections use a Java record whose constructor parameters match the selected values — the cleanest option for API responses. Dynamic projections let the caller pass the desired type as a Class<T> parameter.

Specifications for Dynamic Filters

Search screens with many optional filters lead to an explosion of repository methods. JpaSpecificationExecutor lets you build a query from small reusable Specification objects combined with and()/or(), adding only the conditions the user actually supplied.

Query by Example and Scrolling

Query by Example builds a query from a probe object: non-null fields become conditions. It suits simple admin searches. For iterating over millions of rows, Window results with ScrollPosition (keyset scrolling) are far more efficient than deep OFFSET pagination.

Examples

JPQL, native and modifying queries

Java

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("""
        select o from Order o
        where o.customer.email = :email and o.status in :statuses
        order by o.createdAt desc
        """)
    List<Order> findForCustomer(String email, Collection<OrderStatus> statuses);

    @Query("select coalesce(sum(i.quantity * i.unitPrice), 0) from OrderItem i where i.order.createdAt >= :since")
    BigDecimal revenueSince(Instant since);

    // PostgreSQL-specific: monthly revenue with date_trunc
    @Query(value = """
        select to_char(date_trunc('month', o.created_at), 'YYYY-MM') as month,
               sum(i.quantity * i.unit_price) as revenue
        from orders o join order_item i on i.order_id = o.id
        group by 1 order by 1
        """, nativeQuery = true)
    List<Object[]> monthlyRevenue();

    @Modifying(clearAutomatically = true)
    @Transactional
    @Query("update Order o set o.status = 'CANCELLED' where o.status = 'PENDING' and o.createdAt < :cutoff")
    int cancelStalePendingOrders(Instant cutoff);
}

Output

revenueSince(2026-09-01)          -> 184500.00
monthlyRevenue()                  -> [["2026-07", 120300.00], ["2026-08", 168900.00], ["2026-09", 184500.00]]
cancelStalePendingOrders(...)     -> 12  (rows updated)

Interface, record (DTO) and dynamic projections

Java

// Interface projection
public interface CourseTitleView {
    String getTitle();
    String getLevel();
}

// Record projection built with a JPQL constructor expression
public record CourseStats(String title, long students, double avgProgress) {}

public interface CourseRepository extends JpaRepository<Course, Long> {

    List<CourseTitleView> findByPublishedTrueOrderByTitle();

    @Query("""
        select new com.webnest.shop.course.CourseStats(c.title, count(e), coalesce(avg(e.progressPercent), 0))
        from Course c left join Enrollment e on e.course = c
        group by c.title order by count(e) desc
        """)
    List<CourseStats> courseStats();

    // Caller chooses the projection type
    <T> List<T> findByLevel(String level, Class<T> type);
}

// usage
courseRepository.findByPublishedTrueOrderByTitle()
    .forEach(c -> System.out.println(c.getTitle() + " - " + c.getLevel()));
courseRepository.courseStats().forEach(System.out::println);
List<CourseTitleView> beginners = courseRepository.findByLevel("BEGINNER", CourseTitleView.class);

Output

select c1_0.title, c1_0.level from course c1_0 where c1_0.published order by c1_0.title
Java Core - BEGINNER
Spring Boot - INTERMEDIATE

CourseStats[title=Spring Boot, students=1240, avgProgress=46.2]
CourseStats[title=Java Core, students=980, avgProgress=61.8]

Specifications for a search endpoint with optional filters

Java

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

public final class ProductSpecs {

    private ProductSpecs() {}

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

    public static Specification<Product> priceBetween(BigDecimal min, BigDecimal max) {
        return (root, query, cb) -> {
            if (min == null && max == null) return null;
            if (min == null) return cb.le(root.get("price"), max);
            if (max == null) return cb.ge(root.get("price"), min);
            return cb.between(root.get("price"), min, max);
        };
    }

    public static Specification<Product> inCategory(String category) {
        return (root, query, cb) -> category == null ? null : cb.equal(root.get("category"), category);
    }
}

@GetMapping("/api/products/search")
public Page<Product> search(@RequestParam(required = false) String q,
                            @RequestParam(required = false) BigDecimal minPrice,
                            @RequestParam(required = false) BigDecimal maxPrice,
                            @RequestParam(required = false) String category,
                            Pageable pageable) {
    Specification<Product> spec = Specification.allOf(
        ProductSpecs.nameContains(q),
        ProductSpecs.priceBetween(minPrice, maxPrice),
        ProductSpecs.inCategory(category));
    return productRepository.findAll(spec, pageable);
}

Output

GET /api/products/search?q=hoodie&maxPrice=1500
select ... from product p1_0 where lower(p1_0.name) like ? and p1_0.price<=? offset ? rows fetch first ? rows only

GET /api/products/search?category=books
select ... from product p1_0 where p1_0.category=? offset ? rows fetch first ? rows only

Query by Example and keyset scrolling

Java

// Query by Example: non-null fields of the probe become conditions
Customer probe = new Customer();
probe.setCity("Pune");
probe.setActive(true);
ExampleMatcher matcher = ExampleMatcher.matching().withIgnoreCase();
List<Customer> puneCustomers = customerRepository.findAll(Example.of(probe, matcher));

// Keyset scrolling through a large table without OFFSET
public interface OrderRepository extends JpaRepository<Order, Long> {
    Window<Order> findTop500ByStatusOrderByIdAsc(OrderStatus status, ScrollPosition position);
}

Window<Order> window = orderRepository.findTop500ByStatusOrderByIdAsc(
        OrderStatus.SHIPPED, ScrollPosition.keyset());
while (!window.isEmpty()) {
    window.forEach(invoiceService::archive);
    if (!window.hasNext()) break;
    window = orderRepository.findTop500ByStatusOrderByIdAsc(
        OrderStatus.SHIPPED, window.positionAt(window.size() - 1));
}

Output

select ... from orders where status=? order by id fetch first 500 rows only
select ... from orders where status=? and id>? order by id fetch first 500 rows only
... (each batch uses "id > last seen id" instead of an ever-growing OFFSET)

Common Mistakes

Concatenating request parameters into @Query strings, creating SQL/JPQL injection vulnerabilities.
Writing SQL table/column names in a JPQL query (JPQL uses entity and field names).
Forgetting @Modifying or @Transactional on bulk update queries, which throws at runtime.
Returning entities with lazy associations directly as JSON instead of projecting to DTOs.
Using OFFSET pagination to process millions of rows; later pages get slower and slower.

Key Points to Remember

@Query supports portable JPQL and database-specific native SQL with named parameters.
Bulk updates need @Modifying, a transaction, and usually clearAutomatically = true.
Projections (interface, record, dynamic) load only the columns a use case needs.
Specifications build dynamic WHERE clauses from optional filters cleanly.
Query by Example suits simple searches; keyset scrolling suits large batch processing.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Use your local JDK or project IDE for these examples. Codelab currently runs Python and HTML/CSS/JavaScript; framework examples may need project dependencies.