0
# Query Building and Pagination
1

2
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.
3

4
## Capabilities
5

6
### PanacheQuery Interface
7

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

10
```java { .api }
11
public interface PanacheQuery<Entity> {
12
    // Projection
13
    public <T> PanacheQuery<T> project(Class<T> type);
14
    
15
    // Pagination
16
    public <T extends Entity> PanacheQuery<T> page(Page page);
17
    public <T extends Entity> PanacheQuery<T> page(int pageIndex, int pageSize);
18
    public <T extends Entity> PanacheQuery<T> nextPage();
19
    public <T extends Entity> PanacheQuery<T> previousPage();
20
    public <T extends Entity> PanacheQuery<T> firstPage();
21
    public <T extends Entity> PanacheQuery<T> lastPage();
22
    
23
    // Range-based queries
24
    public <T extends Entity> PanacheQuery<T> range(int startIndex, int lastIndex);
25
    
26
    // Query configuration
27
    public <T extends Entity> PanacheQuery<T> withLock(LockModeType lockModeType);
28
    public <T extends Entity> PanacheQuery<T> withHint(String hintName, Object value);
29
    
30
    // Hibernate filters
31
    public <T extends Entity> PanacheQuery<T> filter(String filterName);
32
    public <T extends Entity> PanacheQuery<T> filter(String filterName, Parameters parameters);
33
    public <T extends Entity> PanacheQuery<T> filter(String filterName, Map<String, Object> parameters);
34
    
35
    // Result retrieval
36
    public long count();
37
    public <T extends Entity> List<T> list();
38
    public <T extends Entity> Stream<T> stream();
39
    public <T extends Entity> T firstResult();
40
    public <T extends Entity> Optional<T> firstResultOptional();
41
    public <T extends Entity> T singleResult();
42
    public <T extends Entity> Optional<T> singleResultOptional();
43
    
44
    // Pagination info
45
    public boolean hasNextPage();
46
    public boolean hasPreviousPage();
47
    public int pageCount();
48
    public Page page();
49
}
50
```
51

52
### Query Projection
53

54
Transform query results into different types using projection classes.
55

56
```java { .api }
57
public <T> PanacheQuery<T> project(Class<T> type);
58
```
59

60
**Usage:**
61
```java
62
// Projection class
63
public class PersonSummary {
64
    public String name;
65
    public String email;
66
    
67
    public PersonSummary(String name, String email) {
68
        this.name = name;
69
        this.email = email;
70
    }
71
}
72

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

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

84
### Page-Based Pagination
85

86
Navigate through result sets using page-based pagination.
87

88
```java { .api }
89
// Set pagination
90
public <T extends Entity> PanacheQuery<T> page(Page page);
91
public <T extends Entity> PanacheQuery<T> page(int pageIndex, int pageSize);
92

93
// Navigate pages
94
public <T extends Entity> PanacheQuery<T> nextPage();
95
public <T extends Entity> PanacheQuery<T> previousPage();
96
public <T extends Entity> PanacheQuery<T> firstPage();
97
public <T extends Entity> PanacheQuery<T> lastPage();
98

99
// Page information
100
public boolean hasNextPage();
101
public boolean hasPreviousPage();
102
public int pageCount();
103
public Page page();
104
```
105

106
**Usage:**
107
```java
108
// Basic pagination
109
PanacheQuery<Person> query = Person.find("status", PersonStatus.Active);
110
List<Person> page1 = query.page(0, 20).list(); // First 20 results
111
List<Person> page2 = query.page(1, 20).list(); // Next 20 results
112

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

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

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

129
// Jump to specific pages
130
List<Person> firstPage = activeQuery.firstPage().list();
131
List<Person> lastPage = activeQuery.lastPage().list();
132
```
133

134
### Range-Based Queries
135

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

138
```java { .api }
139
public <T extends Entity> PanacheQuery<T> range(int startIndex, int lastIndex);
140
```
141

142
**Usage:**
143
```java
144
// Get specific range of results
145
List<Person> results = Person.findAll(Sort.by("name"))
146
    .range(10, 19) // Get results 10-19 (10 results starting from index 10)
147
    .list();
148

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

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

159
### Query Locking
160

161
Apply database locking strategies to queries.
162

163
```java { .api }
164
public <T extends Entity> PanacheQuery<T> withLock(LockModeType lockModeType);
165
```
166

167
**Usage:**
168
```java
169
import jakarta.persistence.LockModeType;
170

171
// Pessimistic locking
172
@Transactional
173
public Person updatePersonWithLock(Long id) {
174
    Person person = Person.find("id", id)
175
        .withLock(LockModeType.PESSIMISTIC_WRITE)
176
        .firstResult();
177
    
178
    if (person != null) {
179
        person.status = PersonStatus.Updated;
180
        // Entity is locked until transaction completes
181
    }
182
    return person;
183
}
184

185
// Optimistic locking
186
List<Person> lockedPersons = Person.find("status", PersonStatus.Active)
187
    .withLock(LockModeType.OPTIMISTIC_FORCE_INCREMENT)
188
    .list();
189
```
190

191
### Query Hints
192

193
Set JPA query hints for performance tuning.
194

195
```java { .api }
196
public <T extends Entity> PanacheQuery<T> withHint(String hintName, Object value);
197
```
198

