By WebNest Studio Engineering Team
Spring Boot Tutorial
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
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);
}
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
// 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);
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
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);
}
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
// 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));
}
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.
Exercise
Using the lesson's JPQL, native and modifying queries example, create a small implementation or design sketch that demonstrates @Query supports portable JPQL and database-specific native SQL with named parameters.. Add a test case or scenario that would catch Concatenating request parameters into @Query strings, creating SQL/JPQL injection vulnerabilities., and describe the expected result.
Show hints
- Start with JPQL with @Query and compare your approach with JPQL, native and modifying queries. Identify the decision or behavior the lesson recommends.
- Walk through your scenario step by step. Confirm that it follows @Query supports portable JPQL and database-specific native SQL with named parameters. and that it would catch the failure described by Concatenating request parameters into @Query strings, creating SQL/JPQL injection vulnerabilities..
Quick quiz
1. Which choice is a mistake to avoid in Custom Queries, Projections and Specifications?
- A. @Query supports portable JPQL and database-specific native SQL with named parameters.
- B. Concatenating request parameters into @Query strings, creating SQL/JPQL injection vulnerabilities.
- C. Writing SQL table/column names in a JPQL query (JPQL uses entity and field names).
Show answer
B. Avoid this approach: Concatenating request parameters into @Query strings, creating SQL/JPQL injection vulnerabilities. The lesson recommends: @Query supports portable JPQL and database-specific native SQL with named parameters.
Interview questions
How would you apply the guidance in JPQL with @Query to a real task, and what failure would you guard against?
Show answer
@Query supports portable JPQL and database-specific native SQL with named parameters. Apply this in JPQL with @Query; the example JPQL, native and modifying queries shows one way to do it. Avoid Concatenating request parameters into @Query strings, creating SQL/JPQL injection vulnerabilities. and explain how your implementation or design prevents that failure.
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 in the playground
Write and run your own code for this topic, or start from the exercise or an example. Runs plain Java 17. Frameworks such as Spring, and databases, are not available here. Your code for each topic is kept in this browser.
Run the examples
Change an input, predict the result, then compare it with the output. Explain why the result changes.
These examples need a JDK project with their libraries on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.
Written by
WebNest Studio Engineering Team
Practical lessons from developers working with Java, Python, React, databases and production systems.
Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering