0
# Pagination
1

2
MyBatis-Plus Extension provides comprehensive pagination support through the `Page` class and `PaginationInnerInterceptor`. The pagination system automatically handles count queries, limit/offset generation, and database-specific SQL dialect differences.
3

4
## Core Components
5

6
### Page Class
7

8
The main pagination container that implements `IPage<T>`.
9

10
```java { .api }
11
public class Page<T> implements IPage<T> {
12
    private static final long serialVersionUID = 8545996863226528798L;
13
    
14
    // Core pagination fields
15
    private List<T> records = Collections.emptyList();
16
    private long total = 0;
17
    private long size = 10;
18
    private long current = 1;
19
    private List<OrderItem> orders = new ArrayList<>();
20
    private boolean optimizeCountSql = true;
21
    private boolean optimizeJoinOfCountSql = true;
22
    private boolean searchCount = true;
23
    private boolean hitCount = false;
24
    private String countId;
25
    private Long maxLimit;
26
    
27
    // Constructors
28
    public Page();
29
    public Page(long current, long size);
30
    public Page(long current, long size, long total);
31
    public Page(long current, long size, boolean searchCount);
32
    public Page(long current, long size, long total, boolean searchCount);
33
    
34
    // Static factory methods
35
    public static <T> Page<T> of(long current, long size);
36
    public static <T> Page<T> of(long current, long size, long total);
37
    public static <T> Page<T> of(long current, long size, boolean searchCount);
38
    
39
    // Data access methods
40
    public List<T> getRecords();
41
    public Page<T> setRecords(List<T> records);
42
    public long getTotal();
43
    public Page<T> setTotal(long total);
44
    public long getSize();
45
    public Page<T> setSize(long size);
46
    public long getCurrent();
47
    public Page<T> setCurrent(long current);
48
    
49
    // Navigation methods
50
    public long getPages();
51
    public long offset();
52
    public boolean hasPrevious();
53
    public boolean hasNext();
54
    
55
    // Ordering methods
56
    public List<OrderItem> orders();
57
    public Page<T> addOrder(OrderItem... items);
58
    public Page<T> addOrder(List<OrderItem> items);
59
    
60
    // Configuration methods
61
    public boolean optimizeCountSql();
62
    public Page<T> setOptimizeCountSql(boolean optimizeCountSql);
63
    public boolean optimizeJoinOfCountSql();
64
    public Page<T> setOptimizeJoinOfCountSql(boolean optimizeJoinOfCountSql);
65
    public boolean searchCount();
66
    public Page<T> setSearchCount(boolean searchCount);
67
    public Long maxLimit();
68
    public Page<T> setMaxLimit(Long maxLimit);
69
}
70
```
71

72
### IPage Interface
73

74
The base pagination interface.
75

76
```java { .api }
77
public interface IPage<T> extends Serializable {
78
    List<T> getRecords();
79
    IPage<T> setRecords(List<T> records);
80
    long getTotal();
81
    IPage<T> setTotal(long total);
82
    long getSize();
83
    IPage<T> setSize(long size);
84
    long getCurrent();
85
    IPage<T> setCurrent(long current);
86
    
87
    // Computed properties
88
    default long getPages() {
89
        if (getSize() == 0) {
90
            return 0L;
91
        }
92
        long pages = getTotal() / getSize();
93
        if (getTotal() % getSize() != 0) {
94
            pages++;
95
        }
96
        return pages;
97
    }
98
    
99
    default long offset() {
100
        return getCurrent() > 0 ? (getCurrent() - 1) * getSize() : 0;
101
    }
102
    
103
    default boolean hasPrevious() {
104
        return getCurrent() > 1;
105
    }
106
    
107
    default boolean hasNext() {
108
        return getCurrent() < getPages();
109
    }
110
}
111
```
112

113
### OrderItem Class
114

115
Represents ordering criteria for pagination queries.
116

117
```java { .api }
118
public class OrderItem implements Serializable {
119
    private String column;
120
    private boolean asc = true;
121
    
122
    public OrderItem();
123
    public OrderItem(String column, boolean asc);
124
    
125
    // Static factory methods
126
    public static OrderItem asc(String column);
127
    public static OrderItem desc(String column);
128
    public static List<OrderItem> ascs(String... columns);
129
    public static List<OrderItem> descs(String... columns);
130
    
131
    // Getters and setters
132
    public String getColumn();
133
    public void setColumn(String column);
134
    public boolean isAsc();
135
    public void setAsc(boolean asc);
136
}
137
```
138

139
### PageDTO Class
140

141
Data Transfer Object for pagination parameters.
142

143
```java { .api }
144
public class PageDTO<T> implements IPage<T> {
145
    private List<T> records = Collections.emptyList();
146
    private long total = 0;
147
    private long size = 10;
148
    private long current = 1;
149
    
150
    // Implements IPage methods
151
}
152
```
153

154
## Database Dialects
155

