mybatis实现自定义分页插件

一、环境搭建

创建一个maven工程，然后引入mybatis依赖和mysql依赖即可。

<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>3.0.4</version>
</dependency>
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.15</version>
</dependency>

pom文件中，还要引入如下插件，不然可能会报错：

  <build>
    <resources>
      <!-- resources文件 -->
      <resource>
        <directory>src/main/java</directory>
        <!-- 引入映射文件等 -->
        <includes>
          <include>**/*.xml</include>
        </includes>
      </resource>
    </resources>
  </build>

二、添加db.properties数据库配置

在【resources】目录下，新建【db.properties】文件。

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/ssm?serverTimezone=UTC&useSSL=false
username=root
password=root

三、添加mybatis-config.xml配置文件

在【resources】目录下，新建【mybatis-config.xml】配置文件。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- XML 配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
    <!-- 指定 MyBatis 数据库配置文件 -->
    <properties resource="db.properties" />
    <!-- 配置自定义的分页插件 -->
    <plugins>
        <!-- 自定义分页插件 -->
        <plugin interceptor="com.gitee.zhuyb.interceptor.PageInterceptor">
            <property name="dialect" value="mysql"/>
        </plugin>
        <!-- 结果集处理插件 -->
        <plugin interceptor="com.gitee.zhuyb.interceptor.ResultInterceptor">
        </plugin>
    </plugins>
    <!-- 数据库环境设置 -->
    <environments default="mysql">
        <!-- 环境配置，即连接的数据库。 -->
        <environment id="mysql">
            <!-- 事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->
            <transactionManager type="JDBC" />
            <!-- dataSource数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!-- 配置映射文件路径 -->
    <mappers>
        <!-- 配置UserMapper.xml映射文件路径 -->
        <mapper resource="com/gitee/zhuyb/mapper/SysUserMapper.xml" />
    </mappers>
</configuration>

四、创建PageVo分页对象

package com.gitee.zhuyb.domain;
 
import java.util.List;
 
public class PageVo {
    private Integer pageIndex;
    private Integer pageSize;
    private Integer pages;
    private Integer total;
    private List data;
 
    public PageVo(Integer pageIndex, Integer pageSize) {
        this.pageIndex = pageIndex;
        this.pageSize = pageSize;
    }
 
    public Integer getPageIndex() {
        return pageIndex;
    }
 
    public void setPageIndex(Integer pageIndex) {
        this.pageIndex = pageIndex;
    }
 
    public Integer getPageSize() {
        return pageSize;
    }
 
    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }
 
    public Integer getPages() {
        return pages;
    }
 
    public void setPages(Integer pages) {
        this.pages = pages;
    }
 
    public Integer getTotal() {
        return total;
    }
 
    public void setTotal(Integer total) {
        this.total = total;
    }
 
    public List getData() {
        return data;
    }
 
    public void setData(List data) {
        this.data = data;
    }
 
    @Override
    public String toString() {
        return "PageVo{" +
                "\n  pageIndex=" + pageIndex +
                "\n, pageSize=" + pageSize +
                "\n, pages=" + pages +
                "\n, total=" + total +
                "\n, data=" + data +
                "\n}";
    }
}

五、创建PageUtil工具类

PageUtil工具类中，通过线程局部变量保存PageVo对象。

package com.gitee.zhuyb.domain;
 
public class PageUtil {
    private static final ThreadLocal<PageVo> LOCAL_PAGE = new ThreadLocal<PageVo>();
 
    // 分页开始对象,设置PageVo
    public static PageVo start(PageVo pageVo) {
        LOCAL_PAGE.set(pageVo);
        return pageVo;
    }
 
    // 分页结束对象,可以获取带有结果集的PageVo
    public static PageVo end() {
        PageVo pageVo = PageUtil.getPageVo();
        LOCAL_PAGE.remove();
        return pageVo;
    }
 
    // 获取分页参数对象
    public static PageVo getPageVo() {
        return LOCAL_PAGE.get();
    }
}

六、创建PageInterceptor分页参数拦截器

mybatis自定义分页插件原理：通过拦截器将SQL执行语句拦截，然后拼接上分页语句，之后执行拼接完整的SQL语句即可。

package com.gitee.zhuyb.interceptor;
 
import com.gitee.zhuyb.domain.PageUtil;
import com.gitee.zhuyb.domain.PageVo;
import org.apache.ibatis.executor.parameter.DefaultParameterHandler;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.executor.statement.PreparedStatementHandler;
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.MetaObject;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

@Intercepts({@Signature(type=StatementHandler.class, method="prepare", args=Connection.class)})
public class PageInterceptor implements Interceptor {
 
    /** 数据库类型 */
    private String dialect;
 
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
        MetaObject delegateMetaObject =MetaObject.forObject(statementHandler);
        PreparedStatementHandler preparedStatementHandler = (PreparedStatementHandler)delegateMetaObject.getValue("delegate");
 
        BoundSql boundSql = preparedStatementHandler.getBoundSql();
        Object parameterObject = boundSql.getParameterObject();
        Connection connection = (Connection)invocation.getArgs()[0];
 
        // 获取分页参数
        PageVo pageVo = PageUtil.getPageVo();
        //如果开启了分页
        if(pageVo != null) {
            // 拼接分页参数
            String pageSql = this.getPageSql(boundSql.getSql(), pageVo);
            // 计算总记录数
            this.countTotal(pageVo, parameterObject, preparedStatementHandler, connection);
            // 设置新的sql
            MetaObject boundSqlMetaObject = MetaObject.forObject(boundSql);
            boundSqlMetaObject.setValue("sql", pageSql);
        }
        // 执行后续操作
        return invocation.proceed();
    }
 
    @Override
    public Object plugin(Object target) {
        // 设置代理对象
        return Plugin.wrap(target,this);
    }
 
    @Override
    public void setProperties(Properties properties) {
        // 设置属性
        this.dialect = properties.getProperty("dialect");
    }
 
 
    /********************************************************************/
 
    /**
     * #计算总记录和总分页数
     * @param pageVo
     * @param parameterObject
     * @param statementHandler
     * @param connection
     */
    private void countTotal(PageVo pageVo, Object parameterObject, PreparedStatementHandler statementHandler, Connection connection){
        MetaObject metaObject = MetaObject.forObject(statementHandler);
        MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("mappedStatement");
        BoundSql boundSql = statementHandler.getBoundSql();
        String sql = boundSql.getSql();
        // 获取统计SQL
        sql = this.getCountSql(sql);
        BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), sql, boundSql.getParameterMappings(), parameterObject);
 
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt =connection.prepareStatement(sql);
            parameterHandler.setParameters(pstmt);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int totalRecord = rs.getInt(1);
                pageVo.setTotal(totalRecord);  // 总记录数
                pageVo.setPages((totalRecord-1)/pageVo.getPageSize()+1); // 总页数
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pstmt != null)
                    pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * #获取分页sql
     * @param sql 拦截前的sql
     * @param pageVo 分页参数对象
     * @return
     */
    private String getPageSql(String sql, PageVo pageVo) {
        StringBuffer sqlBuffer = new StringBuffer(sql);
        if(dialect.equalsIgnoreCase("mysql")){
            return this.getMysqlPageSql(sqlBuffer, pageVo);
        }else if(dialect.equalsIgnoreCase("oralce")){
            return this.getOraclePageSql(sqlBuffer, pageVo);
        }else {
            return sqlBuffer.toString();
        }
    }
 
    /**
     * #获取统计sql,计算总记录数
     * @param sql 拦截前的sql
     * @return
     */
    private String getCountSql(String sql) {
        int beginIndex = sql.indexOf("from");
        sql = sql.substring(beginIndex);
        sql = "select count(1) " + sql;
        return sql;
    }
 
    /**
     * #获取mysql分页sql
     * @param sql 拦截前的sql
     * @param pageVo 分页参数
     * @return 返回分页的sql
     */
    private String getMysqlPageSql(StringBuffer sql, PageVo pageVo) {
        sql.append(" limit ")
                .append(pageVo.getPageIndex())
                .append(",")
                .append(pageVo.getPageSize());
        return sql.toString();
    }
 
    /**
     * #获取oracle分页sql
     * @param sql
     * @param pageVo
     * @return
     */
    private String getOraclePageSql(StringBuffer sql, PageVo pageVo) {
        int page = pageVo.getPageIndex();
        int size = pageVo.getPageSize();
        // 计算记录开始和结束索引
        int startIndex = (page - 1) * size;
        int endIndex = page * size;
        // 拼接小于的索引
        sql.insert(0, "select u.*, rownum r from (")
                .append(") u where rownum <= ")
                .append(endIndex);
        // 拼接大于的索引
        sql.insert(0, "select * from (")
                .append(") where r > ")
                .append(startIndex);
        return sql.toString();
    }
}