199
**Usage:**
200
```java
201
// Fetch size hint
202
List<Person> persons = Person.findAll()
203
    .withHint("org.hibernate.fetchSize", 50)
204
    .withHint("org.hibernate.readOnly", true)
205
    .list();
206

207
// Cache hints
208
List<Person> cachedPersons = Person.find("status", PersonStatus.Active)
209
    .withHint("org.hibernate.cacheable", true)
210
    .withHint("org.hibernate.cacheRegion", "person-region")
211
    .list();
212
```
213

214
### Hibernate Filters
215

216
Enable and configure Hibernate filters for dynamic query constraints.
217

218
```java { .api }
219
public <T extends Entity> PanacheQuery<T> filter(String filterName);
220
public <T extends Entity> PanacheQuery<T> filter(String filterName, Parameters parameters);
221
public <T extends Entity> PanacheQuery<T> filter(String filterName, Map<String, Object> parameters);
222
```
223

224
**Usage:**
225
```java
226
// Filter definition on entity
227
@Entity
228
@FilterDef(name = "statusFilter", parameters = @ParamDef(name = "status", type = "string"))
229
@Filter(name = "statusFilter", condition = "status = :status")
230
public class Person extends PanacheEntity {
231
    public String name;
232
    public PersonStatus status;
233
}
234

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

240
// Multiple filters
241
List<Person> filteredPersons = Person.findAll()
242
    .filter("statusFilter", Parameters.with("status", "ACTIVE"))
243
    .filter("cityFilter", Map.of("city", "NYC"))
244
    .list();
245
```
246

247
### Result Retrieval Methods
248

249
Various methods for obtaining query results in different formats.
250

251
```java { .api }
252
// Count results
253
public long count();
254

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

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

261
// Get single results
262
public <T extends Entity> T firstResult();
263
public <T extends Entity> Optional<T> firstResultOptional();
264
public <T extends Entity> T singleResult();
265
public <T extends Entity> Optional<T> singleResultOptional();
266
```
267

268
**Usage:**
269
```java
270
PanacheQuery<Person> query = Person.find("status", PersonStatus.Active);
271

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

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

278
// Get as stream (requires transaction)
279
@Transactional
280
public void processActivePersons() {
281
    Person.find("status", PersonStatus.Active)
282
        .stream()
283
        .forEach(this::processActivePerson);
284
}
285

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

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

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

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

307
### Complex Query Building Examples
308

309
Combining multiple query features for sophisticated data retrieval.
310

311
```java
312
// Complex paginated query with sorting and filtering
313
public class PersonService {
314
    
315
    public PagedResult<PersonSummary> searchPersons(
316
            String namePattern, 
317
            PersonStatus status, 
318
            String city,
319
            int page, 
320
            int size) {
321
        
322
        PanacheQuery<Person> query = Person.find(
323
            "name ILIKE :name AND status = :status AND city = :city",
324
            Parameters.with("name", "%" + namePattern + "%")
325
                     .and("status", status)
326
                     .and("city", city)
327
        );
328
        
329
        // Get total count for pagination info
330
        long totalCount = query.count();
331
        
332
        // Get paginated results with projection
333
        List<PersonSummary> results = query
334
            .page(page, size)
335
            .project(PersonSummary.class)
336
            .list();
337
        
338
        return new PagedResult<>(results, page, size, totalCount);
339
    }
340
    
341
    @Transactional
342
    public void bulkProcessPersons(PersonStatus status, int batchSize) {
343
        PanacheQuery<Person> query = Person.find("status", status);
344
        
345
        int totalPages = query.page(0, batchSize).pageCount();
346
        
347
        for (int page = 0; page < totalPages; page++) {
348
            List<Person> batch = query.page(page, batchSize).list();
349
            processBatch(batch);
350
        }
351
    }
352
    
353
    @Transactional
354
    public Optional<Person> findAndLockPerson(String email) {
355
        return Person.find("email", email)
356
            .withLock(LockModeType.PESSIMISTIC_WRITE)
357
            .withHint("jakarta.persistence.lock.timeout", 5000L)
358
            .firstResultOptional();
359
    }
360
}
361

362
// Supporting classes
363
public class PagedResult<T> {
364
    public final List<T> content;
365
    public final int page;
366
    public final int size;
367
    public final long totalElements;
368
    public final int totalPages;
369
    
370
    public PagedResult(List<T> content, int page, int size, long totalElements) {
371
        this.content = content;
372
        this.page = page;
373
        this.size = size;
374
        this.totalElements = totalElements;
375
        this.totalPages = (int) Math.ceil((double) totalElements / size);
376
    }
377
}
378
```
379

380
### Performance Considerations
381

382
Tips for optimal query performance with PanacheQuery:
383

384
```java
385
// Use count() before loading large result sets
386
PanacheQuery<Person> query = Person.find("status", PersonStatus.Active);
387
long count = query.count();
388
if (count > 1000) {
389
    // Consider pagination or filtering
390
    List<Person> page = query.page(0, 100).list();
391
} else {
392
    List<Person> all = query.list();
393
}
394

395
// Use streaming for large datasets
396
@Transactional
397
public void exportPersons() {
398
    Person.findAll()
399
        .withHint("org.hibernate.fetchSize", 100)
400
        .stream()
401
        .forEach(this::exportPerson);
402
}
403

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

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