skip to content

JPA Derived Query Methods

4 roadmaps5 questions

JPA derived methods traverse nested properties, support Distinct, IgnoreCase and Top, and cover exists, count and delete — until the name becomes unreadable and @Query is better. Knowing where that line is is what the question is testing.

part ofSpring Frameworkoverview, primer and where to startread it →
on this pageshow

explore

questions

5

What are JPA derived query methods, and how do keywords like IgnoreCase and OrderBy shape the generated query?

level: juniorimportance: must knowfreq 75%

answer

  1. Query from the method name
  2. prefix + criteria + IgnoreCase + OrderBy
  3. params bind positionally
  4. PropertyReferenceException at startup
  5. UPPER() can kill the index

basics

~10 s

Derived queries let Spring Data JPA build a SQL/JPQL query from the method NAME. E.g. findByEmailIgnoreCase compares email case-insensitively; adding OrderByNameAsc sorts results by name ascending. No @Query needed.

solid answer

~40 s

A derived query method is a repository method whose name Spring Data JPA parses into a query at startup. The parser strips a subject prefix (findBy, readBy, getBy, queryBy), then reads a criteria expression built from property names joined by And/Or, plus keywords: IgnoreCase for a single property, AllIgnoreCase for the whole expression, and an OrderBy...Asc/Desc clause for sorting. Method parameters bind positionally to the criteria. So findByLastNameIgnoreCaseAndActiveTrueOrderByCreatedAtDesc('smith') generates WHERE UPPER(last_name)=UPPER(?) AND active=true ORDER BY created_at DESC. The names must map to real entity properties or the app fails fast on startup. It removes boilerplate for simple lookups; complex logic should move to @Query.

code

java · 11 lines
java
public interface UserRepository extends JpaRepository<User, Long> {

    // WHERE UPPER(email) = UPPER(?1)
    Optional<User> findByEmailIgnoreCase(String email);

    // WHERE last_name = ?1 AND active = true ORDER BY created_at DESC
    List<User> findByLastNameAndActiveTrueOrderByCreatedAtDesc(String lastName);

    // AllIgnoreCase applies to every String criterion
    List<User> findByFirstNameAndLastNameAllIgnoreCase(String first, String last);
}

go deeper

for a junior

Know that the method name generates the query and that IgnoreCase/OrderBy are name keywords.

for a middle

Explain positional parameter binding and fail-fast PropertyReferenceException at startup.

for a senior

Discuss AllIgnoreCase vs IgnoreCase, return-type semantics, and the UPPER() index impact.

for a principal

Weigh derived-method readability limits vs @Query/Specifications and normalized-column strategies for case-insensitive search at scale.

## What it is Spring Data JPA can implement a repository method purely from its NAME — you declare the signature, Spring writes the query. These are called **derived query methods** (or query-derivation). ## Anatomy of the name `findByLastNameIgnoreCaseOrderByCreatedAtDesc` 1. **Subject / prefix** — `findBy`, `readBy`, `getBy`, `queryBy`, `searchBy`, `streamBy` all mean the same 'select' intent. `existsBy`, `countBy`, `deleteBy`/`removeBy` change the operation. 2. **Criteria** — property names (`LastName`) combined with `And` / `Or`, each optionally followed by an operator keyword (`Like`, `Between`, `GreaterThan`, `In`, `True`, `Null`, etc.). Bare property means equality. 3. **`IgnoreCase`** — placed right after a String property, wraps both sides in `UPPER(...)` so the comparison is case-insensitive: `findByEmailIgnoreCase`. **`AllIgnoreCase`** at the end applies it to every String criterion. 4. **`OrderBy...Asc/Desc`** — static sort baked into the method: `OrderByCreatedAtDesc`. You can also sort dynamically instead by adding a `Sort` parameter. ## How parameters bind Parameters bind **positionally, left to right**, to the criteria in the name. `findByFirstNameAndLastName(String first, String last)` — first param → FirstName, second → LastName. Count and order must match or startup fails. ## Fail-fast Spring parses every derived method when the `EntityManagerFactory`/repository bean initializes. A typo like `findByEmial` that doesn't map to a property throws `PropertyReferenceException` at startup, not at runtime — a key safety benefit. ## Return types `find...` can return the entity, `Optional<T>`, `List<T>`, `Stream<T>`, `Page<T>`, `Slice<T>`. Returning a single object when multiple rows match throws `IncorrectResultSizeDataAccessException`. ## When to use / stop Great for simple, stable lookups. Once the name grows past ~2-3 conditions it becomes unreadable — switch to `@Query` (JPQL) or Specifications. `IgnoreCase` on a non-String property throws at startup. ## Gotcha `IgnoreCase` uses `UPPER()`, which can defeat a plain B-tree index on that column unless you have a functional/expression index. For high-volume case-insensitive lookups consider a normalized (already-lowercased) column instead.

  • What happens if you misspell a property name in a derived method?
    The application fails at startup with a PropertyReferenceException when Spring parses the repository — it does not wait until the method is called at runtime.
  • What is the difference between IgnoreCase and AllIgnoreCase?
    IgnoreCase applies to the single preceding String property; AllIgnoreCase, placed at the end, applies case-insensitivity to every String property in the criteria.

saying these in an interview costs you the question

  • Thinking the query is parsed lazily on first call rather than at startup
  • Believing IgnoreCase works on numeric/date properties
  • Assuming derived methods need @Query to sort

context

resolving links…

open as a page

How do Distinct and the First/Top limiting keywords behave in derived query methods?

level: middleimportance: should knowfreq 58%

basics

~10 s

Distinct adds SELECT DISTINCT to remove duplicate rows. First and Top limit how many rows come back: findFirstByOrderByScoreDesc returns one row; findTop10By... returns ten. First and Top are interchangeable.

open as a page

How does nested property traversal work in a derived method, and why does findByAddress_City use an underscore?

level: middleimportance: should knowfreq 60%

basics

~20 s

You can navigate into related entities by camel-casing the path: findByAddressCity reaches the City field of the Address association (a JOIN). If a property name is ambiguous, an underscore (findByAddress_City) tells Spring exactly where to split the path.

open as a page

What do the existsBy, countBy, and deleteBy derived prefixes do, and what must you add for derived deletes to work correctly?

level: seniorimportance: should knowfreq 55%

basics

~10 s

existsBy returns a boolean (efficient existence check), countBy returns a long count, and deleteBy/removeBy delete matching rows. Derived deletes are modifying queries, so they must run inside a transaction (@Transactional).

open as a page

When do the limits of derived query methods force you to switch to @Query, Specifications, or Pageable?

level: principalimportance: should knowfreq 45%

basics

~20 s

Derived methods can only express what fits in a method name: fixed conditions, static sort, and a fixed First/Top limit. Anything needing projections, LEFT joins, subqueries, computed expressions, offset paging, or dynamic optional filters must move to @Query, Pageable, or Specifications/Criteria.

open as a page