七、创建ResultInterceptor结果集拦截器

package com.gitee.zhuyb.interceptor;
 
import com.gitee.zhuyb.domain.PageUtil;
import com.gitee.zhuyb.domain.PageVo;
import org.apache.ibatis.executor.resultset.ResultSetHandler;
import org.apache.ibatis.plugin.*;
 
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@Intercepts({@Signature(args = { Statement.class }, method = "handleResultSets", type = ResultSetHandler.class)})
public class ResultInterceptor implements Interceptor {
 
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        List result = new ArrayList();
        // 获取PageVo对象
        PageVo pageVo = PageUtil.getPageVo();
        if (pageVo != null) {
            List<?> list = (List<?>) invocation.proceed();
            // 将查询结果设置到PageVo对象中
            pageVo.setData(list);
            result.add(pageVo);
        } else {
            result = (List)invocation.proceed();
        }
        return result;
    }
 
    @Override
    public Object plugin(Object target) {
        if(target instanceof ResultSetHandler){
            return Plugin.wrap(target, this);
        }else{
            return target;
        }
    }
 
    @Override
    public void setProperties(Properties properties) {
    }
}

八、测试分页效果

package com.gitee.zhuyb;
 
import com.gitee.zhuyb.domain.PageUtil;
import com.gitee.zhuyb.domain.PageVo;
import com.gitee.zhuyb.domain.SysUser;
import com.gitee.zhuyb.mapper.SysUserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestPage {
    public static void main( String[] args ) throws IOException {
        // 读取mybatis-config.xml文件
        InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        // 初始化mybatis，创建SqlSessionFactory类的实例
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        // 创建Session实例
        SqlSession session = sqlSessionFactory.openSession();
        // 获得mapper接口的代理对象
        SysUserMapper sysUserMapper = session.getMapper(SysUserMapper.class);
 
        // 开启分页
        PageVo pageVo = new PageVo(0, 2);
        PageUtil.start(pageVo);
 
        // 查询数据库
        SysUser sysUser = new SysUser();
        List<SysUser> sysUserLists = sysUserMapper.queryUser(sysUser);
 
        // 分页结束
        PageVo end = PageUtil.end();
        System.out.println("输出分页结果对象: " + end);
 
        // 提交事务
        session.commit();
        // 关闭Session
        session.close();
    }
}

