Mybatis使用篇之十二：实现物理分页

Mybatis的自带分页方法只是逻辑分页，如果数据量很大，内存会溢出，不知道为什么开源组织不在里面实现类似Hibernate的物理分页处理方法。在不改动Mybatis源代码的情况下，怎么使Mybatis支持物理分页呢？下面我们来看看。

 

（1）新建一个Java类Dialect.java，该类的内容如下：

package
 org.mybatis.extend.interceptor;

public
 
abstract
 
class
 Dialect {

    public
 
static
 
enum
 Type{
        MYSQL,
        ORACLE
    }

    public
 
abstract
 String getLimitString(String sql, 
int
 skipResults, 
int
 maxResults);

}

 

 

（2）新建一个Java类OracleDialect.java，该类继承Dialect 类，具体的内容如下：

package
 org.mybatis.extend.interceptor;

public
 
class
 OracleDialect 
extends
 Dialect{

    /* (non-Javadoc)
     * @see org.mybatis.extend.interceptor.IDialect#getLimitString(java.lang.String, int, int)
     */
    @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();
    }

}

 

（3）新建一个Mybaits的拦截器PaginationInterceptor.java，实现Interceptor接口，该类的内容如下：

package
 org.mybatis.extend.interceptor;

import
 java.sql.Connection;
import
 java.util.Map;
import
 java.util.Properties;

import
 org.apache.ibatis.executor.parameter.DefaultParameterHandler;
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.MetaObject;
import
 org.apache.ibatis.session.Configuration;
import
 org.apache.ibatis.session.RowBounds;
import
 org.slf4j.Logger;
import
 org.slf4j.LoggerFactory;


@Intercepts
({
@Signature
(type=StatementHandler.
class
,method=
"prepare"
,args={Connection.
class
})})
public
 
class
 PaginationInterceptor 
implements
 Interceptor {
    //日志对象
    protected
 
static
 Logger log = LoggerFactory.getLogger(PaginationInterceptor.
class
);
    /* (non-Javadoc)
     * @see org.apache.ibatis.plugin.Interceptor#intercept(org.apache.ibatis.plugin.Invocation)
     */
    @Override
    public
 Object intercept(Invocation invocation) 
throws
 Throwable {
        StatementHandler statementHandler = (StatementHandler)invocation.getTarget();
        MetaObject metaStatementHandler = MetaObject.forObject(statementHandler);

        RowBounds rowBounds = (RowBounds)metaStatementHandler.getValue("delegate.rowBounds"
);
        if
(rowBounds == 
null
 || rowBounds == RowBounds.DEFAULT){
            return
 invocation.proceed();
        }

        DefaultParameterHandler defaultParameterHandler = (DefaultParameterHandler)metaStatementHandler.getValue("delegate.parameterHandler"
);
        Map parameterMap = (Map)defaultParameterHandler.getParameterObject();
        Object sidx = parameterMap.get("_sidx"
);
        Object sord = parameterMap.get("_sord"
);

        String originalSql = (String)metaStatementHandler.getValue("delegate.boundSql.sql"
);

        if
(sidx != 
null
 && sord != 
null
){
            originalSql = originalSql + " order by "
 + sidx + 
" "
 + sord;
        }

        Configuration configuration = (Configuration)metaStatementHandler.getValue("delegate.configuration"
);

                            Dialect.Type databaseType  = null
;
        try
{
            databaseType = Dialect.Type.valueOf(configuration.getVariables().getProperty("dialect"
).toUpperCase());
        } catch
(Exception e){
            //ignore
        }
        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
;

        }


        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 );
        if
(log.isDebugEnabled()){
            BoundSql boundSql = statementHandler.getBoundSql();
            log.debug("生成分页SQL : "
 + boundSql.getSql());
        }
        return
 invocation.proceed();
    }

    /* (non-Javadoc)
     * @see org.apache.ibatis.plugin.Interceptor#plugin(java.lang.Object)
     */
    @Override
    public
 Object plugin(Object target) {
        return
 Plugin.wrap(target, 
this
);
    }

    /* (non-Javadoc)
     * @see org.apache.ibatis.plugin.Interceptor#setProperties(java.util.Properties)
     */
    @Override
    public
 
void
 setProperties(Properties arg0) {
        // TODO Auto-generated method stub

    }

}

 

 （4）将Mybatis的拦截器配置到Mybatis的全局配置文件(mybatis.cfg.xml)中，具体如下：

<?xml version=
"1.0"
 encoding=
"UTF-8"
 ?>

<!DOCTYPE configuration PUBLIC
    "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd"
>

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

    <plugins>
            <plugin interceptor="org.mybatis.extend.interceptor.PaginationInterceptor"
/>
    </plugins>

</configuration>

 

（5）使用方法同Mybatis逻辑分页一样，拦截器会自动拦截执行SQL的地方，加上分页代码：

getSqlSession().selectList(sqlId, paramMap,
new
 RowBounds(pageId, pageSize));