mysql物理分页逻辑分页_mybatis实现物理分页

这两两天由于工作需要，对mybatis分页进行研究，现进行记录，主要从一下两个方面阐述。博主用的数据库是oracle,所以以下分页都是针对oracle的，mybatis版本是3.3.0

一.mybatis自带分页功能的效率与物理分页的效率对比

1.mybatis自带分页功能

通过查找资料，mybatis分页是逻辑分页，如果数据量大会造成内存溢出.mybatis自带分页实现如下

dao层代码

public List 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 * from T_PROREC

其中RowBounds的源码为

红线部分为默认值 下文还会提到，RowBounds的两个属性offset,limit;

offset:从第n条开始取；limit:取多少条

2.物理分页

dao层代码

@Override

public List queryBySql(int startRow, int endRow) {

Map params =new HashMap();

params.put("startRow", new Integer(startRow));

params.put("endRow", new Integer(endRow));

return sqlSessionTemplate.selectList(QUERY_BY_SQL, params);

}

对应sql

select *

from (SELECT ROWNUM r, t1.*

from (

select * from T_PROREC ) t1

where rownum <= #{endRow})

where r >= #{startRow}

]]>

以上两种方式对比，本来以为mybatis效率会差，因为是查出所有数据，再取出相应的条数，但实际运行的结果是两种方式的差不多，没有太大的差异，不知道是不是因为T_PROREC表中数据太少的原因，表中大约有13000条数据。

二. mybatis 实现物理分页

博主采用了两种方式，一种是通过mybatis的include标签的应用，一种是通过mybatis的拦截器实现的

1.mybatis include标签

dao层代码

@Override

public List queryByMybatis(WorkflowRecord workflowRecord) {

return sqlSessionTemplate.selectList(QUERY_INCLUDE, workflowRecord);

}

传入对象包括startRow,endRow两个属性

对应的sql

select * from T_PROREC

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

/p>

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

where rownum <= #{endRow})

where r >= #{startRow}

]]>

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的监听器

配置完再调用下面dao层，拦截器会判断RowBounds是否为空，如果不为空，则重新构造新的sql，完成物理分页功能

public List 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);

}