【spring boot】开发一个mybatis基于双数据源的分页插件

​单数据源的分页没什么说的用现成的pageHelper插件就可以了，而双数据源因为存在两个数据源方言差异的问题，已经无法通过pageHelper搞定，是时候自己搞一个分页插件了

在上次基于注解实现双数据源配置的基础上，我们在加上分页插件的功能

基本思路：

• 利用拦截器拦截所有分页查询，判断当前数据源的方言
• 根据方言拼装total查询sql查询当前查询的总条数
• 根据方言拼装分页sql,实现分页查询

第一步：修改配置文件

在上次双数据源的配置文件中添加dialect参数，用于动态判断当前数据源方言

  datasource:
    db1:
      jdbc-url: jdbc:mysql://localhost:3306/mybatis-demo?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
      max-idle: 10
      max-wait: 10000
      min-idle: 5
      initial-size: 5
      dialect: mysql
    db2:
      jdbc-url: jdbc:mysql://localhost:3306/mybatis-demo?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
      max-idle: 10
      max-wait: 10000
      min-idle: 5
      initial-size: 5
      dialect: mysql

第二步：新建一个用于分页参数类PageParam.java

@Data
public class PageParam {
    public PageParam() {
        pageSize=0;
        pageNum=0;
        total = 0;
    }

    public PageParam(Integer pageNum, Integer pageSize) {
        this.pageNum = pageNum;
        this.pageSize = pageSize;
    }

    private Integer pageNum;
    //    默认每页显示条数
    private Integer pageSize;
    //    是否启用分页功能
    @JSONField(serialize = false)
    private Boolean useFlag;
    //    是否检测当前页码的合法性（大于最大页码或小于最小页码都不合法）
    @JSONField(serialize = false)
    private Boolean checkFlag;
    //当前sql查询的总记录数，回填
    private Integer total;
    //    当前sql查询实现分页后的总页数，回填
    private Integer totalPage;
    @JSONField(serialize = false)
    private String orderColumn;
    @JSONField(serialize = false)
    private String order;

    public Boolean isUseFlag() {
        return useFlag;
    }
    public Boolean istCheckFlag() {
        return checkFlag;
    }
}

第三步：新建pageConfig.java

用于数据源方言配置信息的加载及根据方言拼装分页相关的SQL语句

@Component
public class PageConfig {
    @Value("${spring.datasource.db1.dialect}")
    private String db1Dialect;
    @Value("${spring.datasource.db2.dialect}")
    private String db2Dialect;

    public String getDateSource() {
        return DataSourceContextHolder.getDB();
    }

    public static PageParam getPageParam(Integer pageNum, Integer pageSize) {
        if (pageSize == null || pageNum == null) {
            return null;
        }
        pageNum = pageNum > 0 ? pageNum : 1;
        pageSize = pageSize > 0 ? pageSize : 1;
        return new PageParam(pageNum, pageSize);
    }

    public static PageParam setPageParam(Integer pageNum, Integer pageSize, Map param) {
        PageParam pageParam = getPageParam(pageNum, pageSize);
        if (pageParam != null)
            param.put("pageParam", pageParam);
        return pageParam;
    }

    public String getDialect() {

        String dialect = getDialect(getDateSource());
        return dialect;
    }
    public String getDialect(String dbName){
        if(dbName.equals("db1"))
            return db1Dialect;
        else
            return db2Dialect;
    }

    public String getTotalSqlParam() {
        String sqlParma = " paging";
        if (getDialect().equals("postgre"))
            sqlParma = " as paging";
        else if (getDialect().equals("sqlserver")) {
            sqlParma = " as paging";
        }
        return sqlParma;
    }

    public String getSelectSqlParam(PageParam pageParam) {
        Map param =  getLimitParam(pageParam);
        //默认方言为mysql
        String sqlParma = " paging_table limit "+(Integer)param.get("first") + " , " +(Integer)param.get("second");
        String dialect = getDialect();
        if (dialect.equals("postgre")) {
            sqlParma = " paging_table limit "+(Integer)param.get("first") + "  offset " +(Integer)param.get("second");
        } else if (dialect.equals("sqlserver")) {
            //sqlserver 分页需要提供一个字段名 作为order by的参数
            sqlParma = " as t_" + pageParam.getOrderColumn() + " order by " + pageParam.getOrderColumn() + " " + pageParam.getOrder() + " OFFSET "+(Integer)param.get("first")+" ROWS\n" +
                    "            FETCH NEXT " +(Integer)param.get("second") +" ROWS ONLY";
        }
        else if(dialect.equals("mysql")){
            return sqlParma;
        }
        return sqlParma;
    }
    /**
     * 获取用于分页的参数，
     * 一个为pageSize 分页大小
     * 一个为offser(位移) = (pageNum-1)* pageSize
     * 不同数据库方言语法不同，所以用map做了映射
     * 方便sql拼装
     */

