ibatis 之分页

 

 

iBatis分页是采用游标滚动的方式来实现的，不支持物理分页，这种方式在大数据量的情况下往往会造成内存溢出、响应速度较慢。因此一般都采用手写SQL语句实现数据库物理分页。

找到ibatis执行sql的地方，截获sql并重新组装sql，为ibatis引入物理分页机制，效率相对较高。

   列表集合获取：使用Spring  ibatis(SqlMapClientTemplate)实现.

总记录数获取：使用Spring JdbcTemplate 实现.

Ibatis sql执行者，截取sql语句并重新组装

 

package com.ibatis;

import java.sql.Connection;

import java.sql.SQLException;

 

import com.ibatis.sqlmap.engine.execution.SqlExecutor;

import com.ibatis.sqlmap.engine.mapping.statement.RowHandlerCallback;

import com.ibatis.sqlmap.engine.scope.RequestScope;

 

public class SQLExecutor extends SqlExecutor {

 

    private Dialect dialect = null;

 

    public Dialect getDialect() {  

       return dialect;

    }

 

    public void setDialect(Dialect dialect) {

       this.dialect = dialect;

    }

 

    @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)) {

           sql = dialect.getLimitString(sql, skipResults, maxResults);

      

           skipResults = NO_SKIPPED_RESULTS;

           maxResults = NO_MAXIMUM_RESULTS;

       }

       super.executeQuery(request, conn, sql, parameters, skipResults,maxResults, callback);

    }

}

Ibatis 动态SQL语句、参数类

package com.ibatis;

 

import com.ibatis.sqlmap.engine.impl.ExtendedSqlMapClient;

import com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap;

import com.ibatis.sqlmap.engine.mapping.sql.Sql;

import com.ibatis.sqlmap.engine.mapping.statement.MappedStatement;

import com.ibatis.sqlmap.engine.scope.RequestScope;

 

public class SQL {

 

    private ExtendedSqlMapClient sqlMapClient = null;

 

    private String statementName = null;

 

    private Object parameterObject = null;

 

    private RequestScope request = null;

 

    public SQL(ExtendedSqlMapClient sqlMapClient, String statementName,

           Object parameterObject) {

       this.sqlMapClient = sqlMapClient;

       this.statementName = statementName;

       this.parameterObject = parameterObject;

 

       this.request = newRequestScope(this.sqlMapClient, this.statementName,this.parameterObject);

    }

 

    private RequestScope newRequestScope(ExtendedSqlMapClient sqlMapClient,String statementName, Object parameterObject) {

 

       RequestScope request = new RequestScope();

      

       MappedStatement stmt = sqlMapClient.getMappedStatement(statementName);

       request.setStatement(stmt);

      

       Sql sql = stmt.getSql();

       request.setSql(sql);

      

       ParameterMap parameterMap = sql.getParameterMap(request,parameterObject);

       request.setParameterMap(parameterMap);

       return request;

    }

 

    public String getSql() {

       return request.getSql().getSql(request, parameterObject);

    }

 

    public Object[] getParameters() {

       return request.getParameterMap().getParameterObjectValues(request,parameterObject);

    }

 

}

 

方言接口

package com.ibatis.dialect;

 

public interface Dialect {

     

    public abstract String getCountString(String sql);

 

    public abstract String getLimitString(String sql, int startRow, int maxRow);

 

}

 

Oracle 方言实现类

package com.ibatis.dialect;

 

public class OracleDialect implements Dialect {

 

 

    public String getCountString(String sql) {

       StringBuffer sql_ = new StringBuffer();

       sql_.append("select count(1) as count from ( ").append(sql).append(" )");           

       return sql_.toString();

    }

 

    public String getLimitString(String sql, int startRow, int maxRow) {

       StringBuffer sql_ = new StringBuffer();

       sql_.append("select * from ( select t1.*, rownum rownum_ from ( ");

       sql_.append(sql);

       sql_.append(" ) t1 where rownum <= "+maxRow+") where rownum_ > "+startRow+"");

        return sql_.toString();    

    }

 

}

 

分页实体类

package com.commons;

 

import java.util.HashMap;

import java.util.List;

import java.util.Map;

 

 

