Skip to content
Tutorials Center
My learning

Topics

Lesson 19 of 38 ยท Data Access

Queries, Pagination and Projections

Write explicit @Query methods, page results with Pageable and Page, and return slim DTO projections in Micronaut Data. Runnable H2 stand-ins show the SQL behind them.

Derived finder names are great until they are not. findByAuthorAndPublishedYearBetweenAndPagesGreaterThanOrderByTitle is a sentence nobody wants to read. Real services also need to return pages of results instead of a million rows, and to select only the columns a screen actually shows. Micronaut Data covers all three: explicit @Query methods, Pageable and Page, and projections.

The framework is not installed on this site, so the Micronaut code is read-only. The runnable programs use real SQL on the H2 database in the Java runner, so every query, count and page boundary you see is genuine; only the Micronaut annotations are missing.

Explicit queries with @Query

Write the SQL (or JPQL when you use the Hibernate module) yourself and bind parameters by name with a colon:

Java
@JdbcRepository(dialect = Dialect.POSTGRES)
public interface BookRepository extends PageableRepository<Book, Long> {

    @Query("SELECT * FROM book WHERE author = :author AND pages >= :minPages ORDER BY title")
    List<Book> findLongBooksBy(String author, int minPages);

    @Query("UPDATE book SET pages = :pages WHERE id = :id")
    void updatePages(long id, int pages);

    @Query("SELECT COUNT(*) FROM book WHERE author = :author")
    long totalFor(String author);
}

Method parameters become the named parameters with the same names. Micronaut Data validates what it can at compile time: parameter names that do not match are build errors. Use @Query for joins, aggregates and anything that reads better as SQL than as a method name. The values are always bound as parameters, never pasted into the string, so queries stay safe from SQL injection.

For LIKE searches, build the pattern in your code, not in the SQL string:

Java
@Query("SELECT * FROM book WHERE LOWER(title) LIKE :pattern")
List<Book> search(String pattern);       // call with "%" + text.toLowerCase() + "%"

Pageable and Page

Never return unbounded lists from an API. A Pageable describes which slice you want, and a Page carries that slice plus totals:

Java
Page<Book> findByAuthor(String author, Pageable pageable);

// in a controller
@Get("/books{?author}")
Page<Book> list(String author, Pageable pageable) {
    return repository.findByAuthor(author, pageable);
}

Micronaut binds Pageable from the query string (?page=0&size=20&sort=title,asc) by default. The Page object serializes as JSON with content, pageable, totalSize and totalPages. Remember:

  • Page numbers start at 0.
  • A Page runs a second query, COUNT(*), to know the total. When you do not need the total (an "infinite scroll"), return Slice<Book> instead, which skips the count and only knows whether another page exists.
  • Always add a stable sort. Without one, rows can repeat or vanish between pages.
  • Limit the maximum size. Set micronaut.data.pageable.max-page-size so a client cannot ask for 100,000 rows.
Java
Pageable first = Pageable.from(0, 10, Sort.of(Sort.Order.asc("title")));
Page<Book> page = repository.findAll(first);

Offset versus keyset

Offset pagination (LIMIT 10 OFFSET 10000) makes the database walk past 10,000 rows to reach the page, so deep pages get slow, and a new row inserted at the top shifts everything. Keyset pagination remembers the last value seen and asks for rows after it: WHERE id > :lastId ORDER BY id LIMIT 10. It stays fast on any page, but it can only move next or previous, not jump to page 50.

Projections

Loading entire rows is wasteful when a list view needs two columns. A projection is a smaller type that holds only what you need.

Java
@Introspected
public record BookSummary(Long id, String title) {}

List<BookSummary> findByAuthor(String author);            // columns are chosen from the DTO
List<String> findTitleByAuthor(String author);           // a single column
@Query("SELECT author, COUNT(*) AS total FROM book GROUP BY author")
List<AuthorStats> authorStats();

Micronaut Data selects only the columns the return type needs. For a DTO record, the component names must match column or property names. Projections reduce data transfer, memory and the chance of accidentally exposing a field such as passwordHash.

Three ways to ask

Finder names Short and checked at build time.
@Query Full SQL for joins and aggregates.
Projections Return only the columns you need.

Choose readability first.

1 / 2

Stand-in 1: pages with LIMIT, OFFSET and a count

This program fills a table with 23 books and implements Page by hand with real SQL: one query for the slice and one COUNT(*) for the total. It prints each page and shows the rules above at work: zero-based pages, an incomplete last page, and an empty page beyond the end.

Java Offset pagination with a Page record

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Page 2 has only 3 rows because 23 = 10 + 10 + 3, and page 3 is empty. A good API either returns 404 or an empty page for a number past the end; decide and document it.

Stand-in 2: keyset pagination and projections

The second program runs a keyset ("seek") query: it remembers the last id seen and asks for the next rows after it, never using OFFSET. It then runs a projection query that selects only two columns into a small record, plus an aggregate query that would be a @Query method in Micronaut Data.

Java Keyset paging, DTO projection and aggregates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Notice that the notes column never leaves the database in either projection. In an API that matters for performance and for privacy.

Practical advice

  • Prefer finder names for simple filters and @Query once a name gets longer than about five words.
  • Index the columns you filter and sort by; paging a table without an index scans it.
  • Return DTOs from controllers, not entities. Projections make that cheap.
  • Test pagination at the edges: empty table, exact multiple of page size, and a page past the end.
  • Use Slice or keyset paging for feeds, and Page with a count when users need page numbers.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does a method parameter reach a named parameter in a @Query?

    Method parameters are bound by name to :name placeholders, which also keeps the query safe from SQL injection.
  2. 2. Which return type avoids the extra COUNT query that Page performs?

    Slice only knows whether there is a next page, so no total is computed.
  3. 3. Why is OFFSET pagination slow for very deep pages?

    Reaching row 100,000 means walking past 100,000 rows. Keyset pagination seeks directly to the last seen key.
  4. 4. What is the main benefit of returning a projection record instead of the entity?

    Projections reduce data transfer and avoid exposing columns that clients should never see.

Summary

  • Use @Query for readable SQL; parameters are bound by name and never concatenated.
  • Pageable selects a zero-based slice; Page adds totals, Slice skips the count.
  • Always sort by a stable key and cap the maximum page size.
  • Offset paging slows down on deep pages; keyset paging scales better but only moves forward or backward.
  • Projections return only needed columns and keep sensitive fields private.

Next: Transactions and Database Migrations