mybatis实现物理分页 -

mzl1989325

浏览: 9404 次

最近访客更多访客>>

dinoli

yingmu3630

java_projects

liuyouming

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

mybatis实现物理分页

博客分类：

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.iteye.com/blog/1402965

感谢以上两位博主

下面这篇文章是讲原理的，我只是实现了功能但没有仔细研究原理，大家可以看下

http://haohaoxuexi.iteye.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);
	}