[置顶] 对IBatis分页的改进,使ibatis支持hibernate式的物理分页

一直以来ibatis的分页都是通过滚动ResultSet实现的，应该算是逻辑分页吧。逻辑分页虽然能很干净地独立于特定数据库，但效率在多数情况下不及特定数据库支持的物理分页，而hibernate的分页则是直接组装sql，充分利用了特定数据库的分页机制，效率相对较高。本文讲述的就是如何在不重新编译ibatis源码的前提下，为ibatis引入hibernate式的物理分页机制。

基本思路就是找到ibatis执行sql的地方，截获sql并重新组装sql。通过分析ibatis源码知道，最终负责执行sql的类是 com.ibatis.sqlmap.engine.execution.SqlExecutor，此类没有实现任何接口，这多少有点遗憾，因为接口是相对稳定契约，非大的版本更新，接口一般是不会变的，而类就相对易变一些，所以这里的代码只能保证对当前版本（2.1.7）的ibatis有效。下面是 SqlExecutor执行查询的方法：

/**      * Long form of the method to execute a query      *      * @param request - the request scope      * @param conn - the database connection      * @param sql - the SQL statement to execute      * @param parameters - the parameters for the statement      * @param skipResults - the number of results to skip      * @param maxResults - the maximum number of results to return      * @param callback - the row handler for the query      *      * @throws SQLException - if the query fails      */      public void executeQuery(RequestScope request, Connection conn, String sql, Object[] parameters,                               int skipResults, int maxResults, RowHandlerCallback callback)          throws SQLException {        ErrorContext errorContext = request.getErrorContext();        errorContext.setActivity("executing query");        errorContext.setObjectId(sql);            PreparedStatement ps = null;        ResultSet rs = null;            try {          errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");              Integer rsType = request.getStatement().getResultSetType();          if (rsType != null) {            ps = conn.prepareStatement(sql, rsType.intValue(), ResultSet.CONCUR_READ_ONLY);          } else {            ps = conn.prepareStatement(sql);          }              Integer fetchSize = request.getStatement().getFetchSize();          if (fetchSize != null) {            ps.setFetchSize(fetchSize.intValue());          }              errorContext.setMoreInfo("Check the parameters (set parameters failed).");          request.getParameterMap().setParameters(request, ps, parameters);              errorContext.setMoreInfo("Check the statement (query failed).");              ps.execute();          rs = getFirstResultSet(ps);              if (rs != null) {            errorContext.setMoreInfo("Check the results (failed to retrieve results).");            handleResults(request, rs, skipResults, maxResults, callback);          }              // clear out remaining results          while (ps.getMoreResults());            } finally {          try {            closeResultSet(rs);          } finally {            closeStatement(ps);          }        }          }  

其中handleResults(request, rs, skipResults, maxResults, callback)一句用于处理分页，其实此时查询已经执行完毕，可以不必关心handleResults方法，但为清楚起见，下面来看看 handleResults的实现：

private void handleResults(RequestScope request, ResultSet rs, int skipResults, int maxResults, RowHandlerCallback callback) throws SQLException {        try {          request.setResultSet(rs);          ResultMap resultMap = request.getResultMap();          if (resultMap != null) {            // Skip Results            if (rs.getType() != ResultSet.TYPE_FORWARD_ONLY) {              if (skipResults > 0) {                rs.absolute(skipResults);              }            } else {              for (int i = 0; i < skipResults; i++) {                if (!rs.next()) {                  break;                }              }            }                // Get Results            int resultsFetched = 0;            while ((maxResults == SqlExecutor.NO_MAXIMUM_RESULTS || resultsFetched < maxResults) && rs.next()) {              Object[] columnValues = resultMap.resolveSubMap(request, rs).getResults(request, rs);              callback.handleResultObject(request, columnValues, rs);              resultsFetched++;            }          }        } finally {          request.setResultSet(null);        }      }  

此处优先使用的是ResultSet的absolute方法定位记录，是否支持absolute取决于具体数据库驱动，但一般当前版本的数据库都支持该方法，如果不支持则逐条跳过前面的记录。由此可以看出如果数据库支持absolute，则ibatis内置的分页策略与特定数据库的物理分页效率差距就在于物理分页查询与不分页查询在数据库中的执行效率的差距了。因为查询执行后读取数据前数据库并未把结果全部返回到内存，所以本身在存储占用上应该差距不大，如果都使用索引，估计执行速度也差不太多。

继续我们的话题。其实只要在executeQuery执行前组装sql，然后将其传给 executeQuery，并告诉handleResults我们不需要逻辑分页即可。拦截executeQuery可以采用aop动态实现，也可直接继承SqlExecutor覆盖executeQuery来静态地实现，相比之下后者要简单许多，而且由于SqlExecutor没有实现任何接口，比较易变，动态拦截反到增加了维护的工作量，所以我们下面来覆盖executeQuery：

package com.aladdin.dao.ibatis.ext;        import java.sql.Connection;    import java.sql.SQLException;        import org.apache.commons.logging.Log;    import org.apache.commons.logging.LogFactory;        import com.aladdin.dao.dialect.Dialect;    import com.ibatis.sqlmap.engine.execution.SqlExecutor;    import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;    import com.ibatis.sqlmap.engine.scope.RequestScope;        public class LimitSqlExecutor extends SqlExecutor {            private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);                private Dialect dialect;            private boolean enableLimit = true;            public Dialect getDialect() {            return dialect;        }            public void setDialect(Dialect dialect) {            this.dialect = dialect;        }            public boolean isEnableLimit() {            return enableLimit;        }            public void setEnableLimit(boolean enableLimit) {            this.enableLimit = enableLimit;        }            @Override        public void executeQuery(RequestScope request, Connection conn, String sql,                Object[] parameters, int skipResults, int maxResults,                RowHandlerCallback callback) throws SQLException {            if ((skipResults != NO_SKIPPED_RESULTS || maxResults != NO_MAXIMUM_RESULTS)                    && supportsLimit()) {                sql = dialect.getLimitString(sql, skipResults, maxResults);                if(logger.isDebugEnabled()){                    logger.debug(sql);                }                skipResults = NO_SKIPPED_RESULTS;                maxResults = NO_MAXIMUM_RESULTS;                        }            super.executeQuery(request, conn, sql, parameters, skipResults,                    maxResults, callback);        }            public boolean supportsLimit() {            if (enableLimit && dialect != null) {                return dialect.supportsLimit();            }            return false;        }        }  

其中：

skipResults = NO_SKIPPED_RESULTS;    maxResults = NO_MAXIMUM_RESULTS; 

告诉handleResults不分页（我们组装的sql已经使查询结果是分页后的结果了），此处引入了类似hibenate中的数据库方言接口Dialect，其代码如下：

package com.aladdin.dao.dialect;        public interface Dialect {                public boolean supportsLimit();            public String getLimitString(String sql, boolean hasOffset);            public String getLimitString(String sql, int offset, int limit);    }    

下面为Dialect接口的MySQL实现：

package com.aladdin.dao.dialect;        public class MySQLDialect implements Dialect {            protected static final String SQL_END_DELIMITER = ";";            public String getLimitString(String sql, boolean hasOffset) {            return new StringBuffer(sql.length() + 20).append(trim(sql)).append(                    hasOffset ? " limit ?,?" : " limit ?")                    .append(SQL_END_DELIMITER).toString();        }            public String getLimitString(String sql, int offset, int limit) {            sql = trim(sql);            StringBuffer sb = new StringBuffer(sql.length() + 20);            sb.append(sql);            if (offset > 0) {                sb.append(" limit ").append(offset).append(',').append(limit)                        .append(SQL_END_DELIMITER);            } else {                sb.append(" limit ").append(limit).append(SQL_END_DELIMITER);            }            return sb.toString();        }            public boolean supportsLimit() {            return true;        }            private String trim(String sql) {            sql = sql.trim();            if (sql.endsWith(SQL_END_DELIMITER)) {                sql = sql.substring(0, sql.length() - 1                        - SQL_END_DELIMITER.length());            }            return sql;        }        }    

接下来的工作就是把LimitSqlExecutor注入ibatis中。我们是通过spring来使用ibatis的，所以在我们的dao基类中执行注入，代码如下：

package com.aladdin.dao.ibatis;        import java.io.Serializable;    import java.util.List;        import org.springframework.orm.ObjectRetrievalFailureException;    import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;        import com.aladdin.dao.ibatis.ext.LimitSqlExecutor;    import com.aladdin.domain.BaseObject;    import com.aladdin.util.ReflectUtil;    import com.ibatis.sqlmap.client.SqlMapClient;    import com.ibatis.sqlmap.engine.execution.SqlExecutor;    import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;        public abstract class BaseDaoiBatis extends SqlMapClientDaoSupport {            private SqlExecutor sqlExecutor;            public SqlExecutor getSqlExecutor() {            return sqlExecutor;        }            public void setSqlExecutor(SqlExecutor sqlExecutor) {            this.sqlExecutor = sqlExecutor;        }            public void setEnableLimit(boolean enableLimit) {            if (sqlExecutor instanceof LimitSqlExecutor) {                ((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);            }        }            public void initialize() throws Exception {            if (sqlExecutor != null) {                SqlMapClient sqlMapClient = getSqlMapClientTemplate()                        .getSqlMapClient();                if (sqlMapClient instanceof ExtendedSqlMapClient) {                    ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)                            .getDelegate(), "sqlExecutor", SqlExecutor.class,                            sqlExecutor);                }            }        }            ...        }  

其中的initialize方法执行注入，稍后会看到此方法在spring Beans 配置中指定为init-method。由于sqlExecutor是 com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient的私有成员，且没有公开的set方法，所以此处通过反射绕过java的访问控制，下面是ReflectUtil的实现代码：

package com.aladdin.util;        import java.lang.reflect.Field;    import java.lang.reflect.Method;    import java.lang.reflect.Modifier;        import org.apache.commons.logging.Log;    import org.apache.commons.logging.LogFactory;        public class ReflectUtil {            private static final Log logger = LogFactory.getLog(ReflectUtil.class);            public static void setFieldValue(Object target, String fname, Class ftype,                Object fvalue) {            if (target == null                    || fname == null                    || "".equals(fname)                    || (fvalue != null && !ftype.isAssignableFrom(fvalue.getClass()))) {                return;            }            Class clazz = target.getClass();            try {                Method method = clazz.getDeclaredMethod("set"                        + Character.toUpperCase(fname.charAt(0))                        + fname.substring(1), ftype);                if (!Modifier.isPublic(method.getModifiers())) {                    method.setAccessible(true);                }                method.invoke(target, fvalue);                } catch (Exception me) {                if (logger.isDebugEnabled()) {                    logger.debug(me);                }                try {                    Field field = clazz.getDeclaredField(fname);                    if (!Modifier.isPublic(field.getModifiers())) {                        field.setAccessible(true);                    }                    field.set(target, fvalue);                } catch (Exception fe) {                    if (logger.isDebugEnabled()) {                        logger.debug(fe);                    }                }            }        }    }    

到此剩下的就是通过Spring将sqlExecutor注入BaseDaoiBatis中了，下面是Spring Beans配置文件：

<?xml version="1.0" encoding="UTF-8"?>    <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN"        "http://www.springframework.org/dtd/spring-beans.dtd">        <beans>        <!-- Transaction manager for a single JDBC DataSource -->        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">            <property name="dataSource">                <ref bean="dataSource" />            </property>        </bean>                <!-- SqlMap setup for iBATIS Database Layer -->        <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">            <property name="configLocation">                <value>classpath:/com/aladdin/dao/ibatis/sql-map-config.xml</value>            </property>            <property name="dataSource">                <ref bean="dataSource" />            </property>        </bean>            <bean id="sqlExecutor" class="com.aladdin.dao.ibatis.ext.LimitSqlExecutor">            <property name="dialect">                <bean class="com.aladdin.dao.dialect.MySQLDialect" />            </property>        </bean>                <bean id="baseDao" abstract="true" class="com.aladdin.dao.ibatis.BaseDaoiBatis" init-method="initialize">            <property name="dataSource">                <ref bean="dataSource" />            </property>            <property name="sqlMapClient">                <ref bean="sqlMapClient" />            </property>            <property name="sqlExecutor">                <ref bean="sqlExecutor" />            </property>         </bean>                 <bean id="userDao" class="com.aladdin.dao.ibatis.UserDaoiBatis" parent="baseDao" />             <bean id="roleDao" class="com.aladdin.dao.ibatis.RoleDaoiBatis" parent="baseDao" />                <bean id="resourceDao" class="com.aladdin.dao.ibatis.ResourceDaoiBatis" parent="baseDao" />            </beans>  

此后就可以通过调用org.springframework.orm.ibatis.SqlMapClientTemplate的

public List queryForList(final String statementName, final Object parameterObject, final int skipResults, final int maxResults) throws DataAccessException

或

public PaginatedList queryForPaginatedList(final String statementName, final Object parameterObject, final int pageSize) throws DataAccessException

得到分页结果了。建议使用第一个方法，第二个方法返回的是PaginatedList，虽然使用简单，但是其获得指定页的数据是跨过我们的dao直接访问ibatis的，不方便统一管理。