运行TestPage类，查看结果如下：

项目代码

九、自定义mybatis的插件在springboot项目中配置

1.使用@Bean注入自定义的Plugin

在spring boot中可以使用如下代码进行注入：

@Configuration 
public class MyBatisConfiguration{
    @Bean 
    public SQLStatsInterceptor sqlStatsInterceptor(){ 
        SQLStatsInterceptor sqlStatsInterceptor = new SQLStatsInterceptor(); 
        Properties properties= new Properties(); 
        properties.setProperty("dialect","mysql"); 
        sqlStatsInterceptor.setProperties(properties); 
        return sqlStatsInterceptor;
    }
}

2.在spring中使用xml配置的方式的话，可以使用如下的方式进行注入：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- XML 配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
    <!-- 指定 MyBatis 数据库配置文件 -->
    <properties resource="db.properties" />
    <!-- 配置自定义的分页插件 -->
    <plugins>
        <!-- 自定义分页插件 -->
        <plugin interceptor="com.gitee.zhuyb.interceptor.PageInterceptor">
            <property name="dialect" value="mysql"/>
        </plugin>
        <!-- 结果集处理插件 -->
        <plugin interceptor="com.gitee.zhuyb.interceptor.ResultInterceptor">
        </plugin>
    </plugins>
    <!-- 数据库环境设置 -->
    <environments default="mysql">
        <!-- 环境配置，即连接的数据库。 -->
        <environment id="mysql">
            <!-- 事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->
            <transactionManager type="JDBC" />
            <!-- dataSource数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}" />
                <property name="url" value="${url}" />
                <property name="username" value="${username}" />
                <property name="password" value="${password}" />
            </dataSource>
        </environment>
    </environments>
    <!-- 配置映射文件路径 -->
    <mappers>
        <!-- 配置UserMapper.xml映射文件路径 -->
        <mapper resource="com/gitee/zhuyb/mapper/SysUserMapper.xml" />
    </mappers>
</configuration>

参考资料，官网

mybatis – MyBatis 3 | 配置