156
MyBatis-Plus Extension supports multiple database dialects for optimal pagination SQL generation.
157

158
### IDialect Interface
159

160
```java { .api }
161
public interface IDialect {
162
    boolean willDoCount(IPage<?> page, List<MappedStatement> countMs);
163
    String buildPaginationSql(String originalSql, long offset, long limit);
164
}
165
```
166

167
### Available Dialects
168

169
```java { .api }
170
// MySQL/MariaDB
171
public class MySqlDialect implements IDialect;
172

173
// PostgreSQL
174
public class PostgreDialect implements IDialect;
175

176
// Oracle
177
public class OracleDialect implements IDialect;
178
public class Oracle12cDialect implements IDialect;
179

180
// SQL Server
181
public class SQLServerDialect implements IDialect;
182
public class SQLServer2005Dialect implements IDialect;
183

184
// DB2
185
public class DB2Dialect implements IDialect;
186

187
// Other databases
188
public class InformixDialect implements IDialect;
189
public class SybaseDialect implements IDialect;
190
public class GBase8sDialect implements IDialect;
191
public class XCloudDialect implements IDialect;
192
public class TrinoDialect implements IDialect;
193
```
194

195
## Usage Examples
196

197
### Basic Pagination
198

199
```java
200
@Autowired
201
private UserService userService;
202

203
// Create page parameters
204
Page<User> page = new Page<>(1, 10); // page 1, size 10
205

206
// Execute paginated query
207
Page<User> result = userService.page(page);
208

209
// Access results
210
List<User> users = result.getRecords();
211
long totalRecords = result.getTotal();
212
long totalPages = result.getPages();
213
long currentPage = result.getCurrent();
214
long pageSize = result.getSize();
215

216
System.out.println("Found " + totalRecords + " users");
217
System.out.println("Page " + currentPage + " of " + totalPages);
218
```
219

220
### Pagination with Conditions
221

222
```java
223
// Create page with conditions
224
Page<User> page = new Page<>(1, 20);
225
QueryWrapper<User> queryWrapper = new QueryWrapper<User>()
226
    .eq("active", true)
227
    .like("name", "John");
228

229
Page<User> result = userService.page(page, queryWrapper);
230

231
// Process results
232
for (User user : result.getRecords()) {
233
    System.out.println("User: " + user.getName());
234
}
235
```
236

237
### Pagination with Ordering
238

239
```java
240
// Add ordering to pagination
241
Page<User> page = new Page<User>(1, 10)
242
    .addOrder(OrderItem.desc("created_time"))
243
    .addOrder(OrderItem.asc("name"));
244

245
Page<User> result = userService.page(page);
246

247
// Alternative: using static methods
248
Page<User> page2 = new Page<User>(1, 10)
249
    .addOrder(OrderItem.descs("created_time", "updated_time"))
250
    .addOrder(OrderItem.ascs("name", "email"));
251
```
252

253
### Static Factory Methods
254

255
```java
256
// Using static factory methods
257
Page<User> page1 = Page.of(1, 10);
258
Page<User> page2 = Page.of(2, 20, 100); // with total count
259
Page<User> page3 = Page.of(1, 15, false); // without count query
260

261
Page<User> result = userService.page(page1);
262
```
263

264
### Navigation Helpers
265

266
```java
267
Page<User> result = userService.page(new Page<>(2, 10));
268

269
// Check navigation state
270
if (result.hasPrevious()) {
271
    System.out.println("Previous page available");
272
}
273

274
if (result.hasNext()) {
275
    System.out.println("Next page available");
276
}
277

278
// Calculate offset
279
long offset = result.offset(); // (current - 1) * size
280
System.out.println("Offset: " + offset);
281
```
282

283
### Count Query Optimization
284

285
```java
286
// Disable count query for performance
287
Page<User> page = new Page<User>(1, 10)
288
    .setSearchCount(false); // No total count query
289

290
Page<User> result = userService.page(page);
291
// result.getTotal() will be 0
292

293
// Optimize count SQL
294
Page<User> optimizedPage = new Page<User>(1, 10)
295
    .setOptimizeCountSql(true) // Remove unnecessary ORDER BY in count
296
    .setOptimizeJoinOfCountSql(true); // Optimize JOIN queries in count
297

298
// Set max limit to prevent large page sizes
299
Page<User> limitedPage = new Page<User>(1, 1000)
300
    .setMaxLimit(500L); // Will be capped at 500
301
```
302

303
### Lambda Query Chains with Pagination
304

305
```java
306
// Using lambda query chains
307
Page<User> page = userService.lambdaQuery()
308
    .eq(User::getActive, true)
309
    .gt(User::getAge, 18)
310
    .orderByDesc(User::getCreatedTime)
311
    .page(new Page<>(1, 10));
312

313
List<User> users = page.getRecords();
314
```
315

316
### Chain Wrappers with Pagination
317

