ibatis的物理分页

   最近研究了下ibatis支持的物理分页，貌似大家都爱好物理分页，对逻辑分页不怎么看好，主要是由于数据量很大的时候，逻辑分页太耗时间啦，物理分页充分发挥其作用，其实在大多数情况下（90%）都是用的物理分页。

   ibatis 带有专门的分页执行器，只要继承该sql执行器--->  com.ibatis.sqlmap.engine.execution.SqlExecutor  就

可以实现分页了。废话不多说，直接上代码

   下面是ibatis的SQL执行器代码

     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; } @Override 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 = NO_SKIPPED_RESULTS; maxResults = NO_MAXIMUM_RESULTS; } super.executeQuery(statementScope, conn, sql, parameters, skipResults, maxResults, callback); } public boolean supportsLimit() { if (enableLimit && dialect != null) { return dialect.supportsLimit(); } return false; }

 

 现在定义数据库方言：

    public interface Dialect { public boolean supportsLimit(); public String getLimitString(String sql, boolean hasOffset); public String getLimitString(String sql, int offset, int limit); } 

 

方言的实现类(本例以mysql为例)：
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; } }

通过以上3个类就可以实现ibatis的物理分页，在执行 查询的时候调用  List<ProductModel> lst = sqlMapClient.queryForList("pv_product_info.queryProduct", param, skipResults, maxResults);

 

param :查询条件
skipResults： 查询的起始位置
maxResults：每页显示的行数

注： 转载请附上原文链接，谢谢