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.

Before You Start

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

What You'll Learn

The Big Idea — Name the Question, Get the Answer

💡 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.

1️⃣ Derived Query Methods

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

2️⃣ Explicit Queries with @Query

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 .

3️⃣ Paging & Sorting

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.

🧠 Quick Recall

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 .

Common Errors

• ❌ Misspelling a property in a method name. Startup fails. Fix: match the entity's exact field names.
• ❌ Marathon method names. Unreadable and brittle. Fix: switch to @Query for complex conditions.
• ❌ Returning the entity for a maybe-missing row. NPE risk. Fix: return Optional<T> .
• ❌ Forgetting @Param. Named JPQL parameters won't bind. Fix: annotate each argument with the matching @Param .
• ❌ Writing table/column names in JPQL. JPQL uses entities/fields. Fix: reference the entity, or use nativeQuery = true for SQL.
• ❌ Sorting on a non-existent property. OrderBy / Sort fails. Fix: sort by a mapped field.

Pro Tips

• 💡 Keep derived names short. If the name needs a paragraph, reach for @Query .
• 💡 Prefer JPQL over native SQL for portability; use native only for vendor-specific features.
• 💡 Return Page<T> for UI lists so you get total counts for pagers.
• 💡 Use Optional<T> for single-result lookups to make absence explicit.

📋 Quick Reference

Keyword / idea

Maps to

Note

findByX

WHERE x = ?

Derived query

GreaterThan / LessThan

x > ? / x < ?

Comparisons

Containing

LIKE %?%

Substring match

And / Or

AND / OR

Combine conditions

OrderByXAsc

ORDER BY x ASC

Sorting

findTop3 / findFirst

LIMIT n

Limit rows

@Query

explicit JPQL/SQL

Full control

@Param("n")

bind :n

Named parameters

Pageable

LIMIT/OFFSET + sort

Returns Page<T>

Optional<T>

single, maybe-empty

Null-safe

📚 Resources / Keep Learning

• 📘 Spring Data JPA — query methods (docs.spring.io)
• 📗 Derived query keyword reference (docs.spring.io)
• 📗 Jakarta Persistence (JPQL) specification (jakarta.ee)

❓ Frequently Asked Questions

🎉 Lesson Complete!

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

Continue this course

• Previous: Entity Relationships
• Next: Bean Validation

Related lessons

• JPA entities