PageHelper一对多分页问题解决，自定义抽象一对多分页工具类

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

1. 引入maven依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.3.0</version>
</dependency>

2. 创建相关数据表

• 用户表

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `username` varchar(20) DEFAULT NULL COMMENT '用户名',
  `password` varchar(20) DEFAULT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `update_by` varchar(225) DEFAULT NULL,
  `create_by` varchar(225) DEFAULT NULL,
  `del_falg` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

• 角色表

CREATE TABLE `t_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键id',
  `role_name` varchar(20) DEFAULT NULL COMMENT '角色名称',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更改时间',
  `create_by` varchar(255) DEFAULT NULL COMMENT '创建人',
  `update_by` varchar(255) DEFAULT NULL COMMENT '更新人',
  `del_flag` int(11) NOT NULL DEFAULT '1' COMMENT '1正常0已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='角色信息 ';

• 用户角色关联表

CREATE TABLE `t_user_role` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `user_id` int(11) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

3. 相关实体类

• PageParam.java

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

/**
 * @author eleven
 * @date 2021/3/20-13:36
 * @apiNote 分页信息类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageParam<T> {
    private Integer currentPage;
    private Integer pageSize;
    private Long total;
    private List<T> data;
}

• User.java

import com.baomidou.mybatisplus.annotation.TableField;
import com.demo.common.PageParam;
import lombok.Data;
import org.yaml.snakeyaml.events.Event;

import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;

/**
 * @author eleven
 * @date 2021/3/20-13:04
 * @apiNote 用户实体类
 */
@Data
public class User extends PageParam<User> {
    private Integer id;
    private String username;
    private String password;
    private LocalDateTime creatTime;
    private LocalDateTime updateTime;
    private String createBy;
    private String updateBy;
    private Integer delFlag;

    @TableField(exist = false)
    private List<UserRole> userRoleList;
    @TableField(exist = false)
    private Role role;

}

• Role.java

import com.demo.common.PageParam;
import lombok.Data;

import java.time.LocalDateTime;

@Data
public class Role extends PageParam<Role> {
  private Integer id;
  private String roleName;
  private LocalDateTime createTime;
  private LocalDateTime updateTime;
  private String createBy;
  private String updateBy;
  private Integer delFlag;
  
}

• UserRole.java

import com.demo.common.PageParam;
import lombok.Data;

@Data
public class UserRole extends PageParam<UserRole> {

  private Integer id;
  /** 用户id */
  private Integer userId;
  /** 角色id */
  private Integer roleId;

  private Role role;

}

4. 情景复现

• UserController

import com.demo.common.Result;
import com.demo.common.ResultFactory;
import com.demo.entity.User;
import com.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
 * @author eleven
 * @date 2021/3/20-13:26
 * @apiNote 用户
 */
@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    /**
     * 需求： 分页查询所有的用户信息并且把角色信息携带出来
     */
    @GetMapping("/selectUser")
    public Result selectUser(User user){
        return userService.selectUser(user);
    }

}

• UserService

import com.baomidou.mybatisplus.extension.service.IService;
import com.demo.common.Result;
import com.demo.entity.User;

/**
 * @author eleven
 * @date 2021/3/20-13:27
 * @apiNote
 */
public interface UserService extends IService<User> {
    /**
     * 查询用户信息
     * @param user
     * @return
     */
    Result selectUser(User user);
}

• UserServiceImpl

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.demo.common.PageParam;
import com.demo.common.PageUtil;
import com.demo.common.Result;
import com.demo.common.ResultFactory;
import com.demo.entity.User;
import com.demo.mapper.UserMapper;
import com.demo.service.UserService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author eleven
 * @date 2021/3/20-13:29
 * @apiNote userService实现类
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public Result selectUser(User user){
       
        PageHelper.startPage(user.getCurrentPage(),user.getPageSize());
        List<User> userList = userMapper.selectUser(user);
        PageInfo<User> info = new PageInfo(userList);
       
        return ResultFactory.success(info);
    }
}

• UserMapper

import java.util.List;

/**
 * @author eleven
 * @date 2021/3/20-13:29
 * @apiNote
 */
public interface UserMapper extends BaseMapper<User> {
    /**
     * 用户信息
     * @param user
     * @return
     */
    List<User> selectUser(User user);
}

• UserMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.demo.mapper.UserMapper">
    <resultMap id="baseMap" type="com.demo.entity.User">
        <id property="id" column="id"/><!---->
        <result property="username" column="username"/><!---->
        <result property="password" column="password"/><!---->
        <result property="creatTime" column="creat_time"/><!---->
        <result property="updateTime" column="update_time"/><!---->
        <result property="createBy" column="create_by"/><!---->
        <result property="updateBy" column="update_by"/><!---->
        <result property="delFlag" column="del_flag"/><!---->
        <!-- 一对一 -->
        <association property="role" javaType="com.demo.entity.Role">
            <id property="id" column="tr_id" />
            <result property="roleName" column="tr_role_name" />
            <result property="createTime" column="tr_create_time" />
            <result property="createBy" column="tr_create_by" />
            <result property="updateTime" column="tr_update_time" />
            <result property="updateBy" column="tr_update_by" />
            <result property="delFlag" column="tr_del_flag" />
        </association>
        <!--一对多-->
        <collection property="userRoleList" ofType="com.demo.entity.UserRole">
            <id property="id" column="tur_id" />
            <result property="userId" column="tur_user_id" />
            <result property="roleId" column="tur_role_id" />
        </collection>
    </resultMap>
    <select id="selectUser" resultMap="baseMap">
        select
           tb.*,
           tur.id as tur_id,
           tur.user_id as tur_user_id,
           tur.role_id as tur_role_id,
           tr.id as tr_id,
           tr.role_name as tr_role_name,
           tr.create_time as tr_create_time,
           tr.update_time as tr_update_time,
           tr.create_by as tr_create_by,
           tr.update_by as tr_update_by,
           tr.del_flag as tr_del_flag
        from t_user tb
        left join t_user_role tur on tb.id = tur.user_id
        left join t_role tr on tur.role_id = tr.id

    </select>
</mapper>

发送请求

GET localhost:8080/user/selectUser?currentPageSize=1&pageSize=3

select tb.*, tur.id as tur_id, tur.user_id as tur_user_id, tur.role_id as tur_role_id, tr.id as tr_id, tr.role_name as tr_role_name, tr.create_time as tr_create_time, tr.update_time as tr_update_time, tr.create_by as tr_create_by, tr.update_by as tr_update_by, tr.del_flag as tr_del_flag from t_user tb left join t_user_role tur on tb.id = tur.user_id left join t_role tr on tur.role_id = tr.id LIMIT 3
<==        Row: 1, 123, 123, null, null, null, null, null, 1, 1, 1, 1, admin, 2021-03-20 13:00:22, 2020-03-20 13:00:30, admin, admin, 1
<==        Row: 1, 123, 123, null, null, null, null, null, 2, 1, 2, null, null, null, null, null, null, null
<==        Row: 2, 456, 456, null, null, null, null, null, 3, 2, 2, null, null, null, null, null, null, null
<==        Row: 3, 789, 789, null, null, null, null, null, 4, 3, 2, null, null, null, null, null, null, null
<==      Total: 4

• 响应结果

{
    "code": 200,
    "message": "success",
    "data": {
        "total": 4,
        "list": [
            {
                "currentPage": null,
                "pageSize": null,
                "total": null,
                "data": null,
                "id": 1,
                "username": "123",
                "password": "123",
                "creatTime": null,
                "updateTime": null,
                "createBy": null,
                "updateBy": null,
                "delFlag": null,
                "userRoleList": [
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 1,
                        "userId": 1,
                        "roleId": 1,
                        "role": null
                    },
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 2,
                        "userId": 1,
                        "roleId": 2,
                        "role": null
                    }
                ],
                "role": {
                    "currentPage": null,
                    "pageSize": null,
                    "total": null,
                    "data": null,
                    "id": 1,
                    "roleName": "admin",
                    "createTime": "2021-03-20T13:00:22",
                    "updateTime": "2020-03-20T13:00:30",
                    "createBy": "admin",
                    "updateBy": "admin",
                    "delFlag": 1
                }
            },
            {
                "currentPage": null,
                "pageSize": null,
                "total": null,
                "data": null,
                "id": 2,
                "username": "456",
                "password": "456",
                "creatTime": null,
                "updateTime": null,
                "createBy": null,
                "updateBy": null,
                "delFlag": null,
                "userRoleList": [
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 3,
                        "userId": 2,
                        "roleId": 2,
                        "role": null
                    }
                ],
                "role": null
            }
        ],
        "pageNum": 1,
        "pageSize": 3,
        "size": 2,
        "startRow": 1,
        "endRow": 2,
        "pages": 2,
        "prePage": 0,
        "nextPage": 2,
        "isFirstPage": true,
        "isLastPage": false,
        "hasPreviousPage": false,
        "hasNextPage": true,
        "navigatePages": 8,
        "navigatepageNums": [
            1,
            2
        ],
        "navigateFirstPage": 1,
        "navigateLastPage": 2
    }
}

因为pageHelper在执行sql之前先执行了 SELECT count(0) FROM t_user tb LEFT JOIN t_user_role tur ON tb.id = tur.user_id LEFT JOIN t_role tr ON tur.role_id = tr.id 去获取查询的总条数，然后在查询语句的最后添加了 limit,mybatis一对多将重复列折叠了，导致total和 data返回出错

5. 解决思路, 使用反射不方便，换成 #6 这种了

创建一个工具类，使用List.subList()方法在java代码中进行手动分页

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;

/**
 * @author eleven
 * @date 2021/3/20-14:31
 * @apiNote 一对多分页工具类
 */
public class PageUtil<T> {
    public PageParam<T> getPage(T t, List<T> data) {
        PageParam<T> page = new PageParam<>();
        Class<?> clazz = t.getClass();
        Integer currentPage = 1;
        Integer pageSize = 10;
        Integer fromIndex = 0;
        Integer toIndex = 10;
        try {
            //获取类中currentPage属性
            PropertyDescriptor currentPageMethod = new PropertyDescriptor("currentPage", clazz);
            //获取getCurrentPage()方法
            Method getCurrentPage = currentPageMethod.getReadMethod();
            //获取set方法
            Method writeMethod = currentPageMethod.getWriteMethod();
            //执行getCurrentPage()方法
            currentPage = (Integer) getCurrentPage.invoke(t);

            PropertyDescriptor pageSizeMethod = new PropertyDescriptor("pageSize", clazz);
            Method getPageSize = pageSizeMethod.getReadMethod();
            pageSize = (Integer) getPageSize.invoke(t);

        } catch (IntrospectionException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }

        int from = (currentPage  - 1) * pageSize ;
        fromIndex = from > data.size() ? data.size() : from;
        toIndex = (fromIndex + pageSize) > data.size() ? data.size() : (fromIndex + pageSize);
        page.setCurrentPage(currentPage);
        page.setPageSize(pageSize);
        Long total = (long)data.size();
        page.setTotal(total);
        page.setData(data.subList(fromIndex, toIndex));
        return page;
    }
}

修改UserServiceImpl

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.demo.common.PageParam;
import com.demo.common.PageUtil;
import com.demo.common.Result;
import com.demo.common.ResultFactory;
import com.demo.entity.User;
import com.demo.mapper.UserMapper;
import com.demo.service.UserService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author eleven
 * @date 2021/3/20-13:29
 * @apiNote userService实现类
 */
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public Result selectUser(User user){
        /**
         * PageHelper.startPage(user.getCurrentPage(),user.getPageSize());
         * List<User> userList = userMapper.selectUser(user);
         * PageInfo<User> info = new PageInfo(userList);
         */
        List<User> userList = userMapper.selectUser(user);
        PageUtil<User> util = new PageUtil<>();
        PageParam<User> page = util.getPage(user, userList);
        return ResultFactory.success(page);
    }
}

再次调用之后的响应结果

{
    "code": 200,
    "message": "success",
    "data": {
        "currentPage": 1,
        "pageSize": 3,
        "total": 3,
        "data": [
            {
                "currentPage": null,
                "pageSize": null,
                "total": null,
                "data": null,
                "id": 1,
                "username": "123",
                "password": "123",
                "creatTime": null,
                "updateTime": null,
                "createBy": null,
                "updateBy": null,
                "delFlag": null,
                "userRoleList": [
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 1,
                        "userId": 1,
                        "roleId": 1,
                        "role": null
                    },
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 2,
                        "userId": 1,
                        "roleId": 2,
                        "role": null
                    }
                ],
                "role": {
                    "currentPage": null,
                    "pageSize": null,
                    "total": null,
                    "data": null,
                    "id": 1,
                    "roleName": "admin",
                    "createTime": "2021-03-20T13:00:22",
                    "updateTime": "2020-03-20T13:00:30",
                    "createBy": "admin",
                    "updateBy": "admin",
                    "delFlag": 1
                }
            },
            {
                "currentPage": null,
                "pageSize": null,
                "total": null,
                "data": null,
                "id": 2,
                "username": "456",
                "password": "456",
                "creatTime": null,
                "updateTime": null,
                "createBy": null,
                "updateBy": null,
                "delFlag": null,
                "userRoleList": [
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 3,
                        "userId": 2,
                        "roleId": 2,
                        "role": null
                    }
                ],
                "role": null
            },
            {
                "currentPage": null,
                "pageSize": null,
                "total": null,
                "data": null,
                "id": 3,
                "username": "789",
                "password": "789",
                "creatTime": null,
                "updateTime": null,
                "createBy": null,
                "updateBy": null,
                "delFlag": null,
                "userRoleList": [
                    {
                        "currentPage": null,
                        "pageSize": null,
                        "total": null,
                        "data": null,
                        "id": 4,
                        "userId": 3,
                        "roleId": 2,
                        "role": null
                    }
                ],
                "role": null
            }
        ]
    }
}

6. 使用Lambda和skip limit来实现分页

1. 工具类

将以前的反射获取变更为通过 lambda 函数式接口获取， 针对两种情况做了特殊处理

1. 能够直接通过 Lambda获取到分页值的情况， 如 Entity::getCurrent, 使用方法 getPage
2. 需要通过 page 分页对象获取到分页数据的， 如 entity.getPage().getCurrent() , 使用方法 getPageByGetter

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.PageDTO;
import lombok.Data;
import lombok.experimental.Accessors;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * @author eleven
 * @date 2021/3/20-14:31
 * @apiNote 一对多分页工具类
 */
public class PageUtil<T> {
    public IPage<T> getPage(T t, List<T> data, Function<T, ? extends Number> currentGetter, Function<T, ? extends Number> sizeGetter) {
        long currentPage = Long.parseLong(String.valueOf(currentGetter.apply(t)));
        long pageSize = Long.parseLong(String.valueOf(sizeGetter.apply(t)));
        return pagination(data, currentPage, pageSize);
    }

    private static <T> IPage<T> pagination(List<T> data, long currentPage, long pageSize) {
        IPage<T> page = new PageDTO<>();
        long fromIndex = 0;
        //long toIndex = 10;
        long from = (currentPage - 1) * pageSize;
        fromIndex = from > data.size() ? data.size() : from;
        //toIndex = (fromIndex + pageSize) > data.size() ? data.size() : (fromIndex + pageSize);
        page.setCurrent(currentPage);
        page.setSize(pageSize);
        page.setTotal(data.size());
        List<T> records = data.stream()
                .skip(fromIndex)
                .limit(pageSize)
                .collect(Collectors.toList());
        page.setRecords(records);
        return page;
    }

    public IPage<T> getPageByGetter(T t, List<T> data, Getter<T, Long> currentGetter, Getter<T, Long> sizeGetter) {
        long currentPage = currentGetter.apply();
        long pageSize = sizeGetter.apply();
        return pagination(data, currentPage, pageSize);
    }

    @FunctionalInterface
    public interface Getter<T, Long> {
        Long apply();
    }
}

2. 测试类

public class PageUtilTest {
    /**
     * 测试实体类，分页参数直接在实体类中能够获取到的情况，这种建议直接使用 getPage 方法
     */
    @Data
    @Accessors(chain = true)
    static class Entity1 {
        private String id;
        private Long current;
        private Long size;
    }

    @Test
    public void getPageTest() {
        // 生成100条数据
        Entity1 cr = null;
        List<Entity1> list = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            cr = new Entity1();
            cr.setId(i + "");
            list.add(cr);
        }
        // 构建查询条件
        Entity1 queryCr = new Entity1().setCurrent(10L).setSize(10L);

        // 分页查询
        PageUtil<Entity1> pageUtil = new PageUtil<>();
        IPage<Entity1> page = pageUtil.getPage(queryCr, list, Entity1::getCurrent, Entity1::getSize);
        printPage(page, Entity1::getId);
    }


    /**
     * 测试 分页条件为 Integer 类型的情况，这种建议直接使用 getPage 方法
     */
    @Data
    @Accessors(chain = true)
    static class Entity2 {
        private String id;
        private Integer current;
        private int size;
    }

    @Test
    public void getPageTest2() {
        // 生成100条数据
        Entity2 cr = null;
        List<Entity2> list = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            cr = new Entity2();
            cr.setId(i + "");
            list.add(cr);
        }
        // 构建查询条件
        Entity2 queryCr = new Entity2().setCurrent(10).setSize(10);

        // 分页查询
        PageUtil<Entity2> pageUtil = new PageUtil<>();
        IPage<Entity2> page = pageUtil.getPage(queryCr, list, Entity2::getCurrent, Entity2::getSize);
        printPage(page, Entity2::getId);
    }

    @Data
    @Accessors(chain = true)
    static class Entity3 {
        private String id;
        // mybatisPlus 分页对象 或者自定义的分页对象
        private PageDTO<Entity3> page;
    }

    @Test
    public void getterTest() {
        // 生成100条数据
        Entity3 cr = null;
        List<Entity3> list = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            cr = new Entity3();
            cr.setId(i + "");
            list.add(cr);
        }
        // 构建查询条件
        Entity3 queryCr = new Entity3();
        PageDTO<Entity3> qrPage = new PageDTO<>();
        qrPage.setCurrent(90).setSize(10);
        queryCr.setPage(qrPage);

        // 分页查询
        PageUtil<Entity3> pageUtil = new PageUtil<>();
        IPage<Entity3> page = pageUtil.getPageByGetter(queryCr, list,
                () -> queryCr.getPage().getCurrent(),
                () -> queryCr.getPage().getSize());
        printPage(page, Entity3::getId);
    }

    private <T> void printPage(IPage<T> page, Function<T, Object> mapper) {
        System.out.println("总条数：" + page.getTotal());
        System.out.println("当前页：" + page.getCurrent());
        System.out.println("每页条数：" + page.getSize());
        System.out.println("总页数：" + page.getPages());
        System.out.println("数据：" + Arrays.toString(page.getRecords().stream().map(mapper).toArray()));
    }
}