Queries: search, sorting, pages

listAll() returns the whole table, but the blog needs more: search posts by title, show the newest first, return the list page by page. Panache accepts queries in HQL — SQL in which you write classes and fields instead of tables and columns. Simple cases need only a short form — a simplified query.

Simplified queries

The list methods return a List at once, while find returns a query object you can add paging to. Values are always passed as parameters (?1, :t), never glued into the string: that way there is no risk of SQL injection.

Queries in the entity class

In the active record style your own queries become static methods of the entity:

// src/main/java/com/example/blog/Post.java
@Entity
public class Post extends PanacheEntity {
 
    // ... the title, body and createdAt fields
 
    public static List<Post> latest(int page, int size) {
        return findAll(Sort.by("createdAt").descending())
                .page(Page.of(page, size))
                .list();
    }
 
    public static List<Post> search(String text) {
        return list("lower(title) like ?1", Sort.by("title"), "%" + text.toLowerCase() + "%");
    }
 
    public static boolean titleExists(String title) {
        return count("title", title) > 0;
    }
}

The imports are io.quarkus.panache.common.Sort, io.quarkus.panache.common.Page and java.util.List. Sort.by("createdAt").descending() sorts from newest to oldest. Page.of(page, size) takes page number page (counting from zero) with size records. lower(title) makes the search case-insensitive.

A resource with pages and search

The page size comes from BlogConfig, the blog.page-size setting:

// src/main/java/com/example/blog/PostResource.java
@Inject
BlogConfig config;
 
@GET
public List<Post> list(@QueryParam("search") String search,
                       @QueryParam("page") @DefaultValue("0") int page) {
    if (search != null) {
        return Post.search(search);
    }
    return Post.latest(page, config.pageSize());
}
 
@GET
@Path("/count")
public long count() {
    return Post.count();
}

The /count path is a separate endpoint with the total number of posts. Quarkus does not confuse it with /{id}: an exact path match beats a template.

Checking

Set blog.page-size=2 in application.properties, create two more posts with POST /posts and request the pages:

curl "http://localhost:8080/posts?page=0"
curl "http://localhost:8080/posts?page=1"
curl "http://localhost:8080/posts?search=DEV"
curl http://localhost:8080/posts/count

The output is shortened to the titles:

"title":"Part 2" "title":"Part 1"
"title":"Dev mode" "title":"Hello, Quarkus"
[{"id":2,"title":"Dev mode", ...}]
4

Page zero has the two new posts, page one the two old ones. The search found the post even though the query was in upper case. Set blog.page-size=10 back.

When a simplified query is not enough

If the string does not start with select, from or order by, Panache adds from Post where itself. You can pass a full query too: Post.find("from Post p where p.createdAt > ?1", date). For complex reports there is the plain EntityManager from Jakarta Persistence — inject it with @Inject.

Common mistake

Queries use the names of class fields, not table columns. If you misspell a name, for example Post.list("titel", "x"), the client gets 500, and the log says:

ERROR [io.quarkus.vertx.http.runtime.QuarkusErrorHandler] HTTP Request to /posts failed, error id: ...: org.hibernate.query.SemanticException: Could not interpret path expression 'titel'

The error appears only when the query runs, not at startup, because the query is a plain string. Check the names in a query against the entity fields.