    public Map getLimitParam(PageParam pageParam) {
        Integer pageSize = pageParam.getPageSize();
        Integer pageNum = pageParam.getPageNum() > 0 ? pageParam.getPageNum() : 1;
        Integer offset = (pageNum - 1) * pageSize;
        Map param = new HashMap();
        String dialect = this.getDialect();
        if (dialect.equals("postgre")) {
            param.put("first", pageSize);
            param.put("second", offset);
        } else if (dialect.equals("mysql") || dialect.equals("sqlserver")) {
            param.put("first", offset);
            param.put("second", pageSize);
        }
        return param;
    }
}

第四步：创建拦截器PageInterceptor

该类主要功能包括2部分：

1. 查询总条数
2. 实现分页查询

@Component
@Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class}))
public class PageInterceptor implements Interceptor {
    private Logger logger = LoggerFactory.getLogger(getClass().getName());
    @Autowired
    private PageConfig pageConfig;
    //    默认页码
    private Integer defaultPageNum = 1;
    //    默认每页显示条数
    private Integer defaultPageSize = 20;
    //    是否启用分页功能
    private boolean defaultUseFlag = true;
    //    检测当前页码的合法性（大于最大页码或小于最小页码都不合法）
    private boolean defaultCheckFlag = true;


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = getActuralHandlerObject(invocation);
        MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
        String sql = statementHandler.getBoundSql().getSql();

        BoundSql boundSql = statementHandler.getBoundSql();
        Object paramObject = boundSql.getParameterObject();
        if (!checkIsSelectFalg2(paramObject)) {
            return invocation.proceed();
        }
        logger.info("Mybatis 分页插件当前数据源为: " + pageConfig.getDateSource());
        logger.info("Mybatis 分页插件当前数据源方言为: " + pageConfig.getDialect());
        PageParam pageParam = getPageParam(paramObject);

        if (pageParam == null)
            return invocation.proceed();

        Integer pageNum = pageParam.getPageNum() == null ? defaultPageNum : pageParam.getPageNum();
        Integer pageSize = pageParam.getPageSize() == null ? defaultPageSize : pageParam.getPageSize();

        Boolean useFlag = pageParam.isUseFlag() == null ? defaultUseFlag : pageParam.isUseFlag();
        Boolean checkFlag = pageParam.istCheckFlag() == null ? defaultCheckFlag : pageParam.istCheckFlag();

        //不使用分页功能
        if (!useFlag) {
            return invocation.proceed();
        }

        int totle = getTotal(invocation, metaStatementHandler, boundSql);

        //将动态获取到的分页参数回填到pageParam中
        setTotltToParam(pageParam, totle, pageSize);

        //检查当前页码的有效性
        //checkPage(checkFlag, pageNum, pageParam.getTotalPage());

