0
# Pagination Support
1

2
Comprehensive pagination with performance optimizations, count query optimization, and type conversion support. MyBatis-Flex provides efficient pagination for large datasets with database-specific optimizations.
3

4
## Capabilities
5

6
### Page Class
7

8
Comprehensive pagination object that encapsulates pagination parameters, results, and metadata.
9

10
```java { .api }
11
/**
12
 * Comprehensive pagination object
13
 * @param <T> Record type
14
 */
15
public class Page<T> {
16
    /**
17
     * Create page with page number and size
18
     * @param pageNumber page number (1-based)
19
     * @param pageSize number of records per page
20
     */
21
    public Page(int pageNumber, int pageSize);
22
    
23
    /**
24
     * Create page with count query optimization setting
25
     * @param pageNumber page number (1-based)
26
     * @param pageSize number of records per page
27
     * @param optimizeCountQuery whether to optimize count query
28
     */
29
    public Page(int pageNumber, int pageSize, boolean optimizeCountQuery);
30
    
31
    /**
32
     * Get current page records
33
     * @return list of records for current page
34
     */
35
    public List<T> getRecords();
36
    
37
    /**
38
     * Set current page records
39
     * @param records records to set
40
     */
41
    public void setRecords(List<T> records);
42
    
43
    /**
44
     * Get total number of records
45
     * @return total record count
46
     */
47
    public long getTotalRow();
48
    
49
    /**
50
     * Set total number of records
51
     * @param totalRow total record count
52
     */
53
    public void setTotalRow(long totalRow);
54
    
55
    /**
56
     * Get total number of pages
57
     * @return total page count
58
     */
59
    public int getTotalPage();
60
    
61
    /**
62
     * Get current page number (1-based)
63
     * @return current page number
64
     */
65
    public int getPageNumber();
66
    
67
    /**
68
     * Set current page number
69
     * @param pageNumber page number to set
70
     */
71
    public void setPageNumber(int pageNumber);
72
    
73
    /**
74
     * Get page size
75
     * @return number of records per page
76
     */
77
    public int getPageSize();
78
    
79
    /**
80
     * Set page size
81
     * @param pageSize records per page
82
     */
83
    public void setPageSize(int pageSize);
84
    
85
    /**
86
     * Check if count query optimization is enabled
87
     * @return true if optimization enabled
88
     */
89
    public boolean isOptimizeCountQuery();
90
    
91
    /**
92
     * Set count query optimization
93
     * @param optimizeCountQuery optimization flag
94
     */
95
    public void setOptimizeCountQuery(boolean optimizeCountQuery);
96
    
97
    /**
98
     * Check if there is a previous page
99
     * @return true if has previous page
100
     */
101
    public boolean hasPrevious();
102
    
103
    /**
104
     * Check if there is a next page
105
     * @return true if has next page
106
     */
107
    public boolean hasNext();
108
    
109
    /**
110
     * Get offset for current page (0-based)
111
     * @return record offset
112
     */
113
    public long getOffset();
114
    
115
    /**
116
     * Convert page to different type
117
     * @param mapper function to convert records
118
     * @return new page with converted records
119
     */
120
    public <R> Page<R> convert(Function<T, R> mapper);
121
    
122
    /**
123
     * Convert page using list transformation
124
     * @param records new records list
125
     * @return new page with provided records
126
     */
127
    public <R> Page<R> convert(List<R> records);
128
}
129
```
130

131
**Basic Pagination Usage:**
132

133
```java
134
import com.mybatisflex.core.paginate.Page;
135
import com.mybatisflex.core.query.QueryWrapper;
136

137
public class BasicPaginationExample {
138
    public void demonstrateBasicPagination() {
139
        UserMapper userMapper = // ... get mapper instance
140
        
141
        // Create page parameters (page 1, 10 records per page)
142
        Page<User> page = new Page<>(1, 10);
143
        
144
        // Execute paginated query
145
        QueryWrapper query = QueryWrapper.create()
146
            .select()
147
            .from(User.class)
148
            .where(USER.ACTIVE.eq(true))
149
            .orderBy(USER.CREATE_TIME.desc());
150
        
151
        Page<User> result = userMapper.paginate(page, query);
152
        
153
        // Access pagination information
154
        System.out.println("Current page: " + result.getPageNumber());
155
        System.out.println("Page size: " + result.getPageSize());
156
        System.out.println("Total records: " + result.getTotalRow());
157
        System.out.println("Total pages: " + result.getTotalPage());
158
        System.out.println("Has next page: " + result.hasNext());
159
        System.out.println("Has previous page: " + result.hasPrevious());
160
        
161
        // Access current page records
162
        List<User> users = result.getRecords();
163
        for (User user : users) {
164
            System.out.println("User: " + user.getName());
165
        }
166
    }
167
}
168
```
169

