SpringBoot+Mybatis-Plus分页查询实战指南

1. 为什么需要分页查询在开发Web应用时数据列表展示是最常见的需求之一。当数据量达到几百、几千甚至更多时如果一次性加载所有数据会导致数据库压力剧增查询性能下降网络传输数据量过大页面加载缓慢前端渲染大量DOM节点浏览器内存占用过高我曾在项目中遇到过这样的场景一个用户管理页面在数据量达到5000条时页面加载时间超过10秒严重影响了用户体验。这就是典型的需要分页处理的案例。2. 环境准备与项目搭建2.1 创建SpringBoot项目使用IDEA或Eclipse创建一个新的SpringBoot项目选择以下依赖Spring WebLombok可选但推荐Thymeleaf用于前端展示2.2 添加Mybatis-Plus依赖在pom.xml中添加Mybatis-Plus starter和数据库相关依赖dependency groupIdcom.baomidou/groupId artifactIdmybatis-plus-boot-starter/artifactId version3.5.3.1/version /dependency !-- MySQL驱动 -- dependency groupIdmysql/groupId artifactIdmysql-connector-java/artifactId version8.0.33/version /dependency !-- Druid连接池推荐 -- dependency groupIdcom.alibaba/groupId artifactIddruid-spring-boot-starter/artifactId version1.2.16/version /dependency注意版本号请根据实际情况选择最新稳定版。Druid连接池不是必须的但生产环境强烈推荐使用。2.3 数据库配置在application.yml中配置数据源spring: datasource: url: jdbc:mysql://localhost:3306/mybatis_plus_demo?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf-8 username: root password: yourpassword driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource3. Mybatis-Plus分页实现3.1 配置分页插件创建配置类MybatisPlusConfigConfiguration public class MybatisPlusConfig { Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); // 分页插件 interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } }这里有几个关键点需要注意PaginationInnerInterceptor是核心分页实现类DbType.MYSQL指定了数据库类型Mybatis-Plus会根据不同类型生成不同的分页SQL在SpringBoot 2.7.x及以上版本这是推荐的分页配置方式3.2 创建实体类和Mapper创建User实体类Data TableName(user) public class User { TableId(type IdType.AUTO) private Long id; private String name; private Integer age; private String email; }创建Mapper接口Mapper public interface UserMapper extends BaseMapperUser { }BaseMapper已经内置了分页查询方法selectPage我们无需额外编写SQL。3.3 Service层实现创建UserService接口public interface UserService extends IServiceUser { }实现类Service public class UserServiceImpl extends ServiceImplUserMapper, User implements UserService { }4. 分页查询实战4.1 基本分页查询在Controller中添加分页查询方法RestController RequestMapping(/user) public class UserController { Autowired private UserService userService; GetMapping(/page) public IPageUser page(RequestParam(defaultValue 1) int current, RequestParam(defaultValue 10) int size) { PageUser page new Page(current, size); return userService.page(page); } }测试访问http://localhost:8080/user/page?current1size54.2 带条件的分页查询实际项目中我们经常需要根据条件筛选数据GetMapping(/pageByCondition) public IPageUser pageByCondition(RequestParam(defaultValue 1) int current, RequestParam(defaultValue 10) int size, RequestParam(required false) String name) { PageUser page new Page(current, size); LambdaQueryWrapperUser queryWrapper new LambdaQueryWrapper(); if (StringUtils.isNotBlank(name)) { queryWrapper.like(User::getName, name); } return userService.page(page, queryWrapper); }4.3 自定义SQL分页对于复杂查询我们可以自定义SQL在Mapper接口中添加方法Mapper public interface UserMapper extends BaseMapperUser { Select(SELECT * FROM user WHERE age #{age}) IPageUser selectByAge(IPageUser page, Param(age) Integer age); }在Service中调用public IPageUser pageByAge(int current, int size, int age) { PageUser page new Page(current, size); return baseMapper.selectByAge(page, age); }5. 前端分页展示5.1 Thymeleaf实现创建Controller方法Controller public class PageController { Autowired private UserService userService; GetMapping(/users) public String userPage(RequestParam(defaultValue 1) int pageNum, Model model) { PageUser page new Page(pageNum, 5); IPageUser userPage userService.page(page); model.addAttribute(page, userPage); return user-list; } }创建user-list.html!DOCTYPE html html xmlns:thhttp://www.thymeleaf.org head meta charsetUTF-8 title用户列表/title style .pagination { margin-top: 20px; } .pagination a { padding: 5px 10px; margin: 0 5px; border: 1px solid #ddd; text-decoration: none; } .pagination a.active { background: #1890ff; color: white; border-color: #1890ff; } /style /head body table border1 cellpadding10 tr thID/th th姓名/th th年龄/th th邮箱/th /tr tr th:eachuser : ${page.records} td th:text${user.id}/td td th:text${user.name}/td td th:text${user.age}/td td th:text${user.email}/td /tr /table div classpagination a th:href{/users(pageNum1)}首页/a a th:href{/users(pageNum${page.current}-1)} th:if${page.current 1}上一页/a a th:eachi : ${#numbers.sequence(1, page.pages)} th:href{/users(pageNum${i})} th:class${i page.current ? active : } th:text${i}/a a th:href{/users(pageNum${page.current}1)} th:if${page.current page.pages}下一页/a a th:href{/users(pageNum${page.pages})}尾页/a /div div 当前页: span th:text${page.current}/span / 总页数: span th:text${page.pages}/span | 总记录数: span th:text${page.total}/span /div /body /html5.2 前后端分离实现对于前后端分离项目后端只需要返回分页数据JSONGetMapping(/api/users) public ResultIPageUser listUsers(RequestParam(defaultValue 1) int pageNum, RequestParam(defaultValue 10) int pageSize) { PageUser page new Page(pageNum, pageSize); IPageUser userPage userService.page(page); return Result.success(userPage); }前端Vue示例template div table tr v-foruser in pageData.records :keyuser.id td{{ user.id }}/td td{{ user.name }}/td td{{ user.age }}/td td{{ user.email }}/td /tr /table div classpagination button clickchangePage(1)首页/button button clickchangePage(currentPage - 1) :disabledcurrentPage 1上一页/button button v-forpage in totalPages :keypage clickchangePage(page) :class{ active: page currentPage } {{ page }} /button button clickchangePage(currentPage 1) :disabledcurrentPage totalPages下一页/button button clickchangePage(totalPages)尾页/button /div /div /template script export default { data() { return { pageData: {}, currentPage: 1, pageSize: 10, totalPages: 0 } }, created() { this.fetchData() }, methods: { async fetchData() { const res await this.$http.get(/api/users, { params: { pageNum: this.currentPage, pageSize: this.pageSize } }) this.pageData res.data.data this.totalPages this.pageData.pages }, changePage(page) { if (page 1 || page this.totalPages) return this.currentPage page this.fetchData() } } } /script6. 高级功能与优化6.1 分页参数合理化在实际项目中我们需要对分页参数进行校验和合理化GetMapping(/safePage) public IPageUser safePage(RequestParam(defaultValue 1) int current, RequestParam(defaultValue 10) int size) { // 限制每页最大100条 size Math.min(size, 100); // 确保页码不小于1 current Math.max(current, 1); PageUser page new Page(current, size); return userService.page(page); }6.2 性能优化禁用COUNT查询当不需要总记录数时PageUser page new Page(current, size); page.setSearchCount(false); // 禁用COUNT查询优化COUNT SQL对于大表可以重写count语句Select(SELECT * FROM user WHERE age #{age}) Options(countSql SELECT COUNT(1) FROM user WHERE age #{age} USE INDEX(idx_age)) IPageUser selectByAge(IPageUser page, Param(age) Integer age);6.3 多表关联分页对于多表关联查询Mybatis-Plus也支持Select(SELECT u.*, d.name as deptName FROM user u LEFT JOIN department d ON u.dept_id d.id) IPageUser selectUserWithDept(IPageUser page);7. 常见问题与解决方案7.1 分页失效问题现象分页配置了但不起作用返回了全部数据原因分页插件未正确配置在事务中调用了分页方法解决方案确保MybatisPlusConfig被Spring扫描到检查是否在事务方法中调用如果是确保事务方法不是private的7.2 排序问题需求需要按特定字段排序解决方案PageUser page new Page(current, size); page.addOrder(OrderItem.desc(create_time)); // 按创建时间降序或者使用QueryWrapperLambdaQueryWrapperUser queryWrapper new LambdaQueryWrapper(); queryWrapper.orderByDesc(User::getCreateTime);7.3 自定义分页对象有时我们需要返回更多分页信息Data public class PageResultT { private long current; private long size; private long total; private long pages; private ListT records; // 其他自定义字段 private Object extraInfo; public static T PageResultT success(IPageT page) { PageResultT result new PageResult(); result.setCurrent(page.getCurrent()); result.setSize(page.getSize()); result.setTotal(page.getTotal()); result.setPages(page.getPages()); result.setRecords(page.getRecords()); return result; } }在Controller中使用GetMapping(/customPage) public PageResultUser customPage(RequestParam(defaultValue 1) int current, RequestParam(defaultValue 10) int size) { PageUser page new Page(current, size); IPageUser userPage userService.page(page); return PageResult.success(userPage); }8. 最佳实践与经验分享8.1 统一分页参数处理在实际项目中建议统一处理分页参数创建分页参数类Data public class PageParam { Min(1) private int pageNum 1; Min(1) Max(100) private int pageSize 10; public T PageT toPage() { return new Page(pageNum, pageSize); } }在Controller中使用GetMapping(/page) public IPageUser page(PageParam pageParam) { return userService.page(pageParam.toPage()); }8.2 分页缓存策略对于变化不频繁的数据可以考虑缓存分页结果Cacheable(value userPage, key #current - #size) public IPageUser getCachedPage(int current, int size) { return userService.page(new Page(current, size)); }8.3 大数据量分页优化当数据量非常大时如百万级以上传统的LIMIT offset, size方式性能会很差。可以采用以下优化方案游标分页记录上一页最后一条记录的IDGetMapping(/cursorPage) public ListUser cursorPage(RequestParam(required false) Long lastId, RequestParam(defaultValue 10) int size) { LambdaQueryWrapperUser queryWrapper new LambdaQueryWrapper(); if (lastId ! null) { queryWrapper.gt(User::getId, lastId); } queryWrapper.orderByAsc(User::getId) .last(LIMIT size); return userService.list(queryWrapper); }子查询优化Select(SELECT * FROM user WHERE id (SELECT id FROM user ORDER BY id LIMIT #{offset}, 1) LIMIT #{size}) ListUser selectBySubQuery(Param(offset) long offset, Param(size) int size);9. 项目结构与完整代码9.1 项目结构src/main/java ├── com.example.demo │ ├── config │ │ └── MybatisPlusConfig.java │ ├── controller │ │ ├── UserController.java │ │ └── PageController.java │ ├── entity │ │ └── User.java │ ├── mapper │ │ └── UserMapper.java │ ├── service │ │ ├── UserService.java │ │ └── impl │ │ └── UserServiceImpl.java │ └── DemoApplication.java src/main/resources ├── static ├── templates │ └── user-list.html └── application.yml9.2 完整配置类Configuration MapperScan(com.example.demo.mapper) public class MybatisPlusConfig { Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); // 分页插件 PaginationInnerInterceptor paginationInnerInterceptor new PaginationInnerInterceptor(); paginationInnerInterceptor.setDbType(DbType.MYSQL); paginationInnerInterceptor.setMaxLimit(100L); // 单页分页条数限制 paginationInnerInterceptor.setOverflow(true); // 溢出总页数后是否进行处理 interceptor.addInnerInterceptor(paginationInnerInterceptor); return interceptor; } Bean public ConfigurationCustomizer configurationCustomizer() { return configuration - configuration.setUseDeprecatedExecutor(false); } }10. 测试与验证10.1 单元测试编写测试类验证分页功能SpringBootTest class UserServiceTest { Autowired private UserService userService; Test void testPageQuery() { PageUser page new Page(1, 5); IPageUser userPage userService.page(page); assertNotNull(userPage.getRecords()); assertEquals(5, userPage.getRecords().size()); assertTrue(userPage.getTotal() 0); } }10.2 API测试使用Postman或curl测试APIcurl http://localhost:8080/user/page?current2size5预期返回结果{ records: [ { id: 6, name: User6, age: 26, email: user6example.com }, ... ], total: 50, size: 5, current: 2, pages: 10 }10.3 前端页面测试访问http://localhost:8080/users应该能看到分页展示的用户列表和分页控件。11. 扩展思考11.1 与其他技术的整合Spring Security在分页查询中加入权限过滤Redis缓存缓存热门数据的分页结果Elasticsearch对于搜索场景使用ES的分页功能11.2 微服务场景下的分页在微服务架构中分页需要考虑跨服务的数据聚合分页分布式事务下的分页一致性服务间分页参数的传递规范11.3 未来演进响应式分页与WebFlux整合智能分页根据网络状况动态调整分页大小无限滚动移动端友好的分页体验在实际项目开发中分页查询看似简单但要做到高性能、易用且可维护需要考虑的细节非常多。Mybatis-Plus提供的分页功能已经覆盖了大部分常见场景通过合理的配置和使用可以显著提高开发效率。