首页 > 其他分享 >使用Mybatis拦截器实现分页功能

使用Mybatis拦截器实现分页功能

时间:2022-12-03 19:46:42浏览次数:64  
标签:拦截器 return 分页 org boundSql sql Mybatis import page

  最近在做项目的时候,想要脱离Mybatis-Plus带来的便利,于是用回Mybatis,现在先记录下用Mybatis的拦截器来统一处理分页的简单方法。

一、定义下分页信息的包装类

import lombok.Data;

import java.util.List;
@Data
public class Page<T> {
    //数据库中符合条件的总记录数
    private int total;
    //当前页码(从1开始)
    private int pageNo;
    //页长
    private int pageLen;

    private List<T> records;
}

二、拦截器编写

1.我们需要改变查询的sql,拼上 limit ,所以可以把拦截类型设置成 StatementHandler,来拦截Sql语法构建的处理
@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class , Integer.class}) })
public class PageInterceptor implements Interceptor{

2.通过用来的sql生成统计记录数的sql

    public String getCountSql(String sql) {
        //不排序
        int end = StringUtil.indexOfIgnoreCase(sql, " order");
        sql = sql.substring(0,end);
        return "select count(0) from (" + sql + ") as total";
    }

    就是把原sql查出来的记录作为临时表,然后数统计数,'select * from tb_user' -> 'select (select * from tb_user) as total',开始我觉得可能回查出多余信息导致效率低,不过我用MySQL的explain工具看了下,MySQL会把sql进行优化,结果大概是这样:select count(0) from tb_user,那...看来或许问题不大?
3.通过原生成分页sql

    public String getPageSql(String sql, Page<?> page) {
        if(page.getPageNo()<=0){
            page.setPageNo(1);
        }
        if(page.getPageLen()<=0){
            page.setPageLen(SystemConfig.DEFAULT_PAGE_SIZE);
        }else if (page.getPageLen() > SystemConfig.MAX_PAGE_SIZE){
            //限制最大页长
            page.setPageLen(SystemConfig.MAX_PAGE_SIZE);
        }
        int startRow = (page.getPageNo()-1)*page.getPageLen();

        if(startRow>=page.getTotal()){
            page.setPageNo(1);
            startRow=0;
        }
        return sql+" limit "+startRow+", "+page.getPageLen();
    }

这里的SystemConfig是我自己定义的常量类
4.拦截器全部代码如下:

点击查看代码
package org.nefure.goodshops.interceptor;

import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.ReflectorFactory;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.factory.ObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.reflection.wrapper.ObjectWrapperFactory;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.nefure.goodshops.constant.SystemConfig;
import org.nefure.goodshops.dto.Page;
import org.nefure.goodshops.util.StringUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

@Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class , Integer.class}) })
public class PageInterceptor implements Interceptor{

