Derived Queries & @Query

Spring Data turns method names into queries — findByLastName just works. When that is not enough, @Query lets you write explicit JPQL, and a Pageable adds paging and sorting.

Learn Derived Queries & @Query in our free Java course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a quick reference.

Part of the free Java course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

You should have mapped JPA entities and connected them with relationships . Familiarity with the Java Stream API helps read the in-memory illustrations.

💡 Analogy: A derived query method is like asking a librarian in plain words: "find by last name." You just name what you want and the librarian (Spring Data) writes the catalog search for you. @Query is for when the request is complicated enough that you hand over a written request slip (the JPQL) yourself. And Pageable is saying "give me the third shelf of ten, sorted alphabetically."

You declare intent on an interface; Spring supplies the implementation.

Declare a method whose name describes the query. Spring parses keywords like And , GreaterThan , Containing , and OrderBy to build it.

When a name would get unwieldy, write the query yourself with @Query . Use JPQL (entity-oriented and portable) by default, bind parameters with @Param , or drop to native SQL with nativeQuery = true .

Add a Pageable parameter and return a Page<T> . The caller builds it with PageRequest.of(page, size, sort) , and the result carries total counts. Here is the paging math in plain Java.

Answer: WHERE status = ? AND age < ? — And joins the conditions and LessThan maps to < .

Answer: annotate the matching argument with @Param("name") .

Answer: paging metadata — total elements, total pages, and the current page — alongside the content slice.

🎯 YOUR TURN — Product derived queries

Add four derived methods to a ProductRepository : by category, below a price, a null-safe by-name, and the five most expensive.

🧩 MINI-CHALLENGE — @Query with paging

Write JPQL @Query methods, including one that returns a Page<Product> using a Pageable .

You can now let Spring Data generate queries from method names, write explicit JPQL or native SQL with @Query , bind values safely with @Param , and add paging and sorting with Pageable and Page<T> .

Next up: Bean Validation — guarding your inputs with @Valid , @NotNull , @Size , and friends.

Practice quiz

How does Spring Data implement a method named findByLastName(String name)?

  • You must write SQL by hand
  • It parses the method name and generates the query
  • It returns null
  • It throws at runtime

Answer: It parses the method name and generates the query. Spring Data parses derived query method names and generates the query automatically from the property names.

What does findByAgeGreaterThan(int age) translate to?

  • WHERE age = ?
  • WHERE age < ?
  • WHERE age > ?
  • ORDER BY age

Answer: WHERE age > ?. The GreaterThan keyword maps to a > comparison on the age property.

Which keyword combines two conditions in a derived query?

  • AlsoWhere
  • And
  • Plus
  • With

Answer: And. And (and Or) join multiple property conditions, e.g. findByFirstNameAndLastName.

What does the @Query annotation let you provide?

  • A bean name
  • A cache key
  • A validation rule
  • An explicit JPQL or native query

Answer: An explicit JPQL or native query. @Query lets you specify an explicit JPQL (or native) query instead of relying on the method name.

In @Query, how are named parameters bound to method arguments?

  • By position only
  • With @Param matching the :name placeholder
  • Automatically by type
  • They cannot be bound

Answer: With @Param matching the :name placeholder. @Param("name") binds a method argument to the :name placeholder in the JPQL.

What does a derived query method's OrderBy keyword do?

  • Filters rows
  • Limits the count
  • Sorts the result by a property
  • Joins tables

Answer: Sorts the result by a property. OrderBy<Property>Asc/Desc sorts the results by that property without a separate Sort argument.

Which return type makes a single-result derived query null-safe?

  • String
  • Optional<T>
  • int
  • void

Answer: Optional<T>. Returning Optional<T> expresses that a single-result query may find nothing, avoiding null surprises.

What does a Pageable parameter add to a repository method?

  • Caching
  • Transactions
  • Validation
  • Pagination and sorting

Answer: Pagination and sorting. Passing a Pageable enables paging (page number and size) and sorting, returning a Page<T>.

What extra information does a Page<T> provide over a List<T>?

  • Nothing
  • Total elements and total pages metadata
  • The SQL text
  • The entity class

Answer: Total elements and total pages metadata. Page<T> includes total element count, total pages, and current page info in addition to the content.

Which keyword in a derived method limits results to the first match?

  • findTop / findFirst
  • findOnly
  • findLimit
  • findSingle

Answer: findTop / findFirst. findFirst and findTop (e.g. findTop3By...) limit the number of returned rows.