MyBatis拦截器（插件）分页
1、MyBatis核心配置文件：
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-config.dtd"> <configuration> <!-- 设置别名 --> <typeAliases> <!-- 2. 指定扫描包，会把包内所有的类都设置别名，别名的名称就是类名，大小写不敏感 --> <package name="com.sgl.entity" /> </typeAliases> <plugins> <plugin interceptor="com.sgl.interceptor.PageInterceptor"> <property name="limit" value="10"/> <property name="dbType" value="mysql"/> </plugin> </plugins> </configuration>
PageInfo.java
package com.sgl.interceptor; import java.util.List; import com.alibaba.fastjson.JSONObject; public class PageInfo { private int pageIndex;// 当前页 private int pageSize = 10;// 每页记录数 private int totalCount;// 总记录数 private int pageCount;// 总页数 private boolean hasPre;// 是否首页,true表示不是首页 private boolean hasNext;// 是否尾页,true表示不是尾页 private JSONObject filter;// 查询过滤条件 private List<?> results;// 结果集 public PageInfo() { super(); } public PageInfo(int pageIndex, int pageSize) { super(); this.pageIndex = pageIndex; this.pageSize = pageSize; } public int getPageIndex() { return pageIndex; } public void setPageIndex(int pageIndex) { this.pageIndex = pageIndex; } public int getPageSize() { return pageSize; } public void setPageSize(int pageSize) { this.pageSize = pageSize; } public int getTotalCount() { return totalCount; } public void setTotalCount(int totalCount) { this.totalCount = totalCount; } public int getPageCount() { return totalCount%pageSize==0?(totalCount/pageSize):(totalCount/pageSize+1); } public boolean getHasPre(){ return (pageIndex>1); } public boolean getHasNext(){ return (pageIndex<getPageCount()); } public JSONObject getFilter() { return filter; } public void setFilter(JSONObject filter) { this.filter = filter; } public List<?> getResults() { return results; } public void setResults(List<?> results) { this.results = results; } @Override public String toString() { return "PageInfo [pageIndex=" + pageIndex + ", pageSize=" + pageSize + ", totalCount=" + totalCount + ", pageCount=" + pageCount + ", hasPre=" + hasPre + ", hasNext=" + hasNext + ", filter=" + filter + ", results=" + results + "]"; } }
2、简单例子：
package com.sgl.interceptor; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.text.DateFormat; import java.util.Date; import java.util.List; import java.util.Locale; import java.util.Properties; 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.mapping.ParameterMapping; import org.apache.ibatis.plugin.Interceptor; import org.apache.ibatis.plugin.Intercepts; import org.apache.ibatis.plugin.Invocation; import org.apache.ibatis.plugin.Plugin; import org.apache.ibatis.plugin.Signature; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.session.Configuration; import org.apache.ibatis.type.TypeHandlerRegistry; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @Intercepts({ @Signature(type = StatementHandler.class, method = "prepare", args = { Connection.class, Integer.class }) }) public class PageInterceptor implements Interceptor { private static Logger logger = LoggerFactory.getLogger(PageInterceptor.class); private static final String SELECT_ID = "selectpage"; @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); Connection connection = (Connection) invocation.getArgs()[0]; // 获取statementHandler包装类 MetaObject statmentHandlerObject = SystemMetaObject.forObject(statementHandler); // 获取操作数据库接口映射的相关信息 MappedStatement mappedStatement = (MappedStatement) statmentHandlerObject.getValue("delegate.mappedStatement"); ParameterHandler parameterHandler = (ParameterHandler) statmentHandlerObject .getValue("delegate.parameterHandler"); String id = mappedStatement.getId(); String sql = statementHandler.getBoundSql().getSql();// 获取执行的sql语句 Object param = statementHandler.getBoundSql().getParameterObject();// 获取sql中的参数 // 对id为selectPage的查询进行分页处理 if (SELECT_ID.equals(id.substring(id.lastIndexOf(".") + 1).toLowerCase())) { // 获取分页参数 PageInfo pageInfo = (PageInfo) parameterHandler.getParameterObject(); int pageIndex = pageInfo.getPageIndex(); int pageSize = pageInfo.getPageSize(); // 获取查询总记录数 int totalCount = getTotalCount(mappedStatement, statementHandler, connection); pageInfo.setTotalCount(totalCount); // 拼接分页查询sql语句 String limitSql = sql.trim() + " limit " + (pageIndex - 1) * pageSize + " , " + pageSize; logger.info("执行的sql[方法]：=====》{}", id); logger.info("执行的sql[语句]：=====》{}", limitSql.replaceAll("[\\s]+", " ")); logger.info("执行的sql[参数]：=====》{}", param); // 将构建完成的分页sql语句赋值个体'delegate.boundSql.sql'，替换原来的sql语句 statmentHandlerObject.setValue("delegate.boundSql.sql", limitSql); } else { logger.info("执行的sql[方法]：=====》{}", id); logger.info("执行的sql[语句]：=====》{}", sql.replaceAll("[\\s]+", " ")); logger.info("执行的sql[参数]：=====》{}", param); } return invocation.proceed(); } // 拦截类型StatementHandler @Override public Object plugin(Object object) { if (object instanceof StatementHandler) { return Plugin.wrap(object, this); } else { return object; } } @Override public void setProperties(Properties properties) { } // 获取查询的总记录数 private int getTotalCount(MappedStatement mappedStatement, StatementHandler statementHandler, Connection connection) { StringBuffer sb = new StringBuffer(); sb.append("SELECT COUNT(1) as count FROM (" + showSql(mappedStatement, statementHandler) + ") t "); PreparedStatement preparedStatement = null; ResultSet resultSet = null; int totalCount = 0; try { preparedStatement = connection.prepareStatement(sb.toString()); resultSet = preparedStatement.executeQuery(); if (resultSet.next()) { totalCount = resultSet.getInt(1); } } catch (Exception e) { logger.info("分页查询获取总记录数发生异常====》{}", e.getMessage()); } finally { try { if (resultSet != null) { resultSet.close(); } if (preparedStatement != null) { preparedStatement.close(); } } catch (Exception e2) { logger.info("关闭资源异常====>{}" + e2.getMessage()); } } return totalCount; } /** * 把sql语句中的?替换成实际参数值，并返回sql语句 * * @return */ private String showSql(MappedStatement mappedStatement, StatementHandler statementHandler) { Configuration configuration = mappedStatement.getConfiguration(); BoundSql boundSql = statementHandler.getBoundSql(); String sql = boundSql.getSql().replaceAll("[\\s]+", " "); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); Object parameterObject = boundSql.getParameterObject(); if (parameterMappings.size() > 0 && parameterObject != null) { // 获取基础类型处理器注册器 TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {// 判断参数类型是否已注册（判断是否是基本参数类型） sql = sql.replaceFirst("\\?", getParameterValue(parameterObject)); } else { MetaObject metaObject = configuration.newMetaObject(parameterObject); for (ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); if (metaObject.hasGetter(propertyName)) { Object value = metaObject.getValue(propertyName); sql = sql.replaceFirst("\\?", getParameterValue(value)); } else if (boundSql.hasAdditionalParameter(propertyName)) { Object additionalParameter = boundSql.getAdditionalParameter(propertyName); sql = sql.replaceFirst("\\?", getParameterValue(additionalParameter)); } } } } return sql; } /** * 对参数值进行分类处理 * * @param object * @return */ private String getParameterValue(Object object) { String value = null; if (object instanceof String) { value = "'" + object.toString() + "'"; } else if (object instanceof Date) { DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); value = "'" + dateFormat.format(new Date()) + "'"; } else { if (object != null) { value = object.toString(); } else { value = ""; } } return value; } }
3、原理解析：
//注解拦截器并签名 @Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class,Integer.class})})
method="prepare"和StatementHandler服务类中prepare方法相对应。
Statement prepare(Connection connection, Integer transactionTimeout) throws SQLException;
自定义的插件类，都需要使用@Intercepts注解，@Signature是对插件需要拦截的对象进行签名，type表示要拦截的类型，method表示拦截类中的方法，args是需要的参数，这里的参数在后面也可以获取到。
StatementHandler：数据库会话器，专门用于处理数据库会话，statement的执行操作，是一个接口。
MetaObject：mybatis工具类，可以有效的读取或修改一些重要对象的属性，基本思想是通过反射去获取和设置对象的属性值，只是MetaObject类不需要我们自己去实现具体反射的方法，已经封装好了。
通过MetaObject.getValue()和MetaObject.setValue(name,value)方法去获取对象属性值和设置对象属性值。
String sql = (String) metaObjectHandler.getValue("delegate.boundSql.sql");
metaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
通过MetaObject属性的获取流程：
MappedStatement mappedStatement = (MappedStatement) metaObjectHandler.getValue("delegate.mappedStatement");
metaObjectHandler是一个MetaObject对象。
首先通过metaStatementHandler.getValue(“delegate”)拿到真正实现StatementHandler接口的服务对象。
public class RoutingStatementHandler implements StatementHandler { //delegate属性来自这里，是一个实现了StatementHandler接口的类 private final StatementHandler delegate; //通过这里给delegate属性赋值 public RoutingStatementHandler(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) { switch(RoutingStatementHandler.SyntheticClass_1.$SwitchMap$org$apache$ibatis$mapping$StatementType[ms.getStatementType().ordinal()]) { case 1: this.delegate = new SimpleStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql); break; case 2: this.delegate = new PreparedStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql); break; case 3: this.delegate = new CallableStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql); break; default: throw new ExecutorException("Unknown statement type: " + ms.getStatementType()); } } }
拿到具体的服务对象（处理逻辑的StatementHandler实现类）后，再获取mappedStatement属性，我们再来看mappedStatement属性的定义：
public abstract class BaseStatementHandler implements StatementHandler { protected final Configuration configuration; protected final ObjectFactory objectFactory; protected final TypeHandlerRegistry typeHandlerRegistry; protected final ResultSetHandler resultSetHandler; protected final ParameterHandler parameterHandler; protected final Executor executor; //定义在这里 protected final MappedStatement mappedStatement; protected final RowBounds rowBounds; protected BoundSql boundSql; }
可以看出是定义在BaseStatementHandler中的属性，三个具体的服务对象都会继承BaseStatementHandler。这里有很多和执行数据库操作相关的属性，如果我们需要的话，都可以通过上述方式获取，如果相获取下层对象的属性，按照这个写法一次获取也可以拿到。
RoutingStatementHandler：不是真正的服务对象，它通过适配器模式找到正确的StatementHandler去执行操作。通过invocation.getTarget()获取到的是一个RoutingStatementHandler代理对象，再通过MappedStatement中不同的类型，找到具体的处理类。
真正实现StatementHandler接口的服务对象有：SimpleStatementHandler，PreparedStatementHandler，CallableStatementHandler都继承BaseStatementHandler，它们分别对应是三种不同的执行器：SIMPLE：默认的简单执行器；REUSE：重用预处理语句的执行期；BATCH：重用语句和批量更新处理器。
BoundSql: 用于组装SQL和参数，使用插件时需要通过它拿到当前运行的SQL和参数及参数规则。它有如下几个重要属性：
public class BoundSql { private String sql; private List<ParameterMapping> parameterMappings; private Object parameterObject; private Map<String, Object> additionalParameters; private MetaObject metaParameters; }
parameterObject：是参数本身，调用方法时传递进来的参数。可以是pojo，map或@param注解的参数等。
parameterMappings：它是一个List，存储了许多ParameterMapping对象。这个对象会描述我们的参数，参数包括属性、名称、表达式、javaType、jdbcType等。
sql：我们书写在mapper.xml文件中的一条sql语句。
MappedStatement:存储mapper.xml文件中一条sql语句配置的所有信息。
Connection:连接对象，在插件中会依赖它去进行一些数据库操作。
Configuration:包含mybatis所有的配置信息。
ParameterHandler:接口，对预编译语句进行参数设置。即将参数设置到sql语句中。它有两个重要方法：getParameterObject()用于获取参数对象和
setParameters(PreparedStatement var1)用于设置参数对象。
在插件中我们使用了一个辅助类，来封装分页时会用到的一些参数，定义如下：
/** * description:实现分页的辅助类，用于封装用于分页的一些参数 */ public class PageParam { private Integer defaultPage; // 默认每页显示条数 private Integer defaultPageSize; // 是否启用分页功能 private Boolean defaultUseFlag; // 是否检测当前页码的合法性（大于最大页码或小于最小页码都不合法） private Boolean defaultCheckFlag; //当前sql查询的总记录数，回填 private Integer totle; // 当前sql查询实现分页后的总页数，回填 private Integer totlePage; ...（省略get和set方法）
当需要使用到分页功能时，我们只需要将分页参数封装到PageParam对象中，并且作为参数传递到查询方法中，插件中就会自动获取到这些参数，并且动态组分页的Sql查询语句。下面就是我们自定义的分页插件类实现：
import org.apache.ibatis.executor.parameter.ParameterHandler; import org.apache.ibatis.executor.statement.RoutingStatementHandler; 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 org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.scripting.defaults.DefaultParameterHandler; import javax.security.auth.login.Configuration; import java.lang.reflect.InvocationTargetException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Map; import java.util.Properties; /** * description:插件分页 */ @Intercepts(@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})) public class PageInterceptor implements Interceptor { // 默认页码 private Integer defaultPage; // 默认每页显示条数 private Integer defaultPageSize; // 是否启用分页功能 private boolean defaultUseFlag; // 检测当前页码的合法性（大于最大页码或小于最小页码都不合法） private boolean defaultCheckFlag; @Override public Object intercept(Invocation invocation) throws Throwable { StatementHandler statementHandler = getActuralHandlerObject(invocation); MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler); String sql = statementHandler.getBoundSql().getSql(); // 检测未通过，不是select语句 if (!checkIsSelectFalg(sql)) { return invocation.proceed(); } BoundSql boundSql = statementHandler.getBoundSql(); Object paramObject = boundSql.getParameterObject(); PageParam pageParam = getPageParam(paramObject); if (pageParam == null) return invocation.proceed(); Integer pageNum = pageParam.getDefaultPage() == null ? defaultPage : pageParam.getDefaultPage(); Integer pageSize = pageParam.getDefaultPageSize() == null ? defaultPageSize : pageParam.getDefaultPageSize(); Boolean useFlag = pageParam.isDefaultUseFlag() == null ? defaultUseFlag : pageParam.isDefaultUseFlag(); Boolean checkFlag = pageParam.isDefaultCheckFlag() == null ? defaultCheckFlag : pageParam.isDefaultCheckFlag(); //不使用分页功能 if (!useFlag) { return invocation.proceed(); } int totle = getTotle(invocation, metaStatementHandler, boundSql); //将动态获取到的分页参数回填到pageParam中 setTotltToParam(pageParam, totle, pageSize); //检查当前页码的有效性 checkPage(checkFlag, pageNum, pageParam.getTotlePage()); //修改sql return updateSql2Limit(invocation, metaStatementHandler, boundSql, pageNum, pageSize); } @Override public Object plugin(Object o) { return Plugin.wrap(o, this); } // 在配置插件的时候配置默认参数 @Override public void setProperties(Properties properties) { String strDefaultPage = properties.getProperty("default.page"); String strDefaultPageSize = properties.getProperty("default.pageSize"); String strDefaultUseFlag = properties.getProperty("default.useFlag"); String strDefaultCheckFlag = properties.getProperty("default.checkFlag"); defaultPage = 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; } /* 获取分页的参数 参数可以通过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 getTotle(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语句,这里是mysql的改写语句,将第一次查询结果作为第二次查询的表 String countSql = "select count(*) as totle from (" + sql + ") $_paging"; // 获取connection连接对象，用于执行countsql语句 Connection conn = (Connection) invocation.getArgs()[0]; PreparedStatement ps = null; int totle = 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中设置的别名对应 totle = rs.getInt("totle"); } } catch (SQLException e) { e.printStackTrace(); } finally { if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } return totle; } // 设置条数参数到pageparam对象 private void setTotltToParam(PageParam param, int totle, int pageSize) { param.setTotle(totle); param.setTotlePage(totle % pageSize == 0 ? totle / pageSize : (totle / pageSize) + 1); } // 修改原始sql语句为分页sql语句 private Object updateSql2Limit(Invocation invocation, MetaObject metaStatementHandler, BoundSql boundSql, int page, int pageSize) throws InvocationTargetException, IllegalAccessException, SQLException { String sql = (String) metaStatementHandler.getValue("delegate.boundSql.sql"); //构建新的分页sql语句 String limitSql = "select * from (" + sql + ") $_paging_table limit ?,?"; //修改当前要执行的sql语句 metaStatementHandler.setValue("delegate.boundSql.sql", limitSql); //相当于调用prepare方法，预编译sql并且加入参数，但是少了分页的两个参数，它返回一个PreparedStatement对象 PreparedStatement ps = (PreparedStatement) invocation.proceed(); //获取sql总的参数总数 int count = ps.getParameterMetaData().getParameterCount(); //设置与分页相关的两个参数 ps.setInt(count - 1, (page - 1) * pageSize); ps.setInt(count, pageSize); return ps; } // 验证当前页码的有效性 private void checkPage(boolean checkFlag, Integer pageNumber, Integer pageTotle) throws Exception { if (checkFlag) { if (pageNumber > pageTotle) { throw new Exception("查询失败，查询页码" + pageNumber + "大于总页数" + pageTotle); } } } }