    private static final Logger logger = LoggerFactory.getLogger(PageInterceptor.class);

    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    private static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY = new DefaultReflectorFactory();
    private static final String PAGE_KEY = "page";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 获得拦截的对象
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        // 待执行的sql的包装对象
        BoundSql boundSql = statementHandler.getBoundSql();
        // 判断是否是查询语句
        if (isSelect(boundSql.getSql())) {
            // 获得参数集合
            Object params = boundSql.getParameterObject();

            if (params instanceof Map) {
                // 请求为多个参数,参数采用Map封装
                return complexParamsHandler(invocation, boundSql, (Map<?, ?>) params);
            } else if (params instanceof Page) {
                // 单个参数且为Page,则表示该操作需要进行分页处理
                return simpleParamHandler(invocation, boundSql, (Page<?>) params);
            }
        }
        return invocation.proceed();
    }

    private Object complexParamsHandler(Invocation invocation, BoundSql boundSql, Map<?, ?> params) throws Throwable {
        //判断参数中是否指定分页
        if (containsPage(params)) {
            return pageHandlerExecutor(invocation, boundSql, (Page<?>) params.get("page"));
        } else {
            return invocation.proceed();
        }
    }

    private boolean containsPage(Map<?, ?> params) {
        if(params==null){
            return false;
        }else if(!params.containsKey(PAGE_KEY)){
            return false;
        }
        Object page = params.get(PAGE_KEY);
        if(page==null){
            return false;
        }else {
            return page instanceof Page;
        }
    }

    private boolean isSelect(String sql) {
        return StringUtils.hasText(sql) && sql.toUpperCase().trim().startsWith("SELECT");
    }

    private Object simpleParamHandler(Invocation invocation, BoundSql boundSql, Page<?> page) throws Throwable {
        return pageHandlerExecutor(invocation, boundSql, page);
    }

    private Object pageHandlerExecutor(Invocation invocation, BoundSql boundSql, Page<?> page) throws Throwable {
        // 获得数据库连接
        Connection connection = (Connection) invocation.getArgs()[0];
        // 使用Mybatis提供的MetaObject,该对象主要用于获取包装对象的属性值
        MetaObject statementHandler = MetaObject.forObject(invocation.getTarget(), DEFAULT_OBJECT_FACTORY,
                DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_REFLECTOR_FACTORY);

        // 获取该sql执行的结果集总数
        int maxSize = getTotalSize(connection, (MappedStatement) statementHandler.getValue("delegate.mappedStatement"),
                boundSql);

        // 生成分页sql
        page.setTotal(maxSize);
        String wrapperSql = getPageSql(boundSql.getSql(), page);

        MetaObject boundSqlMeta = MetaObject.forObject(boundSql, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,
                DEFAULT_REFLECTOR_FACTORY);
        // 修改boundSql的sql
        boundSqlMeta.setValue("sql", wrapperSql);
        return invocation.proceed();
    }

    private int getTotalSize(Connection connection, MappedStatement mappedStatement, BoundSql boundSql) {
        String countSql = getCountSql(boundSql.getSql());
        PreparedStatement countStmt;
        ResultSet rs;
        List<AutoCloseable> closeableList = new ArrayList<>();

        try {
            countStmt = connection.prepareStatement(countSql);
            BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql,
                    boundSql.getParameterMappings(), boundSql.getParameterObject());
            setParameters(countStmt, mappedStatement, countBoundSql, boundSql.getParameterObject());
            rs = countStmt.executeQuery();

            if (rs.next()) {
                return rs.getInt(1);
            }
            closeableList.add(countStmt);
            closeableList.add(rs);
        } catch (SQLException e) {
            logger.error("append an exception[{}] when execute sql[{}] with {}", e, countSql,
                    boundSql.getParameterObject());
        } finally {
            for (AutoCloseable closeable : closeableList) {
                try {
                    if (closeable != null) {
                        closeable.close();
                    }
                } catch (Exception e) {
                    logger.error("append an exception[{}] when close resource[{}] ", e, closeable);
                }
            }
        }
        return 0;
    }

    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
                               Object parameterObject) throws SQLException {
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
        parameterHandler.setParameters(ps);
    }

    @Override
    public Object plugin(Object target) {
        // 当目标类是StatementHandler类型时,才包装目标类,否者直接返回目标本身,减少目标被代理的次数
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {

    }

    public String getCountSql(String sql) {
        //不排序
        int end = StringUtil.indexOfIgnoreCase(sql, " order");
        sql = sql.substring(0,end);
        return "select count(0) from (" + sql + ") as total";
    }

    public String getPageSql(String sql, Page<?> page) {
        if(page.getPageNo()<=0){
            page.setPageNo(1);
        }
        if(page.getPageLen()<=0){
            page.setPageLen(SystemConfig.DEFAULT_PAGE_SIZE);
        }else if (page.getPageLen() > SystemConfig.MAX_PAGE_SIZE){
            //限制最大页长
            page.setPageLen(SystemConfig.MAX_PAGE_SIZE);
        }
        int startRow = (page.getPageNo()-1)*page.getPageLen();

        if(startRow>=page.getTotal()){
            page.setPageNo(1);
            startRow=0;
        }
        return sql+" limit "+startRow+", "+page.getPageLen();
    }
}

三、使用
1.配置到Spring
   在配置类里把它注册成bean就好了

@Configuration
public class MybatisConfig{

    @Bean
    public Interceptor pageInterceptor(){
        return new PageInterceptor();
    }

}

2.测试:

@SpringBootTest
@MapperScan("org.nefure.test.mapper")
public class BaseMapperTest {

    @Resource
    UserMapper userMapper;

    @Test
    void testPageInterceptor(){
        Page<User> pageInfo = new Page<>();
        pageInfo.setPageNo(5);
        pageInfo.setPageLen(20);
        List<User> users = userMapper.selectByPage(pageInfo);
        pageInfo.setRecords(users);
        System.out.println(pageInfo);
    }
}

结果如下:

Page(total=1010, pageNo=5, pageLen=10, records=[
User(id=41, [email protected], password=, nickName=user_3j9erfkl0p, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10),
User(id=42, [email protected], password=, nickName=user_l7rs56ah9y, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=43, [email protected], password=, nickName=user_p3655ctliy, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10),
User(id=44, [email protected], password=, nickName=user_qi1qze1yp1, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=45, [email protected], password=, nickName=user_vrd5ir0rj0, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=46, [email protected], password=, nickName=user_tubboh1byc, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=47, [email protected], password=, nickName=user_j2bdj3d2eo, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=48, [email protected], password=, nickName=user_ncj7r0vu1h, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=49, [email protected], password=, nickName=user_63rhqjqa0a, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10), 
User(id=50, [email protected], password=, nickName=user_80ue5cywnk, icon=, createTime=2022-02-28T10:50:47, updateTime=2022-11-24T21:57:10)
])

测试里是查第5页的数据,页长10条数据,查到41-50条,没有问题。
这些代码大多来自csdn上看到的大佬:
https://blog.csdn.net/lanhezhong/article/details/64907025

标签:拦截器,return,分页,org,boundSql,sql,Mybatis,import,page
From: https://www.cnblogs.com/nefure/p/16948633.html

相关文章