Spring Data JPA Custom Queries

When derived query methods aren’t enough, Spring Data JPA offers @Query for JPQL, native SQL, and Specifications for dynamic queries.

---

Derived Queries (Review)

public interface BookRepository extends JpaRepository<Book, Long> {
    List<Book> findByAuthor(String author);
    List<Book> findByTitleContainingIgnoreCase(String keyword);
    List<Book> findByPriceBetweenOrderByPriceAsc(BigDecimal min, BigDecimal max);
    Optional<Book> findByIsbn(String isbn);
    boolean existsByIsbn(String isbn);
}

---

@Query — JPQL

JPQL operates on entities, not tables:

public interface BookRepository extends JpaRepository<Book, Long> {

    @Query("SELECT b FROM Book b WHERE b.author = :author AND b.status = :status")
    List<Book> findByAuthorAndStatus(@Param("author") String author,
                                     @Param("status") BookStatus status);

    @Query("SELECT b FROM Book b WHERE LOWER(b.title) LIKE LOWER(CONCAT('%', :keyword, '%'))")
    Page<Book> searchByTitle(@Param("keyword") String keyword, Pageable pageable);

    @Query("SELECT b FROM Book b WHERE b.price > :minPrice ORDER BY b.price ASC")
    List<Book> findExpensiveBooks(@Param("minPrice") BigDecimal minPrice);

    @Query("SELECT COUNT(b) FROM Book b WHERE b.author = :author")
    long countByAuthor(@Param("author") String author);

    @Query("SELECT DISTINCT b.author FROM Book b ORDER BY b.author")
    List<String> findAllAuthors();
}

---

@Query — Native SQL

@Query(value = "SELECT * FROM books WHERE title ILIKE %:keyword% LIMIT :limit",
       nativeQuery = true)
List<Book> searchNative(@Param("keyword") String keyword, @Param("limit") int limit);

@Query(value = "SELECT author, COUNT(*) as book_count FROM books GROUP BY author ORDER BY book_count DESC",
       nativeQuery = true)
List<Object[]> getAuthorStats();

Use native SQL only when JPQL can’t express the query (DB-specific functions, complex aggregations).

---

@Modifying — Update and Delete Queries

@Modifying
@Transactional
@Query("UPDATE Book b SET b.status = :status WHERE b.publishedDate < :date")
int updateStatusForOldBooks(@Param("status") BookStatus status,
                            @Param("date") LocalDate date);

@Modifying
@Transactional
@Query("DELETE FROM Book b WHERE b.status = :status")
int deleteByStatus(@Param("status") BookStatus status);

@Modifying queries return int (rows affected). Always add @Transactional.

---

Projections — Partial Results

Interface Projection

public interface BookSummary {
    String getTitle();
    String getAuthor();
    BigDecimal getPrice();
}

public interface BookRepository extends JpaRepository<Book, Long> {
    List<BookSummary> findByAuthor(String author);  // returns only 3 fields
}

DTO Projection with @Query

public record BookAuthorStats(String author, long bookCount, BigDecimal avgPrice) {}

@Query("SELECT new com.bookstore.dto.BookAuthorStats(b.author, COUNT(b), AVG(b.price)) " +
       "FROM Book b GROUP BY b.author ORDER BY COUNT(b) DESC")
List<BookAuthorStats> getAuthorStatistics();

---

JPA Specifications — Dynamic Queries

For complex search/filter APIs:

public interface BookRepository extends JpaRepository<Book, Long>,
                                        JpaSpecificationExecutor<Book> {
}

public class BookSpecifications {

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

    public static Specification<Book> titleContains(String keyword) {
        return (root, query, cb) ->
            keyword == null ? null :
            cb.like(cb.lower(root.get("title")), "%" + keyword.toLowerCase() + "%");
    }

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

Usage:

@Service
public class BookService {

    public Page<BookResponse> search(String title, String author,
                                     BigDecimal minPrice, BigDecimal maxPrice,
                                     Pageable pageable) {
        Specification<Book> spec = Specification
            .where(BookSpecifications.titleContains(title))
            .and(BookSpecifications.hasAuthor(author))
            .and(BookSpecifications.priceBetween(minPrice, maxPrice));

        return bookRepository.findAll(spec, pageable).map(BookResponse::from);
    }
}

---

Key Takeaways

1. Derived queries for simple lookups, @Query for complex ones.
2. JPQL works on entities; native SQL works on tables.
3. @Modifying + @Transactional for UPDATE/DELETE queries.
4. Projections reduce data transfer — select only needed columns.
5. Specifications build dynamic queries — perfect for search/filter APIs.

---

What’s Next?

Next: JPA Relationships — @OneToMany, @ManyToOne, lazy loading, and the N+1 problem.