170
### Performance Optimizations
171

172
Advanced pagination features for performance optimization in large datasets.
173

174
```java { .api }
175
/**
176
 * Performance-optimized pagination options
177
 */
178
public class Page<T> {
179
    /**
180
     * Enable/disable count query optimization
181
     * When enabled, skips total count query for better performance
182
     * @param optimizeCountQuery optimization flag
183
     */
184
    public void setOptimizeCountQuery(boolean optimizeCountQuery);
185
    
186
    /**
187
     * Set search count flag
188
     * When false, skips count query entirely
189
     * @param searchCount whether to perform count query
190
     */
191
    public void setSearchCount(boolean searchCount);
192
    
193
    /**
194
     * Check if search count is enabled
195
     * @return true if count query will be performed
196
     */
197
    public boolean isSearchCount();
198
    
199
    /**
200
     * Set custom count query SQL
201
     * @param countSql custom count SQL
202
     */
203
    public void setCountSql(String countSql);
204
    
205
    /**
206
     * Get custom count query SQL
207
     * @return custom count SQL or null
208
     */
209
    public String getCountSql();
210
}
211
```
212

213
**Performance Optimization Examples:**
214

215
```java
216
public class PaginationOptimizationExample {
217
    public void demonstrateOptimizations() {
218
        UserMapper userMapper = // ... get mapper instance
219
        
220
        // Standard pagination with count query
221
        Page<User> standardPage = new Page<>(1, 10);
222
        Page<User> standardResult = userMapper.paginate(standardPage, query);
223
        // Executes both data query and count query
224
        
225
        // Optimized pagination (skips unnecessary count operations)
226
        Page<User> optimizedPage = new Page<>(1, 10, true);
227
        Page<User> optimizedResult = userMapper.paginate(optimizedPage, query);
228
        // Optimizes count query for better performance
229
        
230
        // Skip count query entirely for performance
231
        Page<User> noCountPage = new Page<>(1, 10);
232
        noCountPage.setSearchCount(false);
233
        Page<User> noCountResult = userMapper.paginate(noCountPage, query);
234
        // Only executes data query, no count query
235
        System.out.println("Total rows: " + noCountResult.getTotalRow()); // Will be 0
236
        
237
        // Custom count query for complex scenarios
238
        Page<User> customCountPage = new Page<>(1, 10);
239
        customCountPage.setCountSql("SELECT COUNT(*) FROM users u WHERE u.active = 1");
240
        Page<User> customCountResult = userMapper.paginate(customCountPage, query);
241
        // Uses custom count SQL instead of generated one
242
    }
243
}
244
```
245

246
### Type Conversion
247

248
Convert pagination results to different types while maintaining pagination metadata.
249

250
```java { .api }
251
/**
252
 * Type conversion methods for pagination results
253
 */
254
public class Page<T> {
255
    /**
256
     * Convert page records using mapper function
257
     * @param mapper function to transform each record
258
     * @return new page with converted records
259
     */
260
    public <R> Page<R> convert(Function<T, R> mapper);
261
    
262
    /**
263
     * Convert page records using provided list
264
     * @param records new records list
265
     * @return new page with provided records  
266
     */
267
    public <R> Page<R> convert(List<R> records);
268
}
269
```
270

271
**Type Conversion Examples:**
272

