Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 36 ยท Data with Panache

Panache Repositories and Queries

Use PanacheRepository in Quarkus, write short HQL queries with positional and named parameters, sort, page results, count, update and delete in bulk.

The repository style keeps your entity a plain class and puts all queries into a separate CDI bean. It is the style most Java developers know from Spring Data or DAO classes, and it is simple to inject, test and mock. In both styles Panache gives you the same short query language, so this lesson is also the query reference for active record entities.

The Panache code is read-only here; the stand-ins model the query shortening rules and the paging arithmetic in plain Java.

A repository

Java
import io.quarkus.hibernate.orm.panache.PanacheRepository;
import jakarta.enterprise.context.ApplicationScoped;

@Entity
public class Book {
    @Id @GeneratedValue
    public Long id;
    public String title;
    public String author;
    public int year;
}

@ApplicationScoped
public class BookRepository implements PanacheRepository<Book> {

    public List<Book> byAuthor(String author) {
        return list("author", author);
    }

    public List<Book> publishedAfter(int year) {
        return list("year > ?1 order by year", year);
    }
}

PanacheRepository<Book> uses Long ids. PanacheRepositoryBase<Book, UUID> supports another id type. Inject it like any bean:

Java
@Path("/books")
public class BookResource {

    @Inject
    BookRepository books;

    @GET
    public List<Book> byAuthor(@RestQuery String author) {
        return author == null ? books.listAll() : books.byAuthor(author);
    }
}

The inherited methods are the same as in active record: persist(entity), findById(id), listAll(), find(...), list(...), stream(...), count(...), delete(...), deleteById(id), deleteAll() and update(...).

Short queries

You write only the part after the entity name. Panache completes the query:

You write Panache builds
find("author", "Herbert") FROM Book WHERE author = ?1
find("author = ?1 and year > ?2", a, y) FROM Book WHERE author = ?1 and year > ?2
find("order by year") FROM Book order by year
find("from Book where year < 1900") used as is
find("select b.title from Book b") used as is (full HQL)

Parameters can be positional (?1, ?2) or named (:author):

Java
import io.quarkus.panache.common.Parameters;

books.list("author = :a and year >= :y",
           Parameters.with("a", "Herbert").and("y", 1960));

Always pass user input as parameters. Never build a query by joining strings: that opens the door to injection.

For a map or Parameters object you get named binding. With a Map<String, Object> you can build the filter dynamically.

Sorting

Add a Sort argument or an order by clause:

Java
import io.quarkus.panache.common.Sort;

books.list("author", Sort.by("year").descending().and("title"), "Herbert");
books.listAll(Sort.by("title"));

Sort.by("year", Sort.Direction.Descending) also works. Sorting by columns chosen by the caller must be checked against an allowed list, otherwise callers can sort by any field.

Paging

Never load a whole large table. find(...) returns a PanacheQuery, which pages the result:

Java
import io.quarkus.hibernate.orm.panache.PanacheQuery;
import io.quarkus.panache.common.Page;

PanacheQuery<Book> query = books.find("year > ?1", Sort.by("title"), 1900);
query.page(Page.of(2, 20));          // zero-based page index 2, 20 rows each

List<Book> rows = query.list();      // rows 41 to 60 of the match
long total = query.count();          // all matches
int pages = query.pageCount();
boolean more = query.hasNextPage();

Page indexes start at 0, so Page.of(2, 20) is the third page: rows 41 to 60. This stand-in does the same arithmetic and lets you check the numbers:

Java Stand-in: paging arithmetic

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.

For an API, return the total and page info too (for example in headers or a wrapper record), so clients can draw page controls. For huge or constantly changing data, keyset paging (where id > :lastId order by id limit 20) is faster than large offsets.

How a short query is completed

Panache looks at the string. This stand-in applies the same rules in a simplified form so you can see why "author" alone works and why a full query is left alone.

Java Stand-in: completing a short query

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.

Bulk updates, deletes and counting

Java
long old = books.count("year < ?1", 1900);

@Transactional
public int rename(String from, String to) {
    return books.update("author = ?1 where author = ?2", to, from);
}

@Transactional
public long removeOld() {
    return books.delete("year < ?1", 1800);   // number of rows deleted
}

Bulk update and delete run as a single SQL statement. They bypass the persistence context, so entities already loaded in the same transaction may be out of date. Use them for housekeeping, not for ordinary edits (which dirty checking handles better).

Choosing between find and list

  • find(...) returns a PanacheQuery: use it when you page, count or take firstResult() / singleResult() / firstResultOptional().
  • list(...) is find(...).list(): a plain List.
  • stream(...) returns a Stream that must be closed and used inside a transaction.

A query, step by step

Write short author = ?1
Panache completes Adds FROM Book WHERE
Bind Parameters, never string joins.
Page Fetch only what you show.

Short queries are still HQL.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does find("author", "Herbert") build?

    A query that is just a field name is expanded to an equality filter on that field.
  2. 2. What is the index of the first page in Panache's Page.of(index, size)?

    Page indexes are zero-based, so Page.of(0, 20) is the first page.
  3. 3. Why pass user input as query parameters instead of concatenating strings?

    Bound parameters are never interpreted as query syntax.
  4. 4. What is a caveat of bulk update and delete in Panache?

    They run as one SQL statement and do not update entities already managed in the session.

Summary

  • Implement PanacheRepository<T> in an @ApplicationScoped bean; the active record entity offers the same methods statically.
  • Short queries such as "author = ?1 and year > ?2" are completed to HQL; use positional or named parameters.
  • Sort orders results; find(...) plus Page.of(index, size) pages them (index starts at 0).
  • Bulk update(...) and delete(...) are single statements that bypass the persistence context.

Next: Relationships and DTOs