skip to content

Explain the derived-query keywords And, Or, Between, Like, In, IsNull, and OrderBy, and how they combine into a predicate.

level: middleimportance: must knowfreq 58%

answer

  1. Or splits into OR-parts, each an AND-chain
  2. arity: Between=2, In=Collection, IsNull=0
  3. OrderBy...Asc/Desc = static sort, no args
  4. StartingWith/Containing add % for you
  5. no Group By / Having => use @Query

basics

~10 s

And/Or combine conditions; Between matches a range (two args); Like does pattern matching; In matches a collection; IsNull checks for null (no arg); OrderBy sorts results, e.g. OrderByAgeDesc.

solid answer

~40 s

Inside the predicate you chain property conditions with the boolean connectors And and Or (Or has lower precedence, so the parser groups by Or into OR-parts, each an AND of conditions). Comparison keywords qualify a property: Between takes two arguments (a range), Like/StartingWith/Containing do string pattern matching, In takes a Collection, GreaterThan/LessThan compare, and IsNull/IsNotNull take no argument at all. A trailing OrderBy clause adds static sorting with Asc/Desc, e.g. findByLastNameOrderByAgeDesc. Each keyword changes how many method arguments that fragment consumes — IsNull consumes none, Between consumes two, plain equality one — so argument count must line up with the keywords used. This grammar is fixed vocabulary; anything it can't express needs an explicit @Query.

code

java · 17 lines
java
public interface PersonRepository extends CrudRepository<Person, Long> {

    // (lastName = ?) AND (age BETWEEN ? AND ?)  -> 3 args total
    List<Person> findByLastNameAndAgeBetween(String lastName, int lo, int hi);

    // firstName LIKE ?  (caller passes e.g. "An%")
    List<Person> findByFirstNameLike(String pattern);

    // age IN (?)  -> one Collection argument
    List<Person> findByAgeIn(Collection<Integer> ages);

    // deletedAt IS NULL  -> zero arguments for the predicate
    List<Person> findByDeletedAtIsNull();

    // (firstName = ?) OR (lastName = ?), then static sort by age desc
    List<Person> findByFirstNameOrLastNameOrderByAgeDesc(String fn, String ln);
}

go deeper

for a junior

Recognizes the common keywords and that OrderBy sorts.

for a middle

Must map each keyword to its argument arity and explain Or/And grouping precedence.

for a senior

Adds IgnoreCase, StartingWith/Containing wildcard handling, and static-vs-dynamic sort tradeoffs.

for a principal

Knows the grammar's expressiveness ceiling and when the readable line is crossed toward @Query.

The predicate (everything after `By`) is a small boolean expression built from keywords. Understanding each keyword's *argument arity* is the crux. **Boolean connectors.** - `And` — logical AND between two property conditions: `findByFirstNameAndLastName(...)`. - `Or` — logical OR: `findByFirstNameOrLastName(...)`. Precedence: the parser first splits the predicate on `Or` into *OR-parts*, and each OR-part is an AND-chain of individual conditions. So `findByAAndBOrC` means `(A AND B) OR C` — there are no explicit parentheses; you cannot regroup. If you need different grouping, use `@Query`. **Comparison / matching keywords (qualify the preceding property).** - `Between` — range test, **consumes two arguments**: `findByAgeBetween(int lo, int hi)`. - `LessThan` / `LessThanEqual` / `GreaterThan` / `GreaterThanEqual` — one argument comparisons. - `Like` / `NotLike` — pattern match; you supply the wildcards (`%`) yourself in the argument. - `StartingWith` / `EndingWith` / `Containing` — convenience matchers that wrap the argument with the store's wildcard for you. - `In` / `NotIn` — membership; **argument is a Collection** (or array/varargs): `findByAgeIn(Collection<Integer> ages)`. - `IsNull` / `Null` and `IsNotNull` / `NotNull` — null checks that **consume no argument**. - `True` / `False` — boolean shortcuts, no argument: `findByActiveTrue()`. - `IsNot` / `Not` — negated equality. - `IgnoreCase` — modifies a string comparison; `AllIgnoreCase` at the end applies to all string conditions. **Sorting: `OrderBy`.** A single trailing `OrderBy` clause defines *static* sort order, each property followed by `Asc` (default) or `Desc`: `findByLastNameOrderByFirstNameAscAgeDesc`. It is not a predicate — it consumes no arguments. For *dynamic* sorting, drop `OrderBy` and add a `Sort` (or `Pageable`) method parameter instead. **Limiting.** Separate from predicate keywords, the subject can carry `Distinct`, and `Top`/`First` limits: `findFirst5ByOrderByAgeDesc`, `findDistinctByLastName`. **Argument-count invariant.** Because each keyword has a fixed arity, the number of method parameters (excluding `Sort`/`Pageable`) must equal the sum of arities. A mismatch is a startup error. This is the most common derived-query bug. **Limits.** The vocabulary is finite and store-agnostic in Commons; there is no `Group By`, `Having`, join-fetch, or arbitrary function call. Reach for `@Query` when you exceed it.

  • How is findByAAndBOrC grouped, and can you make it A AND (B OR C)?
    It parses as (A AND B) OR C — Or splits into top-level OR-parts, each an AND-chain. You cannot express A AND (B OR C) with derived naming; use @Query for custom grouping.
  • What's the difference between OrderByAgeDesc and passing a Sort parameter?
    OrderBy fixes the sort at compile time in the name; a Sort/Pageable parameter lets the caller choose the sort dynamically at runtime. Don't combine both for the same property.

saying these in an interview costs you the question

  • Claiming Between takes one argument (it takes two).
  • Thinking And/Or support explicit parentheses/grouping.
  • Passing an argument for IsNull/True/False, which take none.
  • Believing OrderBy consumes a method argument.

context

resolving links…