273
```java
274
public class PaginationConversionExample {
275
    public void demonstrateConversion() {
276
        // Original pagination with User entities
277
        Page<User> userPage = userMapper.paginate(
278
            new Page<>(1, 10),
279
            QueryWrapper.create()
280
                .select()
281
                .from(User.class)
282
                .where(USER.ACTIVE.eq(true))
283
        );
284
        
285
        // Convert to DTO using mapper function
286
        Page<UserDTO> dtoPage = userPage.convert(user -> {
287
            UserDTO dto = new UserDTO();
288
            dto.setId(user.getId());
289
            dto.setName(user.getName());
290
            dto.setEmail(user.getEmail());
291
            dto.setDisplayName(user.getName() + " (" + user.getEmail() + ")");
292
            return dto;
293
        });
294
        
295
        // Pagination metadata is preserved
296
        System.out.println("DTO Page - Total: " + dtoPage.getTotalRow());
297
        System.out.println("DTO Page - Pages: " + dtoPage.getTotalPage());
298
        
299
        // Convert using pre-built list
300
        List<UserSummary> summaries = userPage.getRecords().stream()
301
            .map(user -> new UserSummary(user.getId(), user.getName()))
302
            .collect(Collectors.toList());
303
        
304
        Page<UserSummary> summaryPage = userPage.convert(summaries);
305
        
306
        // Method chaining with conversion
307
        Page<String> nameOnlyPage = userPage.convert(User::getName);
308
        List<String> userNames = nameOnlyPage.getRecords();
309
    }
310
}
311
```
312

313
### Service Layer Integration
314

315
Pagination support in service layer with enhanced functionality.
316

317
```java { .api }
318
/**
319
 * Service layer pagination methods
320
 */
321
public interface IService<T> {
322
    /**
323
     * Paginate all entities
324
     * @param page pagination parameters
325
     * @return page of entities
326
     */
327
    Page<T> page(Page<T> page);
328
    
329
    /**
330
     * Paginate entities with query conditions
331
     * @param page pagination parameters
332
     * @param queryWrapper query conditions
333
     * @return page of entities
334
     */
335
    Page<T> page(Page<T> page, QueryWrapper queryWrapper);
336
    
337
    /**
338
     * Paginate with type transformation
339
     * @param page pagination parameters
340
     * @param queryWrapper query conditions
341
     * @param asType target type class
342
     * @return page of transformed objects
343
     */
344
    <R> Page<R> pageAs(Page<R> page, QueryWrapper queryWrapper, Class<R> asType);
345
}
346
```
347

348
**Service Layer Pagination Examples:**
349

350
```java
351
@Service
352
public class UserServiceImpl implements UserService {
353
    
354
    public Page<UserDTO> getActiveUsers(int pageNum, int pageSize) {
355
        Page<User> page = new Page<>(pageNum, pageSize);
356
        
357
        // Service layer pagination
358
        Page<User> userPage = page(page, 
359
            QueryWrapper.create()
360
                .from(User.class)
361
                .where(USER.ACTIVE.eq(true))
362
                .orderBy(USER.CREATE_TIME.desc())
363
        );
364
        
365
        // Convert to DTO
366
        return userPage.convert(this::convertToDTO);
367
    }
368
    
369
    public Page<UserSummaryDTO> getUserSummaries(int pageNum, int pageSize, String nameFilter) {
370
        Page<UserSummaryDTO> page = new Page<>(pageNum, pageSize);
371
        
372
        QueryWrapper query = QueryWrapper.create()
373
            .select(USER.ID, USER.NAME, USER.EMAIL, USER.CREATE_TIME)
374
            .from(User.class)
375
            .where(USER.ACTIVE.eq(true));
376
            
377
        if (nameFilter != null && !nameFilter.isEmpty()) {
378
            query.and(USER.NAME.like("%" + nameFilter + "%"));
379
        }
380
        
381
        // Direct type transformation in service
382
        return pageAs(page, query, UserSummaryDTO.class);
383
    }
384
    
385
    private UserDTO convertToDTO(User user) {
386
        UserDTO dto = new UserDTO();
387
        dto.setId(user.getId());
388
        dto.setName(user.getName());
389
        dto.setEmail(user.getEmail());
390
        dto.setAge(user.getAge());
391
        dto.setCreateTime(user.getCreateTime());
392
        return dto;
393
    }
394
}
395
```
396

397
### Database-Specific Pagination
398

399
MyBatis-Flex automatically generates database-specific pagination SQL for optimal performance.
400

