spring3.2+ibatis2.3.4.726 不改jar实现物理分页机制，不影响其他查询。

由于项目组要换技术，初来乍到，第一次接触ibatis，使用过程中发现ibatis的分页机制不是太好，是把该表的所有数据全部load出来然后通过java去把传的页数截出来return出来。这样会影响一些查询性能，通过看了ibatis执行sql的类是SqlExecutor，但它没有实现任何接口，这样是有点硬伤了，接下来我们使用spring注入方法的方式来实现hibernate方言机制物理分页。

第一个类：Dialect 接口，实现各种数据库分页方式，用Oracle做例子

public interface Dialect {
 /**
  * 是否分页
  * @return 
  * @author lim
  */
 public boolean supportsLimit();
 /**
  * 从什么位置开始
  * @param sql
  * @param hasOffset
  * @return 
  * @author lim
  */
 public String getLimitString(String sql, boolean hasOffset);
 /**
  * 实现物理分页
  * @param sql
  * @param offset 开始
  * @param limit 结束
  * @return sql
  * @author lim
  */
 public String getLimitString(String sql, int offset, int limit);
}

 

第二个类：OracleDialect 用Oracle做例子，MySql的话可以自己继承Dialect进行扩展

public class OracleDialect implements Dialect {
 protected static final String SQL_END_DELIMITER = ";";
 /**
  * sql组装分页
  * @param sql
  * @param offset
  * @param limit
  * @return 
  */
 public String getLimitString(String sql, int offset, int limit) {
  sql = trim(sql);
  StringBuffer sb = new StringBuffer(sql.length() + 20);
  sb.append("select * from ( select sq.*, rownum as rn from (");
  sb.append(this.trim(sql));
  sb.append(") sq)row_limit where row_limit.rn between ");
  sb.append(limit * (offset-1) + 1);
  sb.append(" and ");
  sb.append(limit * offset);
  return sb.toString();
 }
 public boolean supportsLimit() {
  return true;
 }
 
 /**
  * 去掉分号
  * @param sql
  * @return 
  * @author lim
  */
 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;
 }
}

 

接下来，我们使用一个类继承SqlExecutor覆盖他的executeQuery方法进行物理分页

import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.ibatis.sqlmap.engine.execution.SqlExecutor;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.StatementScope;
 
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;
 }
 
 /**
  * 重写ibatis执行sql方法
  * @param statementScope
  * @param conn
  * @param sql
  * @param parameters
  * @param skipResults
  * @param maxResults
  * @param callback
  * @throws SQLException 
  * @see com.ibatis.sqlmap.engine.execution.SqlExecutor#executeQuery(com.ibatis.sqlmap.engine.scope.StatementScope, java.sql.Connection, java.lang.String, java.lang.Object[], int, int, com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback)
  */
 public void executeQuery(StatementScope statementScope, 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为SqlExecutor不分页   设置maxResults为SqlExecutor不分页 */
   skipResults = NO_SKIPPED_RESULTS;
   maxResults = NO_MAXIMUM_RESULTS;
   System.out.println(sql);
  }
  super.executeQuery(statementScope, conn, sql, parameters, skipResults,
    maxResults, callback);
 }
 /**
  * @return 
  * @author lim
  */
 public boolean supportsLimit() {
  if (enableLimit && dialect != null) {
   return dialect.supportsLimit();
  }
  return false;
 }
}

第四个类：ReflectUtil 用于spring方法注入util

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);
    }
   }
  }
 }
 public static Object getFieldValue(Object target, String fname) {
  Object reslut = null;
  if (target == null || fname == null || "".equals(fname)) {
   return null;
  }
  Class clazz = target.getClass();
  try {
   Field field = clazz.getDeclaredField(fname);
   reslut = field.get(fname);
  } catch (Exception me) {
   if (logger.isDebugEnabled()) {
    logger.debug(me);
   }
  }
  return reslut;
 }
}

 

 

第五个类：BaseDaoiBatis

public abstract class BaseDaoiBatis {
 
 /** ibatis执行sql类 */
 @Resource
 private SqlExecutor sqlExecutor;
 
 /**  */
 @Resource
 protected SqlMapClient sqlMapClient;
 
 /**
  * 把dialect注入到ibatis的sqlExecutor
  * @throws Exception 
  * @author lim
  */
 @PostConstruct
 public void initialize() throws Exception {
  if (sqlExecutor != null) {
   if (sqlMapClient instanceof ExtendedSqlMapClient) {
    ReflectUtil.setFieldValue(((ExtendedSqlMapClient) sqlMapClient)
      .getDelegate(), "sqlExecutor", SqlExecutor.class,
      sqlExecutor);
   }
  }
 }
 
 public void setEnableLimit(boolean enableLimit) {
  if (sqlExecutor instanceof LimitSqlExecutor) {
   ((LimitSqlExecutor) sqlExecutor).setEnableLimit(enableLimit);
  }
 }
}

 

好了，这样启动的时候调用BaseDaoIBatis的initialize()方法将我们的SqlExecutor注入到ibatis就可以了会自动覆盖ibatis的executeQuery方法

接下来是spring的注入

 

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation" value="classpath:config/deploy/ibatis-conf.xml" />
  <property name="dataSource" ref="dataSource" />
 </bean>
 <bean id="sqlExecutor" class="cn.com.topinfo.exam.code.ibatis.LimitSqlExecutor">
  <property name="dialect">
   <bean class="cn.com.topinfo.exam.code.ibatis.OracleDialect" />
  </property>
 </bean>

 

用的时候我们的BaseDaoImpl只需要继承BaseDaoIBatis就可以了，用sqlMapClient去实现对数据库的操作