ibatis 分页“终极”解决方案

参考http://blog.csdn.net/zhuangyan2004/archive/2007/02/01/1499915.aspx
加上自己的修改，支持分页查询，返回分页的具体信息，比如页数，当前页，结果集。

其中加入一个PageInfo类，并修改了其他的4个类。
附源码

package com.littleqworks.commons.db.ibatis;

import java.util.List;

public class PageInfo {
        public static final int NUMBERS_PER_PAGE = 10;
        // 一页显示的记录数
        private int numPerPage=NUMBERS_PER_PAGE;
        // 记录总数
        private int totalRows;
        // 总页数
        private int totalPages;
        // 当前页码
        private int currentPage;
        // 起始行数
        private int startIndex;
        // 结束行数
        private int lastIndex;
        // 结果集存放List
        private List resultList;


     

        public int getCurrentPage() {
            return currentPage;
        }

        public void setCurrentPage(int currentPage) {
            this.currentPage = currentPage;
        }

        public int getNumPerPage() {
            return numPerPage;
        }

        public void setNumPerPage(int numPerPage) {
            this.numPerPage = numPerPage;
        }

        public List getResultList() {
            return resultList;
        }

        public void setResultList(List resultList) {
            this.resultList = resultList;
        }

        public int getTotalPages() {
            setTotalPages();
            return totalPages;
        }

        // 计算总页数
        public void setTotalPages() {
            if(totalRows % numPerPage == 0){
                this.totalPages = totalRows / numPerPage;
                }else{
                    this.totalPages= (totalRows / numPerPage) + 1;
                }
        }

        public int getTotalRows() {
            return totalRows;
        }

        public void setTotalRows(int totalRows) {
            this.totalRows = totalRows;
        }

        public int getStartIndex() {
            setStartIndex();
            return startIndex;
        }

        public void setStartIndex() {
            this.startIndex = (currentPage - 1) * numPerPage;
        }
       
   
        public int getLastIndex() {
            setLastIndex();
            return lastIndex;
        }

        // 计算结束时候的索引

        public void setLastIndex() {
            if( totalRows < numPerPage){
                this.lastIndex = totalRows;
            }else if((totalRows % numPerPage == 0)
                    || (totalRows % numPerPage != 0 && currentPage < totalPages)){
                this.lastIndex = currentPage * numPerPage;
            }else if(totalRows % numPerPage != 0 && currentPage == totalPages){
                // 最后一页
                this.lastIndex = totalRows ;
            }

        }

}
//************************************************************************************
package com.littleqworks.commons.db.ibatis;

public interface Dialect {
   
    public boolean supportsLimit();

    public String getLimitString(String sql, boolean hasOffset);

    public String getLimitString(String sql, int offset, int limit);
   
    public String getCountSqlString(String sql);
}
//************************************************************************************
package com.littleqworks.commons.db.ibatis;

public class MySQLDialect implements Dialect {

    protected static final String SQL_END_DELIMITER = ";";
    public static final String RS_COLUMN = "nums";

    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 String getCountSqlString(String sql){
        sql = trim(sql);
        StringBuffer sb = new StringBuffer(sql.length() + 10);
        if(sql.toLowerCase().startsWith("select")){
            int i=sql.indexOf(" ");
            int j=sql.lastIndexOf("from");
           
            sb.append(sql.subSequence(0, i));
            sb.append(" ");
            sb.append("count(*) as ");
            sb.append(RS_COLUMN);
            sb.append(" ");
            sb.append(sql.subSequence(j, sql.length()));
        }
        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.littleqworks.commons.db.ibatis;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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.impl.ExtendedSqlMapClient;
import com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.ErrorContext;
import com.ibatis.sqlmap.engine.scope.RequestScope;
import com.ibatis.sqlmap.engine.scope.SessionScope;

public class LimitSqlExecutor extends SqlExecutor {

    private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);
   
    private Dialect dialect;
   
    private int totalRowsCount;
   
   

    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()) {
            int rowsCount = 0;
            String csql = dialect.getCountSqlString(sql);
            if(parameters!=null){
                    rowsCount = executeQuery(request, conn, csql, callback);                   
                    setTotalRowsCount(rowsCount);
            }
           

            if(skipResults>=rowsCount){
                skipResults=rowsCount-skipResults;
            }
            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);
    }

    private int executeQuery(RequestScope request, Connection conn, String sql,
            RowHandlerCallback callback) {
        int rowsCount = 0;
        ErrorContext errorContext = request.getErrorContext();
        errorContext.setActivity("executing query procedure");
        errorContext.setObjectId(sql);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
              errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
//              Integer rsType = request.getStatement().getResultSetType();
              ps = prepareStatement(request.getSession(), conn, sql);
             
              errorContext.setMoreInfo("Check the parameters (set parameters failed).");
              ps.execute();
              errorContext.setMoreInfo("Check the results (failed to retrieve results).");
              rs = ps.getResultSet();
              // Begin ResultSet Handling             
              if(rs!=null){
                  while(rs.next()){
                      rowsCount=rs.getInt(MySQLDialect.RS_COLUMN);
                  }
              }
   
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                  rs.close();
                } catch (SQLException e) {
                  // ignore
                }
              }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                  // ignore
                }
              }
        }
        return rowsCount;
    }

    public boolean supportsLimit() {
        if (enableLimit && dialect != null) {
            return dialect.supportsLimit();
        }
        return false;
    }

    private static PreparedStatement prepareStatement(SessionScope session,
            Connection conn, String sql) throws SQLException {
        SqlMapExecutorDelegate delegate = ((ExtendedSqlMapClient) session
                .getSqlMapExecutor()).getDelegate();
        if (session.hasPreparedStatementFor(sql)) {
            return session.getPreparedStatement((sql));
        } else {
            PreparedStatement ps = conn.prepareStatement(sql);
            session.putPreparedStatement(delegate, sql, ps);
            return ps;
        }
    }

    public int getTotalRowsCount() {
        return totalRowsCount;
    }

    public void setTotalRowsCount(int totalRowsCount) {
        this.totalRowsCount = totalRowsCount;
    }

}
//************************************************************************************
package com.littleqworks.commons.db.ibatis;

