Query Methods & JPQL

Derived query methods#

Spring Data JPA can generate a query directly from a repository method's name — no query language needed at all for common cases:

public interface UserRepository extends JpaRepository<User, Long> {

    Optional<User> findByEmail(String email);

    List<User> findByEmailAndActiveTrue(String email);

    List<User> findByFullNameContainingIgnoreCase(String namePart);

    List<User> findByCreatedAtAfter(LocalDateTime date);

    long countByActiveTrue();

    boolean existsByEmail(String email);
}

Spring Data parses the method name at startup — findBy, countBy, existsBy establish the operation; the rest of the name (EmailAndActiveTrue, FullNameContainingIgnoreCase) is parsed against the entity's actual field names to build the query. If a method name doesn't match any real field, the application fails fast at startup with a clear error, not silently at query time.

Derived queries are ideal for simple, single-entity lookups. Once a query needs joins across relationships, aggregation, or logic that doesn't map cleanly onto a method name, it's time to reach for @Query.

@Query with JPQL#

JPQL (Jakarta Persistence Query Language) queries entities and their fields, not database tables and columns directly — the same query works regardless of the underlying database vendor:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.total > :minTotal")
    List<Order> findLargeOrdersByStatus(@Param("status") OrderStatus status, @Param("minTotal") BigDecimal minTotal);

    @Query("SELECT o FROM Order o JOIN o.customer c WHERE c.email = :email ORDER BY o.createdAt DESC")
    List<Order> findByCustomerEmail(@Param("email") String email);

    @Query("SELECT new com.example.dto.OrderSummary(o.id, o.total, o.status) FROM Order o WHERE o.customer.id = :customerId")
    List<OrderSummary> findSummariesForCustomer(@Param("customerId") Long customerId);
}

The third example — a constructor expression (SELECT new com.example.dto.OrderSummary(...)) — projects directly into a DTO instead of loading the full entity, useful when a query only needs a few fields and shouldn't pull back an entire entity graph.

Native queries#

When JPQL can't express what's needed — a database-specific function, a complex window function, a query already tuned by a DBA — nativeQuery = true runs raw SQL against actual tables/columns instead of entities:

public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query(value = "SELECT * FROM orders WHERE total > :minTotal ORDER BY total DESC LIMIT 10", nativeQuery = true)
    List<Order> findTop10ByTotal(@Param("minTotal") BigDecimal minTotal);
}

SELECT * FROM orders WHERE total > 500 ORDER BY total DESC LIMIT 10;

Native queries lose database portability and JPQL's entity-graph awareness — reach for them only when JPQL genuinely can't express what's needed, not as a default.

Pagination and sorting with Pageable/Sort#

Any repository method can accept a Pageable parameter to get pagination and sorting without writing either into the query:

public interface OrderRepository extends JpaRepository<Order, Long> {
    Page<Order> findByStatus(OrderStatus status, Pageable pageable);
}

Pageable pageable = PageRequest.of(0, 20, Sort.by("createdAt").descending());
Page<Order> page = orderRepository.findByStatus(OrderStatus.SHIPPED, pageable);

page.getContent();       // the 20 Order rows on this page
page.getTotalElements(); // total matching rows across ALL pages
page.getTotalPages();
page.hasNext();

Page<T> runs an extra COUNT query behind the scenes to know the total; if you don't need the total count, Slice<T> returns the same paged content without that extra query — cheaper when you only need "is there a next page," not "how many pages total."

Common mistakes#

• Writing an increasingly long derived-method name (findByStatusAndCustomerEmailAndCreatedAtBetweenOrderByTotalDesc) instead of switching to a clearer @Query once the name becomes hard to read at a glance.
• Using a native query where JPQL would have worked, losing database portability for no real benefit.
• Loading full entities via findAll()/derived methods when only a couple of fields are actually needed for a screen — a DTO projection avoids pulling back (and potentially lazily triggering additional queries for) data that's discarded immediately.
• Forgetting Pageable/Page entirely and loading an unbounded List<T> for a table that will only grow — fine at first, a production incident once the table has millions of rows.