MyBatis 使用Interceptor进行分页(springMVC+mybatis(mysql))

Mybatis的分页功能很弱，它是基于内存的分页（查出所有记录再按偏移量和limit取结果），在大数据量的情况下这样的分页基本上是没有用的。本文基于插件，通过拦截StatementHandler重写sql语句，实现数据库的物理分页。

1.定义一个抽象类实现Mybatis的拦截器接口

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.List;import java.util.Map;import java.util.Properties;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.ibatis.executor.parameter.ParameterHandler;import org.apache.ibatis.executor.resultset.ResultSetHandler;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.Invocation;import org.apache.ibatis.plugin.Plugin;import org.apache.ibatis.reflection.MetaObject;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.apache.ibatis.session.RowBounds;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.page.vo.PageParams;public abstract class AbstractPagingInterceptor implements Interceptor {private Logger log = LoggerFactory.getLogger(AbstractPagingInterceptor.class); private static final Pattern PATTERN_SQL_BLANK = Pattern.compile("\\s+");private static final String FIELD_DELEGATE = "delegate";private static final String FIELD_BOUNDSQL = "boundSql";private static final String FIELD_ROWBOUNDS = "rowBounds";@SuppressWarnings("unused")private static final String FIELD_CONFIGURATION = "configuration";private static final String FIELD_MAPPEDSTATEMENT = "mappedStatement";private static final String FIELD_SQL = "sql";public static final String BLANK = " ";public static final String DOT = ".";public static final String SELECT = "select";public static final String FROM = "from";public static final String ORDER_BY = "order by";public static final String UNION = "union";public static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();public static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();@SuppressWarnings("rawtypes")@Overridepublic Object intercept(Invocation invocation) throws Throwable {Connection connection = (Connection) invocation.getArgs()[0];StatementHandler statementHandler = (StatementHandler) invocation.getTarget();BoundSql boundSql = statementHandler.getBoundSql();MetaObject metaStatementHandler = MetaObject.forObject(statementHandler, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY);RowBounds rowBounds = (RowBounds) metaStatementHandler.getValue(FIELD_DELEGATE + DOT + FIELD_ROWBOUNDS);  // "delegate.rowBounds"if (rowBounds == null || rowBounds == RowBounds.DEFAULT) {return invocation.proceed();}MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue(FIELD_DELEGATE + DOT + FIELD_MAPPEDSTATEMENT);// replace all blankString targetSql = replaceSqlBlank(boundSql.getSql());log.debug("生成分页SQL : " + boundSql.getSql()); // 查询总数//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++// pagingint total = getTotal(connection, mappedStatement, boundSql, targetSql);<span style="color:#ff0000;">//String pagingSql = getSelectPagingSql(targetSql , rowBounds.getOffset(), rowBounds.getLimit());Map param = (Map) boundSql.getParameterObject();PageParams pageParams = (PageParams) param.get("pageParams");String pagingSql = getSelectPagingSql(targetSql , pageParams.getOffset(), pageParams.getLimit());pageParams.setTotalDisplayRecords(total);</span>//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++log.debug("生成分页SQL : " + pagingSql); // 分页查询metaStatementHandler.setValue(FIELD_DELEGATE + DOT + FIELD_BOUNDSQL + DOT + FIELD_SQL, pagingSql);// ensure set to default/**  * RowBounds是MyBatis的逻辑分页类，上面pagingSql已经实现物理分页，RowBounds必须回到默认值，否则在sql取到的数据按RowBounds参数再[逻辑分页(即出去部分数据)]。<br> * RowBounds主要作用是，被用来传输分页值,如(mysql的offset/limit,oracle的start/length),实现物理分页，所以传输的目的达到，RowBounds变回初始值(即不对sql的返回结果再进行逻辑分页处理)。 * 总结：既然RowBounds被用来传输分页值，如果用RowBounds的话下面还要还原，这样可以改造成从参数中取BoundSql.getParameterObject()方便一些。总数total得想办法传回去 */<span style="color:#ff0000;">//metaStatementHandler.setValue(FIELD_DELEGATE + DOT + FIELD_ROWBOUNDS + DOT + "offset", RowBounds.NO_ROW_OFFSET);// "delegate.rowBounds.offset"//metaStatementHandler.setValue(FIELD_DELEGATE + DOT + FIELD_ROWBOUNDS + DOT + "limit", RowBounds.NO_ROW_LIMIT);// "delegate.rowBounds.limit"//metaStatementHandler.setValue(FIELD_DELEGATE + DOT + FIELD_ROWBOUNDS + DOT + "totalDisplayRecords", total);// "delegate.rowBounds.totalDisplayRecords"</span>return invocation.proceed();}// 查询总数private int getTotal(Connection connection, MappedStatement mappedStatement, BoundSql boundSql, String targetSql) {// 通过connection建立一个countSql对应的PreparedStatement对象。String countSQL = getSelectTotalSql(targetSql);// 参数的映射集合List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();// 我们查询传入的参数Object parameterObject = boundSql.getParameterObject();// 构建新的BoundSqlBoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSQL, parameterMappings, parameterObject);ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBoundSql);PreparedStatement pstmt = null;ResultSet rs = null;try {pstmt = connection.prepareStatement(countSQL);// 通过parameterHandler给PreparedStatement对象设置参数parameterHandler.setParameters(pstmt);// 之后就是执行获取总记录数的Sql语句和获取结果了。rs = pstmt.executeQuery();if (rs.next()) {int totalRecord = rs.getInt(1);// 给当前的参数page对象设置总记录数return totalRecord;}} catch (SQLException e) {log.error("分页错误请检查sql:"+targetSql);//抛出异常throw new RuntimeException(e.getMessage());} finally {try {if (rs != null) rs.close();if (pstmt != null) pstmt.close();} catch (SQLException e) {e.printStackTrace();}}return 0;}private String replaceSqlBlank(String originalSql) {Matcher matcher = PATTERN_SQL_BLANK.matcher(originalSql);return matcher.replaceAll(BLANK);}public Object plugin(Object target) {if (target instanceof StatementHandler|| target instanceof ResultSetHandler) {return Plugin.wrap(target, this);} else {return target;}}protected abstract String getSelectTotalSql(String targetSql);protected abstract String getSelectPagingSql(String targetSql, int offset, int limit);@Overridepublic void setProperties(Properties paramProperties) {}}

2.具体实现

import java.sql.Connection;import org.apache.ibatis.executor.statement.StatementHandler;import org.apache.ibatis.plugin.Intercepts;import org.apache.ibatis.plugin.Signature;@Intercepts(@Signature(type=StatementHandler.class,method="prepare",args={Connection.class}))public class MysqlPagingInterceptor extends AbstractPagingInterceptor {@Overrideprotected String getSelectTotalSql(String targetSql) {StringBuilder sb = new StringBuilder();sb.append("SELECT COUNT(1) AS COUNT FROM( ").append(targetSql).append(" ) X");return sb.toString();}@Overrideprotected String getSelectPagingSql(String targetSql, int offset, int limit) {String sql = targetSql.toLowerCase();StringBuilder sqlBuilder = new StringBuilder(sql);sqlBuilder.append(" LIMIT ").append(" "+offset).append(" , ").append(limit+" ");return sqlBuilder.toString();}}

3.PageBounds and PageParams

public class PageBounds extends RowBounds {/** 总记录数 */private int totalDisplayRecords;public PageBounds(){}public PageBounds(int offset, int limit) {super(offset, limit);}public int getTotalDisplayRecords() {return totalDisplayRecords;}public void setTotalDisplayRecords(int totalDisplayRecords) {this.totalDisplayRecords = totalDisplayRecords;}}public class PageParams implements Serializable{/** serialVersionUID */private static final long serialVersionUID = 2210877431550440660L;/** 总记录数 */private int totalDisplayRecords;/**  */private int offset;/**  */private int limit;public PageParams() {this.offset = RowBounds.NO_ROW_OFFSET;this.limit = RowBounds.NO_ROW_LIMIT;}public PageParams(int offset, int limit) {this.offset = offset;this.limit = limit;}public int getTotalDisplayRecords() {return totalDisplayRecords;}public void setTotalDisplayRecords(int totalDisplayRecords) {this.totalDisplayRecords = totalDisplayRecords;}public int getOffset() {return offset;}public void setOffset(int offset) {this.offset = offset;}public int getLimit() {return limit;}public void setLimit(int limit) {this.limit = limit;}}

4.mybatis-config.xml配置

<configuration><properties><property name="dialect" value="mysql" /></properties><!-- 配置mybatis的缓存，延迟加载等等一系列属性 --><settings><!-- 全局映射器启用缓存 --><setting name="cacheEnabled" value="true" /><!-- 查询时，关闭关联对象即时加载以提高性能 --><setting name="lazyLoadingEnabled" value="true" /><!-- 设置关联对象加载的形态，此处为按需加载字段(加载字段由SQL指 定)，不会加载关联表的所有字段，以提高性能 --><setting name="aggressiveLazyLoading" value="false" /><!-- 等等 --></settings><plugins><span style="color:#ff0000;"><plugin interceptor="com.page.myBatisInterceptor.MysqlPagingInterceptor" /></span></plugins></configuration> 

5.调用

@Servicepublic class PageUtil {private Logger log = LoggerFactory.getLogger(PageUtil.class);@Autowiredprivate SqlSessionFactory sqlSessionFactory;//private SqlSessionFactory getSqlSession(String sql) {//if (StringUtils.isEmpty(sql)) {//throw new NullPointerException("SQL为空");//}//return sqlSessionFactory;//}@SuppressWarnings("rawtypes")public Map<String, Object> getPageInfoByMap(String sql, Map<String, Object> param) throws Exception {log.debug("分页处理开始");if(StringUtils.isEmpty(param.get("start")) || StringUtils.isEmpty(param.get("length"))){throw new Exception("缺失分页参数start或者length。");}int start = Integer.parseInt(param.get("start").toString());int length = Integer.parseInt(param.get("length").toString());<span style="color:#cc0000;">//PageBounds rowBounds = new PageBounds(start, length); // 改造前用PageParams pageParams = new PageParams(start, length);param.put("pageParams", pageParams);</span>SqlSession sqlSession = sqlSessionFactory.openSession();List list = null;try{<span style="color:#ff0000;">list = sqlSession.selectList(sql, param, new RowBounds()); // 改造前用 PageBounds</span>sqlSession.commit();if (list == null) {/** new一个list防止web端报错 **/list = new ArrayList();}} catch (Exception e) {e.printStackTrace();} finally{sqlSession.close();}// returnMap<String, Object> result = new HashMap<String, Object>();result.put("offset", start);result.put("limit", length);<span style="color:#cc0000;">result.put("totalDisplayRecords", pageParams.getTotalDisplayRecords());</span>result.put("data", list);return result;}}