Derived Query Methods

Let Spring Data build queries from method names like findByStatusAndCreatedAtAfter, and know when a name has grown too long.

SyntaxOptional<User> findByEmailIgnoreCase(String email); List<Order> findByStatusAndCreatedAtAfter(Status s, Instant since); boolean existsBySku(String sku); List<Order> findFirst10ByCustomerIdOrderByCreatedAtDesc(Long customerId);

Spring Data parses repository method names and builds the query from them. findByEmail(String email) becomes where email = ?. The name starts with a subject (find, exists, count, delete) followed by By and a set of property conditions joined with And or Or.

Keywords you will use

MethodCondition
findByStatusIn(Collection<Status>)status in (...)
findByCreatedAtBetween(Instant, Instant)created_at between ? and ?
findByNameContainingIgnoreCase(String)upper(name) like upper('%x%')
findByShippedAtIsNull()shipped_at is null
findFirst10ByOrderByCreatedAtDesc()latest ten rows
findByCustomerEmail(String)joins through the customer association

Return types shape the query too. Optional<T> or T expects at most one row and throws IncorrectResultSizeDataAccessException if there are more. existsBy stops at the first match, which is cheaper than countBy when you only need a yes or no. A trailing Limit or Pageable parameter caps the result at call time.

Mistakes that only show at runtime

The property names are checked when the application starts. A typo or a renamed field fails startup with PropertyReferenceException, which is good: it is caught by any test that loads the context.

Containing becomes a leading-wildcard LIKE, which no ordinary B-tree index can serve. On a large table this is a full scan per request. For real search use a trigram or full-text index with a native query.

Derived deleteBy methods load every matching entity and delete them one by one so that cascades and lifecycle callbacks run. Deleting 50,000 expired sessions this way means 50,000 statements. Use a @Modifying query for bulk deletes. Derived deletes also need a transaction, so call them from a @Transactional method.

Ambiguous paths are resolved greedily. If an entity has both an addressZip field and an address association with a zip, write findByAddress_Zip to force the traversal.

When to stop

Once a name reads like findByStatusAndCustomerRegionAndCreatedAtAfterOrderByTotalDesc, nobody will review it properly. Switch to @Query with a short, intention-revealing method name. Derived methods are best for one to three conditions.

Example

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.Id;
import java.math.BigDecimal;
import java.time.Instant;
import java.util.Collection;
import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Limit;
import org.springframework.data.jpa.repository.JpaRepository;

enum OrderStatus { PENDING, PAID, SHIPPED, CANCELLED }

@Entity
class PurchaseOrder {
    @Id
    @GeneratedValue
    private Long id;
    private String reference;
    private Long customerId;
    @Enumerated(EnumType.STRING)
    private OrderStatus status;
    private BigDecimal total;
    private Instant createdAt;
    private Instant shippedAt;

    protected PurchaseOrder() { }
}

public interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {

    Optional<PurchaseOrder> findByReference(String reference);

    boolean existsByReference(String reference);

    List<PurchaseOrder> findByCustomerIdOrderByCreatedAtDesc(Long customerId, Limit limit);

    List<PurchaseOrder> findByStatusInAndCreatedAtBefore(Collection<OrderStatus> statuses, Instant before);

    List<PurchaseOrder> findByStatusAndShippedAtIsNull(OrderStatus status);

    long countByStatus(OrderStatus status);

    List<PurchaseOrder> findByTotalGreaterThanEqual(BigDecimal minimum);
}

When to use it

  • Look a customer up by email at login with a method that cannot accidentally return two rows.
  • Show the ten most recent orders on a customer's account page.
  • Check whether a SKU is taken before creating a product, without counting every match.

More examples

Traversing an association

findByAuthorLastNameIgnoreCase joins book to author and compares upper-cased last names. The underscore in findByAuthor_Id removes any doubt about where the property path splits. Note that the books are returned with lazy authors: reading book.getAuthor().getLastName() afterwards still costs a query per book unless you fetch them together.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.ManyToOne;
import java.util.List;
import org.springframework.data.repository.ListCrudRepository;

@Entity
class Author {
    @Id
    private Long id;
    private String lastName;

    protected Author() { }
}

@Entity
class Book {
    @Id
    private Long id;
    private String title;
    @ManyToOne(fetch = FetchType.LAZY)
    private Author author;

    protected Book() { }
}

public interface BookRepository extends ListCrudRepository<Book, Long> {
    List<Book> findByAuthorLastNameIgnoreCase(String lastName);

    List<Book> findByAuthor_Id(Long authorId);
}

Limits chosen by the caller

Sort and Limit parameters keep the method name short and let the service decide the order and size per call. Clamp the limit before passing it: a client asking for a million rows should not reach the database. Limit.unlimited() exists for the rare call that really wants everything.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import java.time.Instant;
import java.util.List;
import org.springframework.data.domain.Limit;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.Repository;
import org.springframework.stereotype.Service;

@Entity
class Event {
    @Id
    private Long id;
    private String type;
    private Instant occurredAt;

    protected Event() { }
}

interface EventRepository extends Repository<Event, Long> {
    List<Event> findByType(String type, Sort sort, Limit limit);
}

@Service
public class EventFeed {
    private final EventRepository events;

    public EventFeed(EventRepository events) { this.events = events; }

    public List<Event> latest(String type, int max) {
        return events.findByType(type, Sort.by(Sort.Direction.DESC, "occurredAt"), Limit.of(Math.min(max, 100)));
    }
}

A bulk delete instead of a derived one

Both methods remove the same rows. The derived version exists so that cascades and @PreRemove callbacks run; when the entity has neither, the single DELETE is the one to use in a nightly clean-up job. The bulk statement bypasses the persistence context, so do not keep loaded Session instances around after calling it.

Example · java
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import java.time.Instant;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

@Entity
class Session {
    @Id
    private String id;
    private Instant expiresAt;

    protected Session() { }
}

public interface SessionRepository extends JpaRepository<Session, String> {

    // Loads each row, then deletes it: fine for a handful, slow for thousands.
    @Transactional
    long deleteByExpiresAtBefore(Instant cutoff);

    // One DELETE statement, no entities loaded.
    @Transactional
    @Modifying
    @Query("delete from Session s where s.expiresAt < :cutoff")
    int purgeExpired(@Param("cutoff") Instant cutoff);
}

Related

sb repositoriessb query and jpqlsb pagination and sortingsb projections

Discussion

  • Be the first to comment on this lesson.