SpringBoot之整合PageHelper分页插件

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));
    }