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

蔚落 2024-04-20 09:06 46阅读 0赞

单数据源的分页没什么说的用现成的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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JkcXhfMDA3_size_16_color_FFFFFF_t_70]  
返回结果如下：

{ 
      "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语句拼装就可以了

[源码地址，参见我的博客][Link 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JkcXhfMDA3_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/590a17bf9ce94fe2896fb614295ba357.png
[Link 1]: http://wanggang.io/blog/14