public class Page implements java.io.Serializable {

 

    private static final long serialVersionUID = 1L;

 

    /** 当前页数 * */

    private int pageNo = 1;

 

    /** 每页显示的记录数 * */

    private int pageSize = 20;

 

    /** 总记录 * */

    private int totalCount;

 

    /** 总页数* */

    private int totalPage;

 

    private boolean hasNext = false;

 

    private int nextPage;

 

    private boolean hasPre = false;

 

    private int prePage;

 

    /** 页面数据List * */

    private List items;

 

    /** 参数 Map * */

    private Map<String, Object> params = new HashMap<String, Object>();

 

    /** URL 参数* */

    private String urlParam;

 

    public Page() {

 

    }

 

    public Page(List items, int pageNo, int pageSize, int totalCount) {

       this.pageNo = pageNo;

       this.totalCount = totalCount;

       this.pageSize = pageSize;

       this.items = items;

    }

 

    public void addParam(String name, Object value) {

       if (name != null && !name.equals("") && value != null && !value.equals("")) {          

           if (params.size() == 0) {

              urlParam = name + "=" + value;

           } else {

              urlParam = "&" + name + "=" + value;

           }

           params.put(name, value);

       }

    }

 

    public int getPageNo() {

       return pageNo;

    }

 

    public void setPageNo(int pageNo) {

       this.pageNo = pageNo;

    }

 

    public int getPageSize() {

       return pageSize;

    }

 

    public void setPageSize(int pageSize) {

       this.pageSize = pageSize;

    }

 

    public int getTotalCount() {

       return totalCount;

    }

 

    public void setTotalCount(int totalCount) {

       this.totalCount = totalCount;

    }

 

    public int getTotalPage() {

       if (totalCount == 0)

           return 0;

       totalPage = totalCount / pageSize;

       if (totalCount % pageSize > 0) {

           totalPage++;

       }

       return totalPage;

    }

 

    public void setTotalPage(int totalPage) {

       this.totalPage = totalPage;

    }

 

    /** 是否有下一页 */

    public boolean isHasNext() {

       this.hasNext = (pageNo + 1 <= getTotalPage());

       return hasNext;

    }

 

    public int getNextPage() {

       if (isHasNext())

           nextPage = pageNo + 1;

       else

           nextPage = totalPage;

       return nextPage;

    }

 

    public void setNextPage(int nextPage) {

       this.nextPage = nextPage;

    }

 

    /** 是否有上一页 */

    public boolean isHasPre() {

       this.hasPre = (pageNo - 1 >= 1);

       return hasPre;

    }

 

    public int getPrePage() {

       if (isHasPre())

           prePage = pageNo - 1;

       else

           prePage = pageNo;

       return prePage;

    }

 

    public void setPrePage(int prePage) {

       this.prePage = prePage;

    }

 

    public List getItems() {

       return items;

    }

 

    public void setItems(List items) {

       this.items = items;

    }

 

    public Map<String, Object> getParams() {

       return params;

    }

 

    public void setParams(Map<String, Object> params) {

       this.params = params;

    }

 

    public void setUrlParam(String urlParam) {

       this.urlParam = urlParam;

    }

 

    public String getUrlParam() {

       return this.urlParam;

    }

 

}

 

基础 DAO接口

 

 

package com.commons;

 

import java.util.List;

 

import org.springframework.jdbc.core.JdbcOperations;

 

 

public interface IBaseDao {

   

     /**

      * JDBC 执行类

      * @date Jan 12, 2011  

      * @return

      */

     JdbcOperations getJdbcTemplate();

     

     /**

      * 单个对象获取

      * @date Jan 12, 2011  

      * @param statementName  

      * @param parameterObject 参数值对象

      * @return

      */

     Object getObject(String statementName,Object parameterObject);

   

     /**

      * 集合对象获取

      * @date Jan 12, 2011  

      * @param statementName

      * @param parameterObject 参数值对象

      * @return

      */

     List list(String statementName,Object parameterObject);

     

     /**

      * 分页对象获取

      * @date Jan 12, 2011  

      * @param statementName

      * @param page 分页值对象

      * @return 

      */

     Page list(String statementName,Page page);