CtrlK
BlogDocsLog inGet started
Tessl Logo

tessl/maven-io-quarkus--quarkus-hibernate-orm-panache

Simplify your persistence code for Hibernate ORM via the active record or the repository pattern

—

Pending
Overview
Eval results
Files

query-pagination.mddocs/

Query Building and Pagination

Advanced query building capabilities including pagination, sorting, filtering, and result processing through the PanacheQuery interface. The PanacheQuery interface provides a fluent API for building complex queries with pagination, sorting, locking, and result processing.

Capabilities

PanacheQuery Interface

The core query interface that enables fluent query building and result processing.

public interface PanacheQuery<Entity> {
    // Projection
    public <T> PanacheQuery<T> project(Class<T> type);
    
    // Pagination
    public <T extends Entity> PanacheQuery<T> page(Page page);
    public <T extends Entity> PanacheQuery<T> page(int pageIndex, int pageSize);
    public <T extends Entity> PanacheQuery<T> nextPage();
    public <T extends Entity> PanacheQuery<T> previousPage();
    public <T extends Entity> PanacheQuery<T> firstPage();
    public <T extends Entity> PanacheQuery<T> lastPage();
    
    // Range-based queries
    public <T extends Entity> PanacheQuery<T> range(int startIndex, int lastIndex);
    
    // Query configuration
    public <T extends Entity> PanacheQuery<T> withLock(LockModeType lockModeType);
    public <T extends Entity> PanacheQuery<T> withHint(String hintName, Object value);
    
    // Hibernate filters
    public <T extends Entity> PanacheQuery<T> filter(String filterName);
    public <T extends Entity> PanacheQuery<T> filter(String filterName, Parameters parameters);
    public <T extends Entity> PanacheQuery<T> filter(String filterName, Map<String, Object> parameters);
    
    // Result retrieval
    public long count();
    public <T extends Entity> List<T> list();
    public <T extends Entity> Stream<T> stream();
    public <T extends Entity> T firstResult();
    public <T extends Entity> Optional<T> firstResultOptional();
    public <T extends Entity> T singleResult();
    public <T extends Entity> Optional<T> singleResultOptional();
    
    // Pagination info
    public boolean hasNextPage();
    public boolean hasPreviousPage();
    public int pageCount();
    public Page page();
}

Query Projection

Transform query results into different types using projection classes.

public <T> PanacheQuery<T> project(Class<T> type);

Usage:

// Projection class
public class PersonSummary {
    public String name;
    public String email;
    
    public PersonSummary(String name, String email) {
        this.name = name;
        this.email = email;
    }
}

// Using projection with explicit select
List<PersonSummary> summaries = Person.find("select name, email from Person where status = ?1", PersonStatus.Active)
    .project(PersonSummary.class)
    .list();

// Automatic projection based on constructor
List<PersonSummary> autoProjected = Person.find("status = ?1", PersonStatus.Active)
    .project(PersonSummary.class)
    .list();

Page-Based Pagination

Navigate through result sets using page-based pagination.

// Set pagination
public <T extends Entity> PanacheQuery<T> page(Page page);
public <T extends Entity> PanacheQuery<T> page(int pageIndex, int pageSize);

// Navigate pages
public <T extends Entity> PanacheQuery<T> nextPage();
public <T extends Entity> PanacheQuery<T> previousPage();
public <T extends Entity> PanacheQuery<T> firstPage();
public <T extends Entity> PanacheQuery<T> lastPage();

// Page information
public boolean hasNextPage();
public boolean hasPreviousPage();
public int pageCount();
public Page page();

Usage:

// Basic pagination
PanacheQuery<Person> query = Person.find("status", PersonStatus.Active);
List<Person> page1 = query.page(0, 20).list(); // First 20 results
List<Person> page2 = query.page(1, 20).list(); // Next 20 results

// Using Page object
Page page = Page.of(0, 10);
List<Person> results = Person.findAll().page(page).list();

