Queries: Derived, JPQL, Native and Projections

Spring Data derives queries from method names (findByStatusAndCreatedAtAfter). For anything non-trivial, use @Query with JPQL or native SQL. Return projections (interfaces or records) instead of full entities when you only need some columns, and use Pageable for paging. For dynamic filters, use Specifications or Querydsl.

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findTop10ByCustomerIdOrderByCreatedAtDesc(long customerId);

    @Query("""
           select new com.example.shop.orders.OrderSummary(o.id, o.status, o.totalCents, o.createdAt)
           from Order o
           where o.customer.id = :customerId and o.status in :statuses
           """)
    Page<OrderSummary> summaries(long customerId, Collection<OrderStatus> statuses, Pageable pageable);

    @Query(value = "select date_trunc('day', created_at) as day, sum(total_cents) as revenue " +
                   "from orders where created_at >= :from group by 1 order by 1", nativeQuery = true)
    List<DailyRevenue> dailyRevenue(Instant from);

    interface DailyRevenue { Instant getDay(); long getRevenue(); }
}

public record OrderSummary(long id, OrderStatus status, long totalCents, Instant createdAt) {}

Common pitfalls

• Very long derived method names — switch to @Query.
• Loading entities to display read-only lists (use projections).
• String-concatenated native queries (SQL injection).

Key points

• Derived queries for simple cases
• @Query for complex ones
• Projections for read models

Further reading: Spring Data JPA: Query methods