Spring Data JPA repositories and derived queries

With Spring Data JPA you declare an interface and never implement it:

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findByStatusAndTotalGreaterThan(Status status, BigDecimal min);

    Optional<Order> findFirstByCustomerIdOrderByCreatedAtDesc(long customerId);

    long countByStatus(Status status);

    Page<Order> findByCustomerId(long customerId, Pageable pageable);

    @Query("select o from Order o join fetch o.items where o.id = :id")
    Optional<Order> findWithItems(@Param("id") long id);
}

At startup Spring Data creates a proxy for the interface. The inherited methods (save, findById, findAll, deleteById, …) are served by a generic implementation, SimpleJpaRepository. Every other method is either annotated with @Query or has its query derived from its name.

Reading a derived query

The name has a subject — find…By, count…By, exists…By, delete…By, optionally limited with First or Top3 — then a predicate: property names joined by And and Or, each optionally followed by a keyword such as GreaterThan, LessThan, Between, IsNull, Containing, In or IgnoreCase, and an optional OrderBy<Property>Asc|Desc. Method parameters bind to the conditions in order — but not one per condition: Between takes two values and IsNull takes none.

A property in the predicate may also be a path through associations: findByCustomerAddressCity on an Order filters on o.customer.address.city, which JPQL turns into joins. Spring Data finds the path by trying the whole name as a property first; if there is none, it splits the name at a camel-case boundary, starting from the right, and looks for the left part as a property. When it finds one, it resolves the rest the same way inside that property's type; when it does not, it moves the split one word further left. An underscore, findByCustomer_Address_City, marks the boundaries explicitly. The model below resolves single-condition names against a small Order → Customer → Address model:

1import java.util.*;2 3public class Solution {4    // Entity metadata: type -> (property -> type of that property).5    static final Map<String, Map<String, String>> MODEL = Map.of(6        "Order", Map.of("status", "String", "createdAt", "Instant",7                        "customer", "Customer", "shippingAddress", "Address"),8        "Customer", Map.of("id", "Long", "name", "String", "address", "Address"),9        "Address", Map.of("city", "String", "zipCode", "String"));10 11    static String lowerFirst(String s) { return Character.toLowerCase(s.charAt(0)) + s.substring(1); }12 13    // Returns the dotted path, or null if no reading of the name works.14    static String resolve(String type, String name) {15        Map<String, String> props = MODEL.getOrDefault(type, Map.of());16        int underscore = name.indexOf('_');17        if (underscore >= 0) {                       // explicit boundary18            String head = lowerFirst(name.substring(0, underscore));19            if (!props.containsKey(head)) return null;20            String rest = resolve(props.get(head), name.substring(underscore + 1));21            return rest == null ? null : head + "." + rest;22        }23        if (props.containsKey(lowerFirst(name))) return lowerFirst(name);24        for (int i = name.length() - 1; i > 0; i--) {   // split points, right to left25            if (!Character.isUpperCase(name.charAt(i))) continue;26            String head = lowerFirst(name.substring(0, i));27            if (!props.containsKey(head)) continue;28            String rest = resolve(props.get(head), name.substring(i));29            if (rest != null) return head + "." + rest;30        }31        return null;32    }33 34    static String describe(String method) {35        String path = resolve("Order", method.substring("findBy".length()));36        return method + " -> " + (path == null ? "no such property: fails at startup" : "o." + path);37    }38 39    public static List<String> run() {40        return List.of(41            describe("findByStatus"),42            describe("findByCustomerId"),43            describe("findByShippingAddressZipCode"),44            describe("findByCustomerAddressCity"),45            describe("findByCustomer_Address_City"),46            describe("findByCustomerPhone"));47    }48}

Property names are checked against the entity when the repository is created. A typo such as findByStauts fails the application at startup, not at the first call — one of the real advantages of derived queries. Their weakness is readability: once a name grows past two or three conditions, write a @Query with JPQL (or nativeQuery = true for SQL) instead. Update and delete queries written with @Query also need @Modifying and must run inside a transaction.

Projections: select less

A repository method may return an interface or a record instead of the entity — List<OrderSummary> findByStatus(Status s) where OrderSummary declares only getId() and getTotal(). Spring Data then selects only the columns it needs, and nothing returned is a managed entity that could trigger lazy loading.

Paging: Page versus Slice

A Pageable (PageRequest.of(page, size, Sort.by("createdAt").descending())) adds a limit and offset. Page numbers are zero-based. Returning Page<T> also runs a count query, so the page knows the total number of elements and pages. Returning Slice<T> skips the count: it fetches one row more than the page size, only to learn whether a next page exists. On a large table that count can cost more than the page itself, so an infinite-scroll API that never shows "page 3 of 57" should use Slice. (Spring Data does skip the count when the rows it got back already reveal the total: a first page shorter than the page size, or a later page that is short but not empty.)

1import java.util.*;2 3public class Solution {4    static final int SIZE = 3;5 6    // order by id desc limit SIZE offset page * SIZE   (page is zero-based)7    static List<Integer> byOffset(List<Integer> table, int page) {8        return table.stream().sorted(Comparator.reverseOrder())9            .skip((long) page * SIZE).limit(SIZE).toList();10    }11 12    // where id < lastId order by id desc limit SIZE13    static List<Integer> byKeyset(List<Integer> table, int lastId) {14        return table.stream().filter(id -> id < lastId).sorted(Comparator.reverseOrder())15            .limit(SIZE).toList();16    }17 18    static List<Integer> seenAgain(List<Integer> first, List<Integer> second) {19        return second.stream().filter(first::contains).toList();20    }21 22    public static List<String> run() {23        List<String> out = new ArrayList<>();24 25        List<Integer> feed = new ArrayList<>(List.of(1, 2, 3, 4, 5, 6, 7));26        List<Integer> first = byOffset(feed, 0);27        feed.add(8);                                   // a new post arrives28        List<Integer> second = byOffset(feed, 1);29        out.add("offset: " + first + " then " + second + ", seen again " + seenAgain(first, second));30 31        feed = new ArrayList<>(List.of(1, 2, 3, 4, 5, 6, 7));32        first = byOffset(feed, 0);                     // the first page is the same query33        feed.add(8);34        second = byKeyset(feed, first.get(first.size() - 1));35        out.add("keyset: " + first + " then " + second + ", seen again " + seenAgain(first, second));36        return out;37    }38}

Spring Data 3.1 and later support keyset paging directly: a repository method that takes a ScrollPosition and returns a Window<T>, started with ScrollPosition.keyset().

Common mistakes

• Treating PageRequest.of(1, 20) as the first page — it is the second; pages start at 0.
• Returning Page from an endpoint that never displays a total, and paying for a count(*) on nearly every request.
• A twelve-word derived method name that nobody can review; past a few conditions, @Query is clearer and the query is visible.

What interviewers ask

• How does Spring Data implement a repository interface you never implemented? — a proxy created at startup; CRUD methods from SimpleJpaRepository, other methods derived from the name or taken from @Query, validated at startup.
• Difference between Page and Slice? — Page runs an extra count query and knows the totals; Slice only knows whether there is a next page.
• When would you write @Query instead of a derived method? — complex predicates, joins and fetch joins, aggregates, or when the name stops being readable.