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")
	@Override
	public 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 blank
		String targetSql = replaceSqlBlank(boundSql.getSql());

		log.debug("生成分页SQL : " + boundSql.getSql()); // 查询总数
		
		//++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		// paging
		int 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();
		// 构建新的BoundSql
		BoundSql 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);
	
	@Override
	public 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 {

	@Override
	protected String getSelectTotalSql(String targetSql) {
		StringBuilder sb = new StringBuilder();
		
		sb.append("SELECT COUNT(1) AS COUNT FROM( ")
		.append(targetSql).append(" ) X");
		
		return sb.toString();
	}

	@Override
	protected 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.调用

@Service
public class PageUtil {
	
	private Logger log = LoggerFactory.getLogger(PageUtil.class);
	
	@Autowired
	private 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();
		}

		// return
		Map<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;
	}
}