// Navigation
PanacheQuery<Person> paginatedQuery = Person.findAll().page(0, 10);
if (paginatedQuery.hasNextPage()) {
    List<Person> nextPage = paginatedQuery.nextPage().list();
}

// Page information
PanacheQuery<Person> activeQuery = Person.find("status", PersonStatus.Active).page(0, 10);
long totalCount = activeQuery.count(); // Total matching entities
int totalPages = activeQuery.pageCount(); // Total pages
Page currentPage = activeQuery.page(); // Current page info

// Jump to specific pages
List<Person> firstPage = activeQuery.firstPage().list();
List<Person> lastPage = activeQuery.lastPage().list();

Range-Based Queries

Use fixed ranges instead of page-based pagination for specific result windows.

public <T extends Entity> PanacheQuery<T> range(int startIndex, int lastIndex);

Usage:

// Get specific range of results
List<Person> results = Person.findAll(Sort.by("name"))
    .range(10, 19) // Get results 10-19 (10 results starting from index 10)
    .list();

// Get first 5 results
List<Person> top5 = Person.find("status", PersonStatus.Active)
    .range(0, 4)
    .list();

// Note: Range-based queries disable further pagination operations
PanacheQuery<Person> rangeQuery = Person.findAll().range(0, 9);
// rangeQuery.nextPage(); // This would throw UnsupportedOperationException

Query Locking

Apply database locking strategies to queries.

public <T extends Entity> PanacheQuery<T> withLock(LockModeType lockModeType);

Usage:

import jakarta.persistence.LockModeType;

// Pessimistic locking
@Transactional
public Person updatePersonWithLock(Long id) {
    Person person = Person.find("id", id)
        .withLock(LockModeType.PESSIMISTIC_WRITE)
        .firstResult();
    
    if (person != null) {
        person.status = PersonStatus.Updated;
        // Entity is locked until transaction completes
    }
    return person;
}

// Optimistic locking
List<Person> lockedPersons = Person.find("status", PersonStatus.Active)
    .withLock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)
    .list();

Query Hints

Set JPA query hints for performance tuning.

public <T extends Entity> PanacheQuery<T> withHint(String hintName, Object value);

Usage:

// Fetch size hint
List<Person> persons = Person.findAll()
    .withHint("org.hibernate.fetchSize", 50)
    .withHint("org.hibernate.readOnly", true)
    .list();

// Cache hints
List<Person> cachedPersons = Person.find("status", PersonStatus.Active)
    .withHint("org.hibernate.cacheable", true)
    .withHint("org.hibernate.cacheRegion", "person-region")
    .list();

Hibernate Filters

Enable and configure Hibernate filters for dynamic query constraints.

public <T extends Entity> PanacheQuery<T> filter(String filterName);
public <T extends Entity> PanacheQuery<T> filter(String filterName, Parameters parameters);
public <T extends Entity> PanacheQuery<T> filter(String filterName, Map<String, Object> parameters);

Usage:

// Filter definition on entity
@Entity
@FilterDef(name = "statusFilter", parameters = @ParamDef(name = "status", type = "string"))
@Filter(name = "statusFilter", condition = "status = :status")
public class Person extends PanacheEntity {
    public String name;
    public PersonStatus status;
}

// Using filters in queries
List<Person> activePersons = Person.findAll()
    .filter("statusFilter", Parameters.with("status", "ACTIVE"))
    .list();

// Multiple filters
List<Person> filteredPersons = Person.findAll()
    .filter("statusFilter", Parameters.with("status", "ACTIVE"))
    .filter("cityFilter", Map.of("city", "NYC"))
    .list();

Result Retrieval Methods

Various methods for obtaining query results in different formats.

// Count results
public long count();

// Get as List
public <T extends Entity> List<T> list();

// Get as Stream (requires transaction)
public <T extends Entity> Stream<T> stream();

// Get single results
public <T extends Entity> T firstResult();
public <T extends Entity> Optional<T> firstResultOptional();
public <T extends Entity> T singleResult();
public <T extends Entity> Optional<T> singleResultOptional();

Usage:

