mybatis实现物理分页

所谓覅破解

  这两两天由于工作需要，对mybatis分页进行研究，现进行记录，主要从一下两个方面阐述。博主用的数据库是oracle,所以以下分页都是针对oracle的，mybatis版本是3.3.0
  一.mybatis自带分页功能的效率与物理分页的效率对比
1.mybatis自带分页功能
通过查找资料，mybatis分页是逻辑分页，如果数据量大会造成内存溢出.mybatis自带分页实现如下
dao层代码
 

public List<WorkflowRecord> queryByMybatis(int startRow, int endRow) {
RowBounds rowBounds = new RowBounds(startRow,endRow);
System.out.println("rowBounds Offset:"+rowBounds.getOffset());
System.out.println("rowBounds Limit:"+rowBounds.getLimit());
WorkflowRecord workflowRecord=new WorkflowRecord();
return sqlSessionTemplate.selectList(QUERY_BY_MYBYTAIS,workflowRecord,rowBounds);
}
 对应的sql为
 

<select id="queryByMybatis" resultMap="proRecResult">
select * from T_PROREC   
</select>
 其中RowBounds的源码为
 


 红线部分为默认值 下文还会提到，RowBounds的两个属性offset,limit;
offset:从第n条开始取；limit:取多少条
 
2.物理分页
 
dao层代码
 

@Override
public List<WorkflowRecord> queryBySql(int startRow, int endRow) {
Map<String, Object> params =new HashMap<String, Object>();
params.put("startRow", new Integer(startRow));   
params.put("endRow", new Integer(endRow));
return sqlSessionTemplate.selectList(QUERY_BY_SQL, params);
}
 对应sql
 
 

<select id="queryBySql" resultMap="proRecResult">
select *
from (SELECT ROWNUM r, t1.*
from (
select * from T_PROREC ) t1
<![CDATA[
where rownum <= #{endRow})
where r >= #{startRow}
]]>
</select>
 
 
以上两种方式对比，本来以为mybatis效率会差，因为是查出所有数据，再取出相应的条数，但实际运行的结果是两种方式的差不多，没有太大的差异，不知道是不是因为T_PROREC表中数据太少的原因，表中大约有13000条数据。
 
二. mybatis 实现物理分页
  博主采用了两种方式，一种是通过mybatis的include标签的应用，一种是通过mybatis的拦截器实现的
1.mybatis include标签
 
dao层代码
 

@Override
public List<WorkflowRecord> queryByMybatis(WorkflowRecord workflowRecord) {
return sqlSessionTemplate.selectList(QUERY_INCLUDE, workflowRecord);
}
传入对象包括startRow,endRow两个属性
 
对应的sql
 
 

<select id="queryInclude" resultMap="proRecResult">

<include refid="com.hdnav.projectlibrary.entity.sqlMappers.pagination.paginationCommon1"/>
select * from T_PROREC
<include refid="com.hdnav.projectlibrary.entity.sqlMappers.pagination.paginationCommon2"/>

</select>
 博主是将分页共同的代码单独的写在一另个sqlMapper里，方便所有的sqlMapper文件引用，代码如下
 
 

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hdnav.projectlibrary.entity.sqlMappers.pagination">

<sql id="paginationCommon1" >
<![CDATA[ select *  from (SELECT ROWNUM r, t1.* from ( ]]>               
</sql>
<sql id="paginationCommon2" >
<![CDATA[ ) t1
where rownum <= #{endRow})
where r >= #{startRow}
]]>

</sql>

</mapper>
 
 
2.mybatis拦截器实现
 
博主是参照这两篇文章实现的
http://www.cnblogs.com/jcli/archive/2011/08/09/2132222.html
http://chenjc-it.iyunv.com/blog/1402965
感谢以上两位博主
 
下面这篇文章是讲原理的，我只是实现了功能但没有仔细研究原理，大家可以看下
http://haohaoxuexi.iyunv.com/blog/1851081
 
下面贴我实现的代码
类Dialect
 

public abstract class Dialect {
public static enum Type{
MYSQL,
ORACLE
}
public abstract String getLimitString(String sql, int skipResults, int maxResults);
}
 类OracleDialect
 
 

public class OracleDialect extends Dialect{
@Override
public String getLimitString(String sql, int offset, int limit) {
sql = sql.trim();
StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
pagingSelect.append(sql);
pagingSelect.append(" ) row_ ) where rownum_ > ").append(offset).append(" and rownum_ <= ").append(offset + limit);
return pagingSelect.toString();
}
}
 
类PaginationInterceptor（mybatis监听器类）

package com.hdnav.common;
import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.ReflectorFactory;
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.session.Configuration;
import org.apache.ibatis.session.RowBounds;
import org.apache.logging.log4j.Logger;


@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class})})
public class PaginationInterceptor implements Interceptor{
private static Logger logger = KJPTUtils.getLogger();
private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
private static final ReflectorFactory DEFAULT_REFLECTOR_FACTORY= new DefaultReflectorFactory();
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
MetaObject metaStatementHandler = MetaObject.forObject(statementHandler,  
DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY,DEFAULT_REFLECTOR_FACTORY);

RowBounds rowBounds = (RowBounds)metaStatementHandler.getValue("delegate.rowBounds");
if(rowBounds == null || rowBounds == RowBounds.DEFAULT){
return invocation.proceed();
}
String originalSql = (String)metaStatementHandler.getValue("delegate.boundSql.sql");
Configuration configuration = (Configuration)metaStatementHandler.getValue("delegate.configuration");
Dialect.Type databaseType  =null;
try {
databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect").toUpperCase());
} catch (Exception e) {
}
if(databaseType == null){
throw new RuntimeException("the value of the dialect property in configuration.xml is not defined : "+ configuration.getVariables().getProperty("dialect"));
}
Dialect dialect =null;
switch(databaseType){
case ORACLE:
dialect = new OracleDialect();
break;
case MYSQL://需要实现MySQL的分页逻辑
break;
}
//改别要执行的sql语句，现在新设置的sql语句是物理分页的，所以现在不再需要mybatis进行额外的操作了，所以把rowBounds的偏移量恢复为初始值(offet:0,limit:Integer.max)
metaStatementHandler.setValue("delegate.boundSql.sql", dialect.getLimitString(originalSql, rowBounds.getOffset(), rowBounds.getLimit()) );
metaStatementHandler.setValue("delegate.rowBounds.offset", RowBounds.NO_ROW_OFFSET );
metaStatementHandler.setValue("delegate.rowBounds.limit", RowBounds.NO_ROW_LIMIT );
BoundSql boundSql = statementHandler.getBoundSql();
logger.info("生成分页SQL : " + boundSql.getSql());
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {

}
}

 

 恢复到上文提到的RowBounds的默认值
 
mybatis.xml代码，配置mybatis的监听器

<properties>
<property name="dialect" value="oracle"/>
</properties>
 

<plugins>
<plugin interceptor="com.hdnav.common.PaginationInterceptor"></plugin>
</plugins>
 
配置完再调用下面dao层，拦截器会判断RowBounds是否为空，如果不为空，则重新构造新的sql，完成物理分页功能 

public List<WorkflowRecord> queryByMybatis(int startRow, int endRow) {
RowBounds rowBounds = new RowBounds(startRow,endRow);
System.out.println("rowBounds Offset:"+rowBounds.getOffset());
System.out.println("rowBounds Limit:"+rowBounds.getLimit());
WorkflowRecord workflowRecord=new WorkflowRecord();
return sqlSessionTemplate.selectList(QUERY_BY_MYBYTAIS,workflowRecord,rowBounds);
}