Spring Boot
Custom Queries
Custom queries in Spring Data JPA cover every scenario where derived query methods and simple @Query annotations are insufficient — dynamic multi-condition searches, Criteria API queries, Specifications, custom repository fragments, and direct EntityManager access. These patterns are the backbone of production search and reporting features.
Custom Repository Fragments
The cleanest way to add custom query logic to a Spring Data repository is the fragment pattern. Define an interface for the custom methods, implement it in a class whose name ends with Impl, and extend both from the main repository. Spring Data merges them automatically — callers see one interface with all methods.
Java
// ── Step 1: Fragment interface — declare the custom methods: ──────────
public interface UserRepositoryCustom {
Page<User> searchUsers(UserSearchCriteria criteria, Pageable pageable);
List<UserActivitySummary> findActivitySummaries(LocalDateTime from,
LocalDateTime to);
int bulkDeactivate(List<Long> ids);
}
// ── Step 2: Fragment implementation — name must end with Impl: ────────
@RequiredArgsConstructor
public class UserRepositoryCustomImpl implements UserRepositoryCustom {
private final EntityManager em;
@Override
@Transactional(readOnly = true)
public Page<User> searchUsers(UserSearchCriteria c, Pageable pageable) {
// Build the content query:
String jpql = buildSearchJpql(c, false);
TypedQuery<User> query = em.createQuery(jpql, User.class);
applyParameters(query, c);
query.setFirstResult((int) pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<User> content = query.getResultList();
// Build the count query:
String countJpql = buildSearchJpql(c, true);
TypedQuery<Long> countQuery = em.createQuery(countJpql, Long.class);
applyParameters(countQuery, c);
long total = countQuery.getSingleResult();
return new PageImpl<>(content, pageable, total);
}
private String buildSearchJpql(UserSearchCriteria c, boolean count) {
StringBuilder sb = new StringBuilder(
count ? "SELECT COUNT(u) FROM User u WHERE 1=1"
: "SELECT u FROM User u WHERE 1=1");
if (c.getName() != null) sb.append(" AND LOWER(u.name) LIKE :name");
if (c.getEmail() != null) sb.append(" AND u.email = :email");
if (c.getRole() != null) sb.append(" AND u.role = :role");
if (c.getActive() != null) sb.append(" AND u.active = :active");
if (!count && pageable != null)
sb.append(" ORDER BY u.createdAt DESC");
return sb.toString();
}
private void applyParameters(Query query, UserSearchCriteria c) {
if (c.getName() != null)
query.setParameter("name", "%" + c.getName().toLowerCase() + "%");
if (c.getEmail() != null) query.setParameter("email", c.getEmail());
if (c.getRole() != null) query.setParameter("role", c.getRole());
if (c.getActive() != null) query.setParameter("active", c.getActive());
}
@Override
@Transactional(readOnly = true)
public List<UserActivitySummary> findActivitySummaries(
LocalDateTime from, LocalDateTime to) {
return em.createQuery("""
SELECT new com.example.dto.UserActivitySummary(
u.id, u.name, COUNT(a), MAX(a.createdAt))
FROM User u
LEFT JOIN u.activities a
WHERE a.createdAt BETWEEN :from AND :to
GROUP BY u.id, u.name
ORDER BY COUNT(a) DESC
""", UserActivitySummary.class)
.setParameter("from", from)
.setParameter("to", to)
.getResultList();
}
@Override
@Modifying
@Transactional
public int bulkDeactivate(List<Long> ids) {
return em.createQuery(
"UPDATE User u SET u.active = false WHERE u.id IN :ids")
.setParameter("ids", ids)
.executeUpdate();
}
}
// ── Step 3: Main repository extends both: ─────────────────────────────
@Repository
public interface UserRepository
extends JpaRepository<User, Long>, UserRepositoryCustom {
// Standard derived methods and @Query here:
Optional<User> findByEmail(String email);
boolean existsByEmail(String email);
@Query("SELECT u FROM User u JOIN FETCH u.roles WHERE u.id = :id")
Optional<User> findByIdWithRoles(@Param("id") Long id);
// Custom methods from the fragment are available here too —
// searchUsers(), findActivitySummaries(), bulkDeactivate()
}Criteria API
The JPA Criteria API builds queries programmatically using a type-safe builder. It is verbose compared to JPQL strings but catches field name errors at compile time and is the foundation of the Specifications pattern.
Java
@Repository
@RequiredArgsConstructor
public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {
private final EntityManager em;
@Override
public List<Product> findByCriteria(ProductFilter filter) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> root = query.from(Product.class);
// ── Build predicates: ──────────────────────────────────────────
List<Predicate> predicates = new ArrayList<>();
if (filter.getName() != null) {
predicates.add(cb.like(
cb.lower(root.get("name")),
"%" + filter.getName().toLowerCase() + "%"
));
}
if (filter.getMinPrice() != null) {
predicates.add(cb.greaterThanOrEqualTo(
root.get("price"), filter.getMinPrice()
));
}
if (filter.getMaxPrice() != null) {
predicates.add(cb.lessThanOrEqualTo(
root.get("price"), filter.getMaxPrice()
));
}
if (filter.getCategory() != null) {
predicates.add(cb.equal(
root.get("category"), filter.getCategory()
));
}
if (filter.getActive() != null) {
predicates.add(cb.equal(root.get("active"), filter.getActive()));
}
if (filter.getTags() != null && !filter.getTags().isEmpty()) {
Join<Product, Tag> tagJoin = root.join("tags", JoinType.INNER);
predicates.add(tagJoin.get("name").in(filter.getTags()));
query.distinct(true);
}
// ── Compose WHERE clause: ──────────────────────────────────────
query.where(cb.and(predicates.toArray(new Predicate[0])));
// ── ORDER BY: ─────────────────────────────────────────────────
if ("price".equals(filter.getSortBy())) {
query.orderBy(filter.isAscending()
? cb.asc(root.get("price"))
: cb.desc(root.get("price")));
} else {
query.orderBy(cb.asc(root.get("name")));
}
// ── Execute with pagination: ───────────────────────────────────
return em.createQuery(query)
.setFirstResult(filter.getOffset())
.setMaxResults(filter.getPageSize())
.getResultList();
}
@Override
public long countByCriteria(ProductFilter filter) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<Product> root = query.from(Product.class);
// Reuse the same predicate-building logic:
List<Predicate> predicates = buildPredicates(cb, root, filter);
query.select(cb.count(root)).where(predicates.toArray(new Predicate[0]));
return em.createQuery(query).getSingleResult();
}
}Specifications Pattern
Spring Data JPA's Specification interface wraps a single JPA Criteria predicate. Individual Specifications are composable with and(), or(), and not(). A repository that extends JpaSpecificationExecutor gains findAll(Specification, Pageable) and other Specification-aware methods.
Java
// ── Enable Specifications on the repository: ─────────────────────────
@Repository
public interface ProductRepository
extends JpaRepository<Product, Long>,
JpaSpecificationExecutor<Product> { }
// ── Define reusable Specification predicates: ─────────────────────────
public final class ProductSpecs {
// Private constructor — utility class:
private ProductSpecs() { }
public static Specification<Product> hasName(String name) {
return (root, query, cb) -> name == null ? null :
cb.like(cb.lower(root.get("name")),
"%" + name.toLowerCase() + "%");
}
public static Specification<Product> hasCategory(String category) {
return (root, query, cb) -> category == null ? null :
cb.equal(root.get("category"), category);
}
public static Specification<Product> isActive() {
return (root, query, cb) -> cb.isTrue(root.get("active"));
}
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.lessThanOrEqualTo(root.get("price"), max);
if (max == null) return cb.greaterThanOrEqualTo(root.get("price"), min);
return cb.between(root.get("price"), min, max);
};
}
public static Specification<Product> createdAfter(LocalDateTime date) {
return (root, query, cb) -> date == null ? null :
cb.greaterThan(root.get("createdAt"), date);
}
public static Specification<Product> hasTag(String tagName) {
return (root, query, cb) -> {
if (tagName == null) return null;
query.distinct(true);
Join<Product, Tag> tags = root.join("tags", JoinType.INNER);
return cb.equal(tags.get("name"), tagName);
};
}
}
// ── Service — compose Specifications at runtime: ──────────────────────
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class ProductService {
private final ProductRepository productRepository;
public Page<ProductResponse> search(ProductSearchRequest req,
Pageable pageable) {
Specification<Product> spec = Specification
.where(ProductSpecs.hasName(req.name()))
.and(ProductSpecs.hasCategory(req.category()))
.and(ProductSpecs.isActive())
.and(ProductSpecs.priceBetween(req.minPrice(), req.maxPrice()))
.and(ProductSpecs.createdAfter(req.createdAfter()))
.and(ProductSpecs.hasTag(req.tag()));
return productRepository.findAll(spec, pageable)
.map(ProductResponse::from);
}
// ── Combine with Or: ──────────────────────────────────────────────
public List<Product> findFeaturedOrNew() {
Specification<Product> spec =
ProductSpecs.hasTag("featured")
.or(ProductSpecs.createdAfter(
LocalDateTime.now().minusDays(7)));
return productRepository.findAll(spec);
}
// ── Negate: ───────────────────────────────────────────────────────
public List<Product> findNonActive() {
return productRepository.findAll(
Specification.not(ProductSpecs.isActive()));
}
}QueryDSL Integration
QueryDSL generates type-safe Q-classes from entity classes at build time. These Q-classes provide a fluent, compile-time-checked query DSL that is more concise than the Criteria API and less error-prone than JPQL strings.
XML
<!-- pom.xml — QueryDSL dependencies: -->
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<classifier>jakarta</classifier>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<classifier>jakarta</classifier>
<scope>provided</scope>
</dependency>
<!-- APT plugin to generate Q-classes: -->
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<executions>
<execution>
<goals><goal>process</goal></goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
// ── Repository — extend QuerydslPredicateExecutor: ────────────────────
@Repository
public interface ProductRepository
extends JpaRepository<Product, Long>,
QuerydslPredicateExecutor<Product> { }
// ── Service — use generated QProduct class: ───────────────────────────
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class ProductService {
private final ProductRepository productRepository;
public Page<ProductResponse> search(ProductSearchRequest req,
Pageable pageable) {
QProduct p = QProduct.product; // generated Q-class
BooleanBuilder predicate = new BooleanBuilder();
if (req.name() != null)
predicate.and(p.name.containsIgnoreCase(req.name()));
if (req.category() != null)
predicate.and(p.category.eq(req.category()));
if (req.minPrice() != null)
predicate.and(p.price.goe(req.minPrice()));
if (req.maxPrice() != null)
predicate.and(p.price.loe(req.maxPrice()));
if (req.active() != null)
predicate.and(p.active.eq(req.active()));
if (req.tag() != null)
predicate.and(p.tags.any().name.eq(req.tag()));
return productRepository.findAll(predicate, pageable)
.map(ProductResponse::from);
}
}Direct EntityManager Queries
For one-off queries, reporting, or situations that don't fit any repository pattern, inject the EntityManager directly into a service or component and execute JPQL or native SQL directly.
Java
@Service
@RequiredArgsConstructor
@Transactional(readOnly = true)
public class ReportingService {
private final EntityManager em;
// ── Typed JPQL query: ─────────────────────────────────────────────
public List<OrderSummary> getOrderSummary(LocalDateTime from,
LocalDateTime to) {
return em.createQuery("""
SELECT new com.example.dto.OrderSummary(
DATE(o.createdAt),
COUNT(o),
SUM(o.total),
AVG(o.total))
FROM Order o
WHERE o.createdAt BETWEEN :from AND :to
GROUP BY DATE(o.createdAt)
ORDER BY DATE(o.createdAt)
""", OrderSummary.class)
.setParameter("from", from)
.setParameter("to", to)
.getResultList();
}
// ── Native query with result mapping: ─────────────────────────────
public List<Object[]> getRevenueByCountry() {
return em.createNativeQuery("""
SELECT c.country,
SUM(o.total) AS revenue,
COUNT(DISTINCT o.id) AS order_count,
COUNT(DISTINCT c.id) AS customer_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'COMPLETED'
GROUP BY c.country
ORDER BY revenue DESC
""")
.getResultList();
}
// ── Streaming large result sets: ──────────────────────────────────
@Transactional(readOnly = true)
public void exportAllUsers(OutputStream out) {
em.createQuery("SELECT u FROM User u ORDER BY u.id", User.class)
.setHint(QueryHints.HINT_FETCH_SIZE, 100)
.getResultStream()
.forEach(user -> writeLine(out, user));
}
// ── Single scalar result: ─────────────────────────────────────────
public long getTotalRevenue() {
return em.createQuery(
"SELECT COALESCE(SUM(o.total), 0) FROM Order o " +
"WHERE o.status = :status", Long.class)
.setParameter("status", Order.Status.COMPLETED)
.getSingleResult();
}
// ── Hints for read-only optimisation: ─────────────────────────────
public List<User> findAllReadOnly() {
return em.createQuery("SELECT u FROM User u", User.class)
.setHint(QueryHints.HINT_READONLY, true)
.setHint(QueryHints.HINT_CACHEABLE, false)
.getResultList();
}
}Choosing the Right Approach
Each custom query mechanism has a clear niche. Mixing them in the same repository is normal — use the simplest tool that satisfies the requirement.
Shell
# ── Decision guide: ──────────────────────────────────────────────────
# Derived query method (findByEmailAndActiveTrue):
# Use when: 1-3 conditions, no JOIN FETCH needed, method name is readable
# Avoid when: method name exceeds ~60 characters
# @Query with JPQL:
# Use when: 2-5 conditions, JOIN FETCH needed, GROUP BY, subquery
# Avoid when: database-specific SQL features needed
# @Query with nativeQuery = true:
# Use when: window functions, CTEs, UPSERT, full-text search,
# database-specific operators, proven performance requirement
# Avoid when: database portability is required
# Specifications (JpaSpecificationExecutor):
# Use when: user-driven search with optional filters (search forms, APIs)
# all conditions optional, number of conditions varies at runtime
# Avoid when: conditions are always fixed — @Query is simpler
# Criteria API (direct):
# Use when: complex dynamic queries, reusable predicate library needed
# Specifications don't fit (e.g. subqueries, multiple roots)
# Avoid when: Specifications cover the need — less verbose
# QueryDSL:
# Use when: large codebase, many dynamic queries, team prefers type safety
# over annotation-based queries
# Avoid when: simple project — extra build step cost not justified
# Custom repository fragment + EntityManager:
# Use when: bulk operations, streaming, stored procedures,
# reporting queries that return DTOs, anything not fitting above
# Avoid when: a simpler mechanism covers the need
# ── All mechanisms coexist in one repository: ─────────────────────────
@Repository
public interface UserRepository
extends JpaRepository<User, Long>,
JpaSpecificationExecutor<User>,
UserRepositoryCustom { // fragment for complex queries
Optional<User> findByEmail(String email); // derived
boolean existsByEmail(String email); // derived
@Query("SELECT u FROM User u JOIN FETCH u.roles WHERE u.id = :id")
Optional<User> findByIdWithRoles(@Param("id") Long id); // @Query
// searchUsers(), bulkDeactivate() — from UserRepositoryCustom fragment
// findAll(Specification, Pageable) — from JpaSpecificationExecutor
}