PanacheQuery<Person> query = Person.find("status", PersonStatus.Active);

// Count without loading entities
long totalActive = query.count();

// Get as list
List<Person> activePersons = query.list();

// Get as stream (requires transaction)
@Transactional
public void processActivePersons() {
    Person.find("status", PersonStatus.Active)
        .stream()
        .forEach(this::processActivePerson);
}

// Single result methods
Person firstPerson = Person.find("status", PersonStatus.Active)
    .page(0, 1)
    .firstResult(); // Returns null if no results

Optional<Person> maybePerson = Person.find("email", "john@example.com")
    .firstResultOptional(); // Returns Optional

// Single result (expects exactly one)
try {
    Person uniquePerson = Person.find("email", "unique@example.com")
        .singleResult(); // Throws if 0 or >1 results
} catch (NoResultException | NonUniqueResultException e) {
    // Handle error
}

// Safe single result
Optional<Person> singlePerson = Person.find("id", 1L)
    .singleResultOptional(); // Returns Optional, throws if >1 result

Complex Query Building Examples

Combining multiple query features for sophisticated data retrieval.

// Complex paginated query with sorting and filtering
public class PersonService {
    
    public PagedResult<PersonSummary> searchPersons(
            String namePattern, 
            PersonStatus status, 
            String city,
            int page, 
            int size) {
        
        PanacheQuery<Person> query = Person.find(
            "name ILIKE :name AND status = :status AND city = :city",
            Parameters.with("name", "%" + namePattern + "%")
                     .and("status", status)
                     .and("city", city)
        );
        
        // Get total count for pagination info
        long totalCount = query.count();
        
        // Get paginated results with projection
        List<PersonSummary> results = query
            .page(page, size)
            .project(PersonSummary.class)
            .list();
        
        return new PagedResult<>(results, page, size, totalCount);
    }
    
    @Transactional
    public void bulkProcessPersons(PersonStatus status, int batchSize) {
        PanacheQuery<Person> query = Person.find("status", status);
        
        int totalPages = query.page(0, batchSize).pageCount();
        
        for (int page = 0; page < totalPages; page++) {
            List<Person> batch = query.page(page, batchSize).list();
            processBatch(batch);
        }
    }
    
    @Transactional
    public Optional<Person> findAndLockPerson(String email) {
        return Person.find("email", email)
            .withLock(LockModeType.PESSIMISTIC_WRITE)
            .withHint("jakarta.persistence.lock.timeout", 5000L)
            .firstResultOptional();
    }
}

// Supporting classes
public class PagedResult<T> {
    public final List<T> content;
    public final int page;
    public final int size;
    public final long totalElements;
    public final int totalPages;
    
    public PagedResult(List<T> content, int page, int size, long totalElements) {
        this.content = content;
        this.page = page;
        this.size = size;
        this.totalElements = totalElements;
        this.totalPages = (int) Math.ceil((double) totalElements / size);
    }
}

Performance Considerations

Tips for optimal query performance with PanacheQuery:

// Use count() before loading large result sets
PanacheQuery<Person> query = Person.find("status", PersonStatus.Active);
long count = query.count();
if (count > 1000) {
    // Consider pagination or filtering
    List<Person> page = query.page(0, 100).list();
} else {
    List<Person> all = query.list();
}

// Use streaming for large datasets
@Transactional
public void exportPersons() {
    Person.findAll()
        .withHint("org.hibernate.fetchSize", 100)
        .stream()
        .forEach(this::exportPerson);
}

// Prefer list() over firstResult() when you need null safety
Optional<Person> person = Person.find("email", email)
    .firstResultOptional(); // Better than firstResult() for null safety

// Use projection for read-only queries
List<String> names = Person.find("status", PersonStatus.Active)
    .project(PersonNameProjection.class)
    .list();

Install with Tessl CLI

npx tessl i tessl/maven-io-quarkus--quarkus-hibernate-orm-panache

docs

active-record.md

index.md

query-pagination.md

repository-pattern.md

utilities.md

tile.json