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>
参考资料，官网
http://www.mybatis.org/mybatis-3/zh/configuration.html#plugins
