ibatis支持hibernate式的物理分页.doc

对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执行查询的方法：Java代码 1. /* 2. *Longformofthemethodtoexecuteaquery 3. * 4. *paramrequest-therequestscope 5. *paramconn-thedatabaseconnection 6. *paramsql-theSQLstatementtoexecute 7. *paramparameters-theparametersforthestatement 8. *paramskipResults-thenumberofresultstoskip 9. *parammaxResults-themaximumnumberofresultstoreturn 10. *paramcallback-therowhandlerforthequery 11. * 12. *throwsSQLException-ifthequeryfails 13. */14. publicvoidexecuteQuery(RequestScoperequest,Connectionconn,Stringsql,Objectparameters, 15. intskipResults,intmaxResults,RowHandlerCallbackcallback) 16. throwsSQLException 17. ErrorContexterrorContext=request.getErrorContext(); 18. errorContext.setActivity(executingquery); 19. errorContext.setObjectId(sql); 20. 21. PreparedStatementps=null; 22. ResultSetrs=null; 23. 24. try 25. errorContext.setMoreInfo(ChecktheSQLStatement(preparationfailed).); 26. 27. IntegerrsType=request.getStatement().getResultSetType(); 28. if(rsType!=null) 29. ps=conn.prepareStatement(sql,rsTValue(),ResultSet.CONCUR_READ_ONLY); 30. else 31. ps=conn.prepareStatement(sql); 32. 33. 34. IntegerfetchSize=request.getStatement().getFetchSize(); 35. if(fetchSize!=null) 36. ps.setFetchSize(fetchSValue(); 37. 38. 39. errorContext.setMoreInfo(Checktheparameters(setparametersfailed).); 40. request.getParameterMap().setParameters(request,ps,parameters); 41. 42. errorContext.setMoreInfo(Checkthestatement(queryfailed).); 43. 44. ps.execute(); 45. rs=getFirstResultSet(ps); 46. 47. if(rs!=null) 48. errorContext.setMoreInfo(Checktheresults(failedtoretrieveresults).); 49. handleResults(request,rs,skipResults,maxResults,callback); 50. 51. 52. /clearoutremainingresults 53. while(ps.getMoreResults(); 54. 55. finally 56. try 57. closeResultSet(rs); 58. finally 59. closeStatement(ps); 60. 61. 62. 63. /* * 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, rsTValue(), ResultSet.CONCUR_READ_ONLY); else ps = conn.prepareStatement(sql); Integer fetchSize = request.getStatement().getFetchSize(); if (fetchSize != null) ps.setFetchSize(fetchSValue(); 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的实现：Java代码 1. privatevoidhandleResults(RequestScoperequest,ResultSetrs,intskipResults,intmaxResults,RowHandlerCallbackcallback)throwsSQLException 2. try 3. request.setResultSet(rs); 4. ResultMapresultMap=request.getResultMap(); 5. if(resultMap!=null) 6. /SkipResults 7. if(rs.getType()!=ResultSet.TYPE_FORWARD_ONLY) 8. if(skipResults0) 9. rs.absolute(skipResults); 10. 11. else 12. for(inti=0;iskipResults;i+) 13. if(!rs.next() 14. break; 15. 16. 17. 18. 19. /GetResults 20. intresultsFetched=0; 21. while(maxResults=SqlExecutor.NO_MAXIMUM_RESULTS|resultsFetched 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 0) 18. sb.append(limit).append(offset).append(,).append(limit) 19. .append(SQL_END_DELIMITER); 20. else 21. sb.append(limit).append(limit).append(SQL_END_DELIMITER); 22. 23. returnsb.toString(); 24. 25. 26. publicbooleansupportsLimit() 27. returntrue; 28. 29. 30. privateStringtrim(Stringsql) 31. sql=sql.trim(); 32. if(sql.endsWith(SQL_END_DELIMITER) 33. sql=sql.substring(0,sql.length()-134. -SQL_END_DELIMITER.length(); 35. 36. returnsql; 37. 38. 39. 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基类中执行注入，代码如下：Java代码 1. packagecom.aladdin.dao.ibatis; 2. 3. importjava.io.Serializable; 4. importjava.util.List; 5. 6. importorg.springframework.orm.ObjectRetrievalFailureException; 7. importorg.springframework.orm.ibatis.support.SqlMapClientDaoSupport; 8. 9. importcom.aladdin.dao.ibatis.ext.LimitSqlExecutor; 10. importcom.aladdin.domain.BaseObject; 11. importcom.aladdin.util.ReflectUtil; 12. importcom.ibatis.sqlmap.client.SqlMapClient; 13. importcom.ibatis.sqlmap.engine.execution.SqlExecutor; 14. importcom.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient; 15. 16. publicabstractclassBaseDaoiBatisextendsSqlMapClientDaoSupport 17. 18. privateSqlExecutorsqlExecutor; 19. 20. publicSqlExecutorgetSqlExecutor() 21. returnsqlExecutor; 22. 23. 24. publicvoidsetSqlExecutor(SqlExecutorsqlExecutor) 25. this.sqlExecutor=sqlExecutor; 26. 27. 28. publicvoidsetEnableLimit(booleanenableLimit) 29. if(sqlExecutorinstanceofLimitSqlExecutor) 30. (LimitSqlExecutor)sqlExecutor).setEnableLimit(enableLimit); 31. 32. 33. 34. publicvoidinitialize()throwsException 35. if(sqlExecutor!=null) 36. SqlMapClientsqlMapClient=getSqlMapClientTemplate() 37. .getSqlMapClient(); 38. if(sqlMapClientinstanceofExtendedSqlMapClient) 39. ReflectUtil.setFieldValue(ExtendedSqlMapClient)sqlMapClient) 40. .getDelegate(),sqlExecutor,SqlExecutor.class, 41. sqlExecutor); 42. 43. 44. 45. 46. . 47. 48. 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).setEnableLi