Derived Queries & @Query

Reviewed & published by Brayan K

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.

Part of the free Java course at LearnCodingFast — hands-on lessons with worked examples and the output they print, 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

• Writing derived query methods (findByX)
• Keywords: And, GreaterThan, Containing, OrderBy
• Explicit JPQL with @Query
• Binding parameters with @Param
• Native SQL queries when needed
• Paging and sorting with Pageable / Page

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.

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.domain.*;
import java.util.*;

// Spring Data parses these METHOD NAMES and generates the queries for you.
public interface CustomerRepository extends JpaRepository<Customer, Long> {

    // WHERE last_name = ?
    List<Customer> findByLastName(String lastName);

    // WHERE first_name = ? AND last_name = ?
    Customer findByFirstNameAndLastName(String first, String last);

    // WHERE age > ?
    List<Customer> findByAgeGreaterThan(int age);

    // WHERE email LIKE ? (containing) ... ORDER BY last_name ASC
    List<Customer> findByEmailContainingOrderByLastNameAsc(String part);

    // null-safe single result
    Optional<Customer> findByEmail(String email);

    // LIMIT 3
    List<Customer> findTop3ByOrderByAgeDesc();

    // paging + sorting
    Page<Customer> findByLastName(String lastName, Pageable pageable);
}

import java.util.*;
import java.util.stream.*;

// PLAIN-JAVA illustration of what "findByAgeGreaterThan + OrderBy" MEANS.
// Spring generates SQL; here we filter and sort an in-memory list the same way.
public class Main {
    record Customer(String name, int age) { }

    public static void main(String[] args) {
        List<Customer> all = List.of(
            new Customer("Ana", 31),
            new Customer("Ben", 22),
            new Customer("Cara", 45),
            new Customer("Dan", 28));

        // findByAgeGreaterThan(25) ORDER BY age ASC
        List<Customer> result = all.stream()
            .filter(c -> c.age() > 25)
            .sorted(Comparator.comparingInt(Customer::age))
            .collect(Collectors.toList());

        result.forEach(c -> System.out.println(c.name() + " (" + c.age() + ")"));
    }
}

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.

import org.springframework.data.jpa.repository.*;
import org.springframework.data.repository.query.Param;
import java.util.*;

public interface CustomerRepository extends JpaRepository<Customer, Long> {

    // Explicit JPQL with a NAMED parameter bound via @Param.
    @Query("SELECT c FROM Customer c WHERE c.lastName = :name")
    List<Customer> withLastName(@Param("name") String name);

    // JPQL across an association + ordering.
    @Query("SELECT c FROM Customer c WHERE c.age BETWEEN :lo AND :hi ORDER BY c.age")
    List<Customer> inAgeRange(@Param("lo") int lo, @Param("hi") int hi);

    // A NATIVE SQL query when you need database-specific features.
    @Query(value = "SELECT * FROM customers WHERE email LIKE %:part%", nativeQuery = true)
    List<Customer> emailLike(@Param("part") String part);
}

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.

import java.util.*;

// PLAIN-JAVA illustration of paging metadata (like Spring's Page<T>).
public class Main {
    public static void main(String[] args) {
        List<String> all = List.of("a","b","c","d","e","f","g");
        int pageSize = 3, pageNumber = 1;       // zero-based: page 1 = second page

        int from = pageNumber * pageSize;
        int to   = Math.min(from + pageSize, all.size());
        List<String> content = all.subList(from, to);

        int totalElements = all.size();
        int totalPages = (int) Math.ceil((double) totalElements / pageSize);

        System.out.println("content: " + content);
        System.out.println("page " + pageNumber + " of " + totalPages);
        System.out.println("totalElements: " + totalElements);
    }
}

🧠 Quick Recall

Predict before you peek.

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.

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.*;

// YOUR TURN: add derived query methods to ProductRepository.
//  1) find all products in a given category  -> findByCategory(String c)
//  2) find products priced below an amount    -> findByPriceLessThan(int cents)
//  3) find one product by exact name, null-safe -> Optional<Product> ...
//  4) the 5 most expensive products           -> findTop5ByOrderByPriceDesc()
//
// public interface ProductRepository extends JpaRepository<Product, Long> {
//     ...
// }

🧩 MINI-CHALLENGE — @Query with paging

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

import org.springframework.data.jpa.repository.*;
import org.springframework.data.repository.query.Param;
import org.springframework.data.domain.*;
import java.util.*;

// MINI-CHALLENGE: write @Query methods.
//  1) JPQL: orders placed by a given customer id, newest first.
//       @Query("SELECT o FROM Order o WHERE o.customerId = :cid ORDER BY o.createdAt DESC")
//  2) JPQL with paging: products in a category, returning Page<Product>
//       (add a Pageable parameter; return Page<Product>).
//  3) Use @Param to bind the named parameters.
//
// Think: when is a derived method clearer than @Query, and vice versa?

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

📚 Resources / Keep Learning

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

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

Practice quiz

Continue this course

• Previous: Entity Relationships
• Next: Bean Validation — @Valid, @NotNull/@Size/@Email and handling validation errors
• Quick reference: Java cheat sheet

Frequently asked questions

How do derived query methods work?

Spring Data parses the method name into a query at startup. A name like findByLastNameAndAgeGreaterThan is split into the action (find), the properties (lastName, age), and keywords (And, GreaterThan), which Spring turns into a JPQL/SQL query against the entity. You never write the query body — just declare the method on an interface extending JpaRepository, and Spring provides the implementation. This is ideal for straightforward lookups where the method name stays readable.

When should I use @Query instead of a derived method?

Use @Query when the query is too complex to express clearly in a method name — joins across associations, custom projections, aggregate functions, conditional logic, or database-specific SQL. A method called findByFirstNameAndLastNameAndAgeBetweenAndStatusOrderByCreatedAtDesc is a sign you have outgrown derived queries. @Query lets you write explicit JPQL (portable, entity-oriented) or, with nativeQuery = true, raw SQL. It keeps the repository readable while giving you full control over the query.

What is the difference between JPQL and native SQL in @Query?

JPQL (the default for @Query) queries entities and their fields rather than tables and columns, so it is database-portable and refactor-friendly — SELECT c FROM Customer c references the entity, not a table. Native SQL (nativeQuery = true) is plain SQL against actual tables and columns, which you use when you need database-specific features, vendor functions, or performance tuning JPQL cannot express. The trade-off is that native queries tie you to a particular database dialect.

How do @Param and named parameters work?

In a JPQL string you reference parameters by name with a colon, like :name. You then bind each one to a method argument by annotating that argument with @Param("name"). The names must match. Named parameters are clearer and safer than positional ?1, ?2 binding, especially as queries grow, and they always use parameter binding under the hood — never string concatenation — which protects against SQL injection.

How does paging and sorting work with Pageable?

Add a Pageable parameter to a repository method and return a Page<T>. The caller builds a Pageable with PageRequest.of(pageNumber, pageSize, sort), and Spring applies the limit, offset, and ORDER BY automatically. The returned Page<T> carries not just the slice of content but also metadata: total element count, total number of pages, and whether more pages exist. If you only need the slice without the count query, you can return a Slice<T> instead, which is cheaper.

Related lessons

• JPA entities