401
```java { .api }
402
/**
403
 * Database-specific pagination is handled automatically by dialects
404
 * No additional configuration required
405
 */
406
public class DatabaseSpecificPagination {
407
    public void demonstrateDatabaseSpecificPagination() {
408
        Page<User> page = new Page<>(2, 15); // Page 2, 15 records per page
409
        QueryWrapper query = QueryWrapper.create()
410
            .select()
411
            .from(User.class)
412
            .where(USER.ACTIVE.eq(true))
413
            .orderBy(USER.NAME.asc());
414
        
415
        // MyBatis-Flex generates database-specific SQL:
416
        
417
        // MySQL/PostgreSQL/H2:
418
        // SELECT * FROM users WHERE active = 1 ORDER BY name ASC LIMIT 15 OFFSET 15
419
        
420
        // SQL Server 2012+:
421
        // SELECT * FROM users WHERE active = 1 ORDER BY name ASC 
422
        // OFFSET 15 ROWS FETCH NEXT 15 ROWS ONLY
423
        
424
        // Oracle 12c+:
425
        // SELECT * FROM users WHERE active = 1 ORDER BY name ASC 
426
        // OFFSET 15 ROWS FETCH NEXT 15 ROWS ONLY
427
        
428
        // Oracle (traditional):
429
        // SELECT * FROM (
430
        //   SELECT ROWNUM rn, t.* FROM (
431
        //     SELECT * FROM users WHERE active = 1 ORDER BY name ASC
432
        //   ) t WHERE ROWNUM <= 30
433
        // ) WHERE rn > 15
434
        
435
        Page<User> result = userMapper.paginate(page, query);
436
    }
437
}
438
```
439

440
### Navigation Helpers
441

442
Convenient methods for pagination navigation and metadata.
443

444
```java { .api }
445
/**
446
 * Navigation and metadata helper methods
447
 */
448
public class Page<T> {
449
    /**
450
     * Check if current page is first page
451
     * @return true if first page
452
     */
453
    public boolean isFirstPage();
454
    
455
    /**
456
     * Check if current page is last page
457
     * @return true if last page
458
     */
459
    public boolean isLastPage();
460
    
461
    /**
462
     * Get previous page number (null if no previous)
463
     * @return previous page number or null
464
     */
465
    public Integer getPreviousPage();
466
    
467
    /**
468
     * Get next page number (null if no next)
469
     * @return next page number or null
470
     */
471
    public Integer getNextPage();
472
    
473
    /**
474
     * Calculate page numbers for pagination UI
475
     * @param displayCount number of page links to display
476
     * @return list of page numbers for UI
477
     */
478
    public List<Integer> getPageNumbers(int displayCount);
479
    
480
    /**
481
     * Get start record number (1-based)
482
     * @return start record number
483
     */
484
    public long getStartRow();
485
    
486
    /**
487
     * Get end record number (1-based)  
488
     * @return end record number
489
     */
490
    public long getEndRow();
491
}
492
```
493

494
**Navigation Examples:**
495

496
```java
497
public class PaginationNavigationExample {
498
    public void demonstrateNavigation() {
499
        Page<User> result = userMapper.paginate(new Page<>(3, 10), query);
500
        
501
        // Navigation checks
502
        System.out.println("Is first page: " + result.isFirstPage()); // false
503
        System.out.println("Is last page: " + result.isLastPage()); // depends on data
504
        System.out.println("Has previous: " + result.hasPrevious()); // true
505
        System.out.println("Has next: " + result.hasNext()); // depends on data
506
        
507
        // Page numbers
508
        Integer prevPage = result.getPreviousPage(); // 2
509
        Integer nextPage = result.getNextPage(); // 4 or null
510
        
511
        // UI pagination helper
512
        List<Integer> pageNumbers = result.getPageNumbers(5);
513
        // For page 3: [1, 2, 3, 4, 5] or similar range
514
        
515
        // Record range
516
        System.out.println("Showing records " + result.getStartRow() + 
517
                          " to " + result.getEndRow() + 
518
                          " of " + result.getTotalRow());
519
        // Output: "Showing records 21 to 30 of 157"
520
    }
521
}
522
```
523

524
## Types
525

526
```java { .api }
527
// Core pagination types
528
public class Page<T> {
529
    public Page(int pageNumber, int pageSize);
530
    public Page(int pageNumber, int pageSize, boolean optimizeCountQuery);
531
    
532
    public List<T> getRecords();
533
    public long getTotalRow();
534
    public int getTotalPage();
535
    public int getPageNumber();
536
    public int getPageSize();
537
    
538
    public <R> Page<R> convert(Function<T, R> mapper);
539
}
540

541
// Functional interface for conversion
542
@FunctionalInterface
543
public interface Function<T, R> {
544
    R apply(T t);
545
}
546

547
// Standard Java collections
548
public interface List<E> extends Collection<E> {
549
    E get(int index);
550
    int size();
551
    boolean isEmpty();
552
}
553

554
// Optional for navigation
555
public interface Optional<T> {
556
    boolean isPresent();
557
    T get();
558
    T orElse(T other);
559
}
560
```