import java.sql.SQLException;
import java.util.List;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;


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);
            }
        }
    }

   
    public PageInfo queryForPage(final String selectStatement,PageInfo page){
        try {
            List list=getSqlMapClientTemplate().getSqlMapClient().queryForList(selectStatement,
                    null,
                    page.getStartIndex(), page.getNumPerPage());
            page.setTotalRows(((LimitSqlExecutor)getSqlExecutor()).getTotalRowsCount());
            page.setResultList(list);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return page;   
    }
}

抱歉，忘了考虑查询总行数时候的参数映射，其实LimitSqlExecutor代码有改动。如下

package com.littleqworks.commons.db.ibatis;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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.impl.ExtendedSqlMapClient;
import com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate;
import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;
import com.ibatis.sqlmap.engine.scope.ErrorContext;
import com.ibatis.sqlmap.engine.scope.RequestScope;
import com.ibatis.sqlmap.engine.scope.SessionScope;

public class LimitSqlExecutor extends SqlExecutor {

    private static final Log logger = LogFactory.getLog(LimitSqlExecutor.class);
   
    private Dialect dialect;
   
    private int totalRowsCount;
   
   

    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()) {
            int rowsCount = 0;
            String csql = dialect.getCountSqlString(sql);
            if(parameters!=null){
                    rowsCount = executeQuery(request, parameters, conn, csql, callback);                   
                    setTotalRowsCount(rowsCount);
            }
           

            if(skipResults>=rowsCount){
                skipResults=rowsCount-skipResults;
            }
            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);
    }

    private int executeQuery(RequestScope request, Object[] parameters, Connection conn, String sql,
            RowHandlerCallback callback) {
        int rowsCount = 0;
        ErrorContext errorContext = request.getErrorContext();
        errorContext.setActivity("executing query procedure");
        errorContext.setObjectId(sql);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
              errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
//              Integer rsType = request.getStatement().getResultSetType();
              ps = prepareStatement(request.getSession(), conn, sql);
             
              if(parameters!=null){
                  for(int i=0;i<parameters.length;i++)
                      ps.setObject(i+1, parameters[i]);
              }

             
              errorContext.setMoreInfo("Check the parameters (set parameters failed).");
              ps.execute();
              errorContext.setMoreInfo("Check the results (failed to retrieve results).");
              rs = ps.getResultSet();
              // Begin ResultSet Handling             
              if(rs!=null){
                  while(rs.next()){
                      rowsCount=rs.getInt(MySQLDialect.RS_COLUMN);
                  }
              }
   
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                  rs.close();
                } catch (SQLException e) {
                  // ignore
                }
              }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                  // ignore
                }
              }
        }
        return rowsCount;
    }

    public boolean supportsLimit() {
        if (enableLimit && dialect != null) {
            return dialect.supportsLimit();
        }
        return false;
    }

    private static PreparedStatement prepareStatement(SessionScope session,
            Connection conn, String sql) throws SQLException {
        SqlMapExecutorDelegate delegate = ((ExtendedSqlMapClient) session
                .getSqlMapExecutor()).getDelegate();
        if (session.hasPreparedStatementFor(sql)) {
            return session.getPreparedStatement((sql));
        } else {
            PreparedStatement ps = conn.prepareStatement(sql);
            session.putPreparedStatement(delegate, sql, ps);
            return ps;
        }
    }

    public int getTotalRowsCount() {
        return totalRowsCount;
    }

    public void setTotalRowsCount(int totalRowsCount) {
        this.totalRowsCount = totalRowsCount;
    }

}


可以这样调用改方法
        PageInfo resultPage=baseDao.queryForPage("classes.selectClasses",new Integer(1),page);

方法

    public PageInfo queryForPage(String selectStatement,PageInfo page){
        try {
            List list=super.getSqlClient().queryForList(selectStatement,
                    null,
                    page.getStartIndex(), page.getNumPerPage());
            page.setTotalRows(super.getSqlExecutor().getTotalRowsCount());
            page.setResultList(list);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return page;       
    }