Mybatis技术（三） 实现物理分页
Mybatis技术（三） 实现物理分页
博客分类：
[*]Mybatis技术内幕
Mybatis物理分页
Mybatis的自带分页方法只是逻辑分页，如果数据量很大，内存会溢出，不知道为什么开源组织不在里面实现类似Hibernate的物理分页处理方法。在不改动Mybatis源代码的情况下，怎么使Mybatis支持物理分页呢？下面我们来看看。
（1）新建一个Java类Dialect.java，该类的内容如下：
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 类，具体的内容如下：
Java代码
[*]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接口，该类的内容如下：
Java代码
[*]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)中，具体如下：
Java代码
[*]<?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的地方，加上分页代码：
Java代码
[*]getSqlSession().selectList(sqlId, paramMap,new RowBounds(pageId, pageSize));
页:
[1]