318
```java
319
// Using chain wrappers
320
Page<User> page = ChainWrappers.queryChain(userMapper)
321
    .eq("department", "IT")
322
    .isNotNull("email")
323
    .orderByAsc("name")
324
    .page(new Page<>(1, 15));
325
```
326

327
### Custom Count Query
328

329
```java
330
// Use custom count query ID
331
Page<User> page = new Page<User>(1, 10)
332
    .setCountId("selectUserCountCustom"); // Custom count method
333

334
Page<User> result = userService.page(page, queryWrapper);
335
```
336

337
### Map Results Pagination
338

339
```java
340
// Paginate map results
341
Page<Map<String, Object>> mapPage = new Page<>(1, 10);
342
Page<Map<String, Object>> result = userService.pageMaps(mapPage, 
343
    new QueryWrapper<User>().select("id", "name", "email"));
344

345
for (Map<String, Object> record : result.getRecords()) {
346
    System.out.println("ID: " + record.get("id"));
347
    System.out.println("Name: " + record.get("name"));
348
}
349
```
350

351
## Advanced Features
352

353
### Custom Pagination Interceptor Configuration
354

355
```java
356
@Configuration
357
public class PaginationConfig {
358
    
359
    @Bean
360
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
361
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
362
        
363
        PaginationInnerInterceptor paginationInterceptor = 
364
            new PaginationInnerInterceptor(DbType.MYSQL);
365
        
366
        // Configure options
367
        paginationInterceptor.setMaxLimit(1000L); // Max page size
368
        paginationInterceptor.setOverflow(false); // Handle page overflow
369
        paginationInterceptor.setOptimizeJoin(true); // Optimize JOIN in count
370
        
371
        interceptor.addInnerInterceptor(paginationInterceptor);
372
        return interceptor;
373
    }
374
}
375
```
376

377
### Custom Dialect
378

379
```java
380
public class CustomDialect implements IDialect {
381
    
382
    @Override
383
    public boolean willDoCount(IPage<?> page, List<MappedStatement> countMs) {
384
        return page.searchCount();
385
    }
386
    
387
    @Override
388
    public String buildPaginationSql(String originalSql, long offset, long limit) {
389
        // Custom pagination SQL generation
390
        return originalSql + " LIMIT " + offset + ", " + limit;
391
    }
392
}
393

394
// Register custom dialect  
395
@Configuration
396
public class CustomDialectConfig {
397
    
398
    @Bean
399
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
400
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
401
        
402
        PaginationInnerInterceptor paginationInterceptor = 
403
            new PaginationInnerInterceptor();
404
        paginationInterceptor.setDialect(new CustomDialect());
405
        
406
        interceptor.addInnerInterceptor(paginationInterceptor);
407
        return interceptor;
408
    }
409
}
410
```
411

412
### Performance Optimizations
413

414
```java
415
// For large datasets, disable count query on subsequent pages
416
Page<User> firstPage = new Page<>(1, 50);
417
Page<User> result1 = userService.page(firstPage);
418

419
// Subsequent pages without count
420
Page<User> secondPage = new Page<User>(2, 50)
421
    .setSearchCount(false)
422
    .setTotal(result1.getTotal()); // Use previous total
423

424
Page<User> result2 = userService.page(secondPage);
425
```
426

427
### Infinite Scroll Pattern
428

429
```java
430
// Implementing infinite scroll
431
public Page<User> getNextPage(Long lastId, int size) {
432
    Page<User> page = new Page<User>(1, size)
433
        .setSearchCount(false); // No count needed for infinite scroll
434
    
435
    QueryWrapper<User> wrapper = new QueryWrapper<User>()
436
        .gt("id", lastId) // Cursor-based pagination
437
        .orderByAsc("id");
438
    
439
    return userService.page(page, wrapper);
440
}
441

442
// Usage
443
Page<User> firstBatch = getNextPage(0L, 20);
444
Long lastId = firstBatch.getRecords().get(firstBatch.getRecords().size() - 1).getId();
445
Page<User> nextBatch = getNextPage(lastId, 20);
446
```
447

448
### Error Handling
449

450
```java
451
try {
452
    Page<User> page = new Page<>(100, 50); // Potentially invalid page
453
    Page<User> result = userService.page(page);
454
    
455
    if (result.getRecords().isEmpty() && result.getCurrent() > 1) {
456
        // Handle empty page - might want to redirect to last valid page
457
        long lastPage = result.getPages();
458
        page.setCurrent(lastPage);
459
        result = userService.page(page);
460
    }
461
} catch (Exception e) {
462
    // Handle pagination errors
463
    log.error("Pagination error", e);
464
}
465
```
466

467
## Performance Considerations
468

469
- Use `setSearchCount(false)` when total count is not needed
470
- Enable count query optimizations with `setOptimizeCountSql(true)`
471
- Set reasonable `maxLimit` to prevent memory issues
472
- Consider cursor-based pagination for very large datasets
473
- Use database-appropriate dialects for optimal SQL generation
474
- Cache count results for expensive queries when possible