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;
}
}