公司的大部分项目都开始使用IBatis作为O/R Mapping了,但是在使用的过程中也发现了很多不方便和存在争议的地方,其中一个不方便的地方就是分页,目前的处理方式都是在sqlMap中写针对特 定数据库的物理分页Sql语句,对于oracle数据库都是在分页的sql语句里面加上三层嵌套的sql语句,想了很多办法,都没能很好的避免这个问题, 无意间在javaeye上看到了《使ibatis支持hibernate式的物理分页》这篇文章,可点进去已经被删除了 ,幸好google了一下有很多人已经收藏了,这里转载一下
,以便再找不到了.
转载地址:http://www.blogjava.net/libin2722/articles/192504.html
一 直以来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);
- }
- }
- }
/** * 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 );
- }
- }
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 ;
- }
- }
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);
- }
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;
- }
- }
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);
- }
- }
- }
- ...
- }
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);
- }
- }
- }
- }
- }
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" />