        //修改sql
        return updateSql2Limit(invocation, metaStatementHandler, boundSql, pageParam);
    }

    @Override
    public Object plugin(Object o) {
        return Plugin.wrap(o, this);
    }

    //    在配置插件的时候配置默认参数
    @Override
    public void setProperties(Properties properties) {
        String strDefaultPage = properties.getProperty("defaultPageNum");
        String strDefaultPageSize = properties.getProperty("defaultPageSize");
        String strDefaultUseFlag = properties.getProperty("defaultUseFlag");
        String strDefaultCheckFlag = properties.getProperty("defaultCheckFlag");
        defaultPageNum = Integer.valueOf(strDefaultPage);
        defaultPageSize = Integer.valueOf(strDefaultPageSize);
        defaultUseFlag = Boolean.valueOf(strDefaultUseFlag);
        defaultCheckFlag = Boolean.valueOf(strDefaultCheckFlag);
    }


    //    从代理对象中分离出真实statementHandler对象,非代理对象
    private StatementHandler getActuralHandlerObject(Invocation invocation) {
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
        Object object = null;
//        分离代理对象链，目标可能被多个拦截器拦截，分离出最原始的目标类
        while (metaStatementHandler.hasGetter("h")) {
            object = metaStatementHandler.getValue("h");
            metaStatementHandler = SystemMetaObject.forObject(object);
        }

        if (object == null) {
            return statementHandler;
        }
        return (StatementHandler) object;
    }

    //    判断是否是select语句，只有select语句，才会用到分页
    private boolean checkIsSelectFalg(String sql) {
        String trimSql = sql.trim();
        int index = trimSql.toLowerCase().indexOf("select");
        return index == 0;
    }

    //有分页参数，则启动分页
    private boolean checkIsSelectFalg2(Object paramerObject) {
        boolean result = false;
        if (paramerObject == null) {
            return false;
        }
        if (paramerObject instanceof Map) {
            Map<String, Object> params = (Map<String, Object>) paramerObject;
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                if (entry.getValue() instanceof PageParam) {
                    result = true;
                    break;
                }
            }
        } else if (paramerObject instanceof PageParam) {
//            继承方式 pojo继承自PageParam 只取出我们希望得到的分页参数
            result = true;
        }
        return result;
    }

    /*
    获取分页的参数
    参数可以通过map，@param注解进行参数传递。或者请求pojo继承自PageParam  将PageParam中的分页数据放进去
     */
    private PageParam getPageParam(Object paramerObject) {
        if (paramerObject == null) {
            return null;
        }

        PageParam pageParam = null;
        //通过map和@param注解将PageParam参数传递进来，pojo继承自PageParam不推荐使用  这里从参数中提取出传递进来的pojo继承自PageParam
//        首先处理传递进来的是map对象和通过注解方式传值的情况，从中提取出PageParam,循环获取map中的键值对，取出PageParam对象
        if (paramerObject instanceof Map) {
            Map<String, Object> params = (Map<String, Object>) paramerObject;
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                if (entry.getValue() instanceof PageParam) {
                    return (PageParam) entry.getValue();
                }
            }
        } else if (paramerObject instanceof PageParam) {
//            继承方式 pojo继承自PageParam 只取出我们希望得到的分页参数
            pageParam = (PageParam) paramerObject;

        }
        return pageParam;
    }

    //    获取当前sql查询的记录总数
    private int getTotal(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql) {
//        获取mapper文件中当前查询语句的配置信息
        MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");

        //获取所有配置Configuration
        org.apache.ibatis.session.Configuration configuration = mappedStatement.getConfiguration();

//        获取当前查询语句的sql
        String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");

//        将sql改写成统计记录数的sql语句
        String sqlParma = pageConfig.getTotalSqlParam();
        String countSql = "select count(*) as total from (" + sql + ") " + sqlParma;
//        获取connection连接对象，用于执行countsql语句
        Connection conn = (Connection) invocation.getArgs()[0];
        PreparedStatement ps = null;
        int total = 0;

        try {
//            预编译统计总记录数的sql
            ps = conn.prepareStatement(countSql);
            //构建统计总记录数的BoundSql
            BoundSql countBoundSql = new BoundSql(configuration, countSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
            //构建ParameterHandler，用于设置统计sql的参数
            ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBoundSql);
            //设置总数sql的参数
            parameterHandler.setParameters(ps);
            //执行查询语句
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
//                与countSql中设置的别名对应
                total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (ps != null)
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return total;
    }

    //    设置条数参数到pageparam对象
    private void setTotltToParam(PageParam param, int totle, int pageSize) {
        param.setTotal(totle);
        param.setTotalPage(totle % pageSize == 0 ? totle / pageSize : (totle / pageSize) + 1);
    }

    //    修改原始sql语句为分页sql语句
    private Object updateSql2Limit(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, PageParam pageParam) throws InvocationTargetException, IllegalAccessException, SQLException {
        String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql");
        //构建新的分页sql语句
        String sqlParma = pageConfig.getSelectSqlParam(pageParam);
        String limitSql = "select * from (" + sql + ") " + sqlParma;
        //修改当前要执行的sql语句
        metaStatementHandler.setValue("delegate.boundSql.sql", limitSql);
        //相当于调用prepare方法，预编译sql并且加入参数
        PreparedStatement ps = (PreparedStatement) invocation.proceed();
        return ps;
    }

    //    验证当前页码的有效性
    private void checkPage(boolean checkFlag, Integer pageNumber, Integer pageTotle) throws Exception {
        if (checkFlag) {
            if (pageNumber > pageTotle) {
                throw new Exception("查询失败，查询页码" + pageNumber + "大于总页数" + pageTotle);
            }
            if (pageNumber < 0) {
                throw new Exception("查询失败，查询页码小于0 " + pageNumber );
            }
        }
    }
}

第五步：使用及测试

controller 按年龄查询employee

    @ApiOperation(value = "分页查询")
    @GetMapping("getEmployeeByPage")
    public ResultMsg getEmployeeByPage(Integer age,Integer pageNum,Integer paseSize){
        Map param = new HashMap<>();
        param.put("age",age);
        PageParam pageParam = new PageParam(pageNum,paseSize);
        param.put("pageParam", pageParam);
        List result = employeeMapper.getEmployeeByPage(param);
        return ResultMsg.getMsg(result,pageParam);
    }

注意：分页插件是通过判断查询参数是否有PageParam类型参数，来判断是否分页；所以如果要分页就传入一个pageParam的参数即可

Mapper 数据源为db2

    @DS("db2")
    List getEmployeeByPage(Map param);

XML

    <select id="getEmployeeByPage" parameterType="map" resultMap="BaseResultMap">
    select * from employee where age > #{age}
  </select>

打开swagger

返回结果如下：

{
  "data": [
    {
      "address": "北新街5lkgw",
      "age": "26",
      "createTime": 1562061284000,
      "deptId": "4",
      "gender": 1,
      "id": "318397755906347008",
      "name": "hkl2txf41c"
    },
    {
      "address": "北新街n5iaz",
      "age": "27",
      "createTime": 1562061285000,
      "deptId": "5",
      "gender": 1,
      "id": "318397756015398912",
      "name": "edz0ehjnrm"
    }
  ],
  "pageInfo": {
    "pageNum": 2,
    "pageSize": 4,
    "total": 6,
    "totalPage": 2
  },
  "result": "SUCCESS",
  "resultCode": 200,
  "resultMsg": ""
}
Response Code

log打印：

2019-09-20 16:12:23.243  INFO 12092 --- [nio-9393-exec-1] com.wg.demo.common.aop.LogAspect         : Request : {url='http://localhost:9393/mybatis/employee/getEmployeeByPage', ip='0:0:0:0:0:0:0:1', classMethod='com.wg.demo.controller.EmployeeController.getEmployeeByPage', args=[22, 1, 2]}
2019-09-20 16:12:23.243  INFO 12092 --- [nio-9393-exec-1] com.wg.demo.common.aop.LogAspect         : request Param: [22, 1, 2]
2019-09-20 16:12:23.250  INFO 12092 --- [nio-9393-exec-1] c.w.d.c.d.DynamicDataSourceAspect        : 当前数据源为db2
2019-09-20 16:12:23.263  INFO 12092 --- [nio-9393-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2019-09-20 16:12:23.478  INFO 12092 --- [nio-9393-exec-1] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2019-09-20 16:12:23.483  INFO 12092 --- [nio-9393-exec-1] c.w.d.c.interceptor.PageInterceptor      : Mybatis 分页插件当前数据源为: db2
2019-09-20 16:12:23.484  INFO 12092 --- [nio-9393-exec-1] c.w.d.c.interceptor.PageInterceptor      : Mybatis 分页插件当前数据源方言为: mysql
2019-09-20 16:12:23.484 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : ==>  Preparing: select count(*) as total from (select * from employee where age > ?) paging 
2019-09-20 16:12:23.525 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : ==> Parameters: 22(Integer)
2019-09-20 16:12:23.564 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : <==      Total: 1
2019-09-20 16:12:23.565 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : ==>  Preparing: select * from (select * from employee where age > ?) paging_table limit 0 , 2 
2019-09-20 16:12:23.566 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : ==> Parameters: 22(Integer)
2019-09-20 16:12:23.572 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : <==      Total: 2

mybatis sql log插件打印sql语句如下：

26  2019-09-20 16:12:23.525 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : ==>
select count(*) as total
 FROM (select *
 FROM employee
 WHERE age > 22) paging;
-----------------------------------------------------------------------------------------------------
27  2019-09-20 16:12:23.566 DEBUG 12092 --- [nio-9393-exec-1] c.w.d.d.E.getEmployeeByPage              : ==>
select *
 FROM (select *
 FROM employee
 WHERE age > 22) paging_table
 LIMIT 0 , 2;
-----------------------------------------------------------------------------------------------------

至此分页插件开发完毕，目前因为实际项目中我只用到了mysql、postgre、sqlserver这三种数据库，所以插件目前只支持这三种方言，需要其他方言支持的朋友可修改PageConfig类的getLimitParam及getSelectSqlParam两个方法，添加对应方言的sql语句拼装就可以了

源码地址，参见我的博客