Data with JPA›Extra· 50 min read

Custom Queries, @Query & Pagination

Go beyond findAll: write derived finder methods by name, custom JPQL with @Query, and return data one page at a time with Pageable.

What you will learn

Write derived query methods from their name
Write a custom query with @Query (JPQL)
Page and sort results with Pageable

Three ways to query, from easy to powerful

You already get findAll, findById and friends for free. Real apps need more specific queries — “find all products under 500”, “find books by this author”, “give me page 2 of the results”. Spring Data JPA offers three tools, from simplest to most flexible:

Tool	Best for
Derived methods (name the method)	Simple filters by field
`@Query` (write JPQL/SQL)	Anything too complex for a name
`Pageable`	Returning large results in pages

Derived query methods: just name them

A derived query method is one where Spring writes the query from the method name. You declare the method in the repository interface — no body — and Spring reads words like findBy, And, LessThan, OrderBy and turns them into a query.

Four derived methods — Spring writes the query from each name

public interface ProductRepository
        extends JpaRepository<Product, Long> {

    List<Product> findByName(String name);

    List<Product> findByPriceLessThan(double max);

    List<Product> findByNameContainingIgnoreCase(String text);

    List<Product> findByPriceLessThanOrderByPriceAsc(double max);
}

Note: Output: findByPriceLessThan(500) runs: SELECT * FROM product WHERE price < 500 findByNameContainingIgnoreCase("key") runs: SELECT * FROM product WHERE LOWER(name) LIKE '%key%' You wrote no SQL — Spring built each query from the method name. The keywords (LessThan, Containing, IgnoreCase, OrderBy) map directly to SQL.

Common keywords you can use in a name

Keyword in method name	Becomes
`findByName`	WHERE name = ?
`findByPriceLessThan`	WHERE price < ?
`findByPriceBetween`	WHERE price BETWEEN ? AND ?
`findByNameContaining`	WHERE name LIKE %?%
`...AndStock...`	adds AND stock = ?
`...OrderByPriceDesc`	ORDER BY price DESC

When a name is not enough: @Query

Some queries are too complex to express in a method name. For those, write the query yourself with @Query. It uses JPQL (Java Persistence Query Language — like SQL, but you query your entities and their fields, not raw table and column names).

A custom JPQL query with @Query and named parameters

public interface ProductRepository
        extends JpaRepository<Product, Long> {

    // :max is a named parameter, filled by the argument below
    @Query("SELECT p FROM Product p WHERE p.price < :max AND p.name LIKE %:text%")
    List<Product> search(double max, String text);
}

Note: Output: search(500, "key") runs roughly: SELECT * FROM product WHERE price < 500 AND name LIKE '%key%' Note JPQL says Product (the entity name) and p.price (the field), not the table/column names. The :max and :text are filled from the method arguments.

Reading the JPQL piece by piece: SELECT p FROM Product p means “select Product entities, calling each one p”. WHERE p.price < :max filters by the field price. The :max is a named parameter — a placeholder Spring fills with the matching method argument, which keeps the query safe from injection.

Pagination: return results one page at a time

A query that returns 50,000 rows would be slow and huge. Pagination means returning a small page of results at a time (say 10), plus the info needed to fetch the next page. Spring gives you this almost for free: accept a Pageable and return a Page.

Paged, sorted results driven by query parameters

// In the repository — Spring already supports this signature:
Page<Product> findByPriceLessThan(double max, Pageable pageable);

// In the controller — read page & size from the URL:
@GetMapping("/products")
public Page<Product> list(
        @RequestParam(defaultValue = "0") int page,
        @RequestParam(defaultValue = "10") int size) {
    Pageable pageable = PageRequest.of(page, size, Sort.by("price"));
    return repo.findByPriceLessThan(100000, pageable);
}

Note: Output (visiting /products?page=0&size=2): { "content": [ {"id":1,"name":"Mouse","price":499.0}, {"id":2,"name":"Keyboard","price":799.0} ], "totalElements": 25, "totalPages": 13, "number": 0, "size": 2 } The content holds just this page (2 items). The extras tell the client there are 25 items over 13 pages, and this is page 0 — everything a front-end needs to build “Next / Previous” buttons.

Reading the worked values: with 25 products and a page size of 2, there are totalPages = ceil(25 ÷ 2) = 13 pages. PageRequest.of(0, 2, Sort.by("price")) asks for page 0, two per page, sorted by price ascending.

Tip: Pagination is not optional for list endpoints in real apps — without it, a growing table eventually returns thousands of rows in one response and slows everything down. Make list endpoints paged from the start.

Watch out: Page numbers are zero-based: page 0 is the first page, 1 is the second. A very common bug is sending page=1 expecting the first page and getting the second instead.

Q. When would you reach for @Query instead of a derived method name?

Answer: Derived methods are great for simple filters you can spell out in a name. When the logic gets complex, @Query lets you write the JPQL (or SQL) directly while still using named parameters safely.

✍️ Practice

Add a derived method findByAuthorContainingIgnoreCase(String text) to a Book repository and use it in an endpoint.
Add a paged /books?page=0&size=5 endpoint that returns a Page<Book> sorted by title.

🏠 Homework

Add to your Task API: a derived findByTitleContaining search, a custom @Query that finds tasks by done-status, and a paged list endpoint. Test each with different query parameters.