一
直以來ibatis的分頁都是通過滾動ResultSet實(shí)現(xiàn)的，應(yīng)該算是邏輯分頁吧。邏輯分頁雖然能很干凈地獨(dú)立于特定數(shù)據(jù)庫，但效率在多數(shù)情況下不及
特定數(shù)據(jù)庫支持的物理分頁，而hibernate的分頁則是直接組裝sql，充分利用了特定數(shù)據(jù)庫的分頁機(jī)制，效率相對較高。本文講述的就是如何在不重新
編譯ibatis源碼的前提下，為ibatis引入hibernate式的物理分頁機(jī)制。
基本思路就是找到ibatis執(zhí)行sql的地方，
截獲sql并重新組裝sql。通過分析ibatis源碼知道，最終負(fù)責(zé)執(zhí)行sql的類是
com.ibatis.sqlmap.engine.execution.SqlExecutor，此類沒有實(shí)現(xiàn)任何接口，這多少有點(diǎn)遺憾，因?yàn)榻涌谑窍?
對穩(wěn)定契約，非大的版本更新，接口一般是不會變的，而類就相對易變一些，所以這里的代碼只能保證對當(dāng)前版本（2.1.7）的ibatis有效。下面是
SqlExecutor執(zhí)行查詢的方法：
/**
* 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)一句用于處理分頁，其實(shí)此時(shí)查詢已經(jīng)執(zhí)行完畢，可以不必關(guān)心handleResults方法，但為清楚起見，下面來看看
handleResults的實(shí)現(xiàn)：
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);
}
}
此
處優(yōu)先使用的是ResultSet的absolute方法定位記錄，是否支持absolute取決于具體數(shù)據(jù)庫驅(qū)動，但一般當(dāng)前版本的數(shù)據(jù)庫都支持該方
法，如果不支持則逐條跳過前面的記錄。由此可以看出如果數(shù)據(jù)庫支持absolute，則ibatis內(nèi)置的分頁策略與特定數(shù)據(jù)庫的物理分頁效率差距就在于
物理分頁查詢與不分頁查詢在數(shù)據(jù)庫中的執(zhí)行效率的差距了。因?yàn)椴樵儓?zhí)行后讀取數(shù)據(jù)前數(shù)據(jù)庫并未把結(jié)果全部返回到內(nèi)存，所以本身在存儲占用上應(yīng)該差距不大，
如果都使用索引，估計(jì)執(zhí)行速度也差不太多。
繼續(xù)我們的話題。其實(shí)只要在executeQuery執(zhí)行前組裝sql，然后將其傳給
executeQuery，并告訴handleResults我們不需要邏輯分頁即可。攔截executeQuery可以采用aop動態(tài)實(shí)現(xiàn)，也可直接繼
承SqlExecutor覆蓋executeQuery來靜態(tài)地實(shí)現(xiàn)，相比之下后者要簡單許多，而且由于SqlExecutor沒有實(shí)現(xiàn)任何接口，比較易
變，動態(tài)攔截反到增加了維護(hù)的工作量，所以我們下面來覆蓋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已經(jīng)使查詢結(jié)果是分頁后的結(jié)果了），此處引入了類似hibenate中的數(shù)據(jù)庫方言接口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實(shí)現(xiàn)：
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基類中執(zhí)行注入，代碼如下：
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方法執(zhí)行注入，稍后會看到此方法在spring
Beans 配置中指定為init-method。由于sqlExecutor是
com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient的私有成員，且沒有公開的set方法，所以此處
通過反射繞過java的訪問控制，下面是ReflectUtil的實(shí)現(xiàn)代碼：
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>
此后就可以通過調(diào)用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
得到分頁結(jié)果了。建議使用第一個(gè)方法，第二個(gè)方法返回的是PaginatedList，雖然使用簡單，但是其獲得指定頁的數(shù)據(jù)是跨過我們的dao直接訪問ibatis的，不方便統(tǒng)一管理。