SpringBoot之整合PageHelper分页插件
文章目录
详细配置请查看官网或MyBatis分页插件之PageHelper详细介绍-CSDN博客
1. 引入坐标
<!--pagehelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.3.0</version>
<!--排除pagehelper的依赖mybatis和mybatis-spring的jar包以免与mybatis-plus的冲突，导致报NoClassFound org.mybatis.logging.LoggerFactory-->
<exclusions>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</exclusion>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
</exclusion>
</exclusions>
</dependency>
2. application.yml配置
pagehelper:
helper-dialect: mysql
reasonable: true
support-methods-arguments: true
params: count=countSql
3. 基本使用
@Autowired
private PublicService publicService;
@GetMapping(value = "/getUserList")
public Result<PageInfo> getUserList(@RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
@RequestParam(name="pageSize", defaultValue="5") Integer pageSize){
StringBuffer sql = new StringBuffer();
sql.append("SELECT\n" +
" a.id,\n" +
" a.username,\n" +
" b.id AS file_id,\n" +
" b.file_url,\n" +
" b.file_size,\n" +
"FROM\n" +
" sys_user a\n" +
" LEFT JOIN sys_file b ON a.id = b.parent_id \n" +
"WHERE\n" +
" a.del_flag = '0'");
Result result = new Result<>();
Map map = new HashMap(5);
map.put("sql",sql.toString());
//获取第pageNo页，pageSize条内容，默认查询总数count
PageHelper.startPage(pageNo, pageSize);
//紧跟着的第一个select方法会被分页
List<Map<String, Object>> mapList = publicService.sqlQuery(map);
result.setResult(mapList);
result.setSuccess(true);
PageInfo pageInfo = new PageInfo(mapList);
return Result.OK(pageInfo);
4. 对多个查询执行分页
Pagehelper中只有紧跟在
PageHelper.startPage方法后的第一个 Mybatis 的查询（Select）方法会被分页。
1. 默认第一个Select语句会执行分页
案例代码如下：
@Autowired
private PublicService publicService;
public List<SignatureUser> getUserList(){
//获取第pageNo页，pageSize条内容，默认查询总数count
PageHelper.startPage(pageNo, pageSize);
//紧跟着的第一个select方法会被分页
List<Map<String, Object>> mapList = publicService.sqlQuery(map);
IPage iPage = IPageUtil.pageData(mapList);
//下面这个查询不会分页
List<SignatureUser> signatureUserList = publicService.getSignatureUserList(map);
System.out.println(signatureUserList.size());
return signatureUserList;
}
2. 让Pagehelper也能执行多个分页的方法
在查询参数中设置pageNum与pageSize参数使其第二个查询也能分页，如下：
@Autowired
private PublicService publicService;
public List<SignatureUser> getUserList(){
//获取第pageNo页，pageSize条内容，默认查询总数count
PageHelper.startPage(pageNo, pageSize);
//紧跟着的第一个select方法会被分页
List<Map<String, Object>> mapList = publicService.sqlQuery(map);
IPage iPage = IPageUtil.pageData(mapList);
System.out.println("第一个查询分页结果",iPage);
Map map1 = new HashMap(3);
//加入mybatis分页的参数pageNum与pageSize则其他查询也能分页
map1.put("pageNum", pageNo);
map1.put("pageSize", pageSize);
List<SignatureUser> signatureUserList = publicService.getSignatureUserList(map1);
System.out.println(signatureUserList.size());
return signatureUserList;
}
3. 完整案例
@Autowired
private PublicService publicService;
@ApiOperation(value = "用户信息列表", notes = "用户信息列表")
@GetMapping(value = "/getUserList")
public Result<?> getUserList(@RequestParam(name="pageNo", defaultValue="1") Integer pageNo,
@RequestParam(name="pageSize", defaultValue="5") Integer pageSize){
StringBuffer sql = new StringBuffer();
sql.append("SELECT\n" +
" a.id,\n" +
" a.username,\n" +
" b.id AS file_id,\n" +
" b.file_url,\n" +
" b.file_size,\n" +
"FROM\n" +
" sys_user a\n" +
" LEFT JOIN sys_file b ON a.id = b.parent_id \n" +
"WHERE\n" +
" a.del_flag = '0'");
//一.直接sql方式分页
Map map = new HashMap(5);
map.put("sql",sql.toString());
//获取第pageNo页，pageSize条内容，默认查询总数count
PageHelper.startPage(pageNo, pageSize);
//紧跟着的第一个select方法会被分页
List<Map<String, Object>> mapList = publicService.sqlQuery(map);
IPage iPage = IPageUtil.pageData(mapList);
//return Result.OK(iPage);
//二.对象集合分页
Map map1 = new HashMap(3);
map1.put("pageNum", pageNo);
map1.put("pageSize", pageSize);
List<SignatureUser> signatureUserList = publicService.getSignatureUserList(map1);
System.out.println(signatureUserList.size());
return Result.OK(IPageUtil.pageData(signatureUserList));
}
