JdbcTemplate + sqlserver 2005 分页+Extjs

JdbcTemplate + sqlserver 2005 分页+Extjs

package com.ur.util;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

public class Pagination  {
    public static final int NUMBERS_PER_PAGE = 20;
    private int offset = 0;
    private int limit = 20;
    private int totalSize;
    private List<Map<String, Object>> resultList;
    private JdbcTemplate jTemplate;

    public Pagination() {

    }
    public Pagination(String sql, int offset, int limit,JdbcTemplate jTemplate){
        setOffset(offset);
        setLimit(limit);
        setjTemplate(jTemplate);
        int orderByIndex = sql.toLowerCase().lastIndexOf("order by");  
        if (orderByIndex <= 0) {  
          throw new UnsupportedOperationException(  
              "must specify 'order by' statement to support limit operation with offset in sql server 2005");  
        }  
        String totalSql = "select count(*) as totalsize from ("+sql.toLowerCase().substring(0, orderByIndex) +") totalTable ";

        setTotalSize(this.jTemplate.queryForInt(totalSql));
        String sqls = this.getLimitString(sql, offset, limit);
        setResultList(this.jTemplate.queryForList(sqls));
    }
    public String getLimitString(String sql, int offset, int limit) { 
        return getLimitString(sql, offset > 0,offset,limit);  
    }  

    public String getLimitString(String sql, boolean hasOffset, int offset, int limit) {
        int orderByIndex = sql.toLowerCase().lastIndexOf("order by");  
        if (orderByIndex <= 0) {  
          throw new UnsupportedOperationException(  
              "must specify 'order by' statement to support limit operation with offset in sql server 2005");  
        } 

        String sqlOrderBy = sql.substring(orderByIndex + 8);  
        String sqlRemoveOrderBy = sql.substring(0, orderByIndex);  
        int insertPoint = getSqlAfterSelectInsertPoint(sql);  
        StringBuilder sbSql =  new StringBuilder(sql.length() + 100).append("with tempPagination as(").append(sqlRemoveOrderBy).insert(  
            insertPoint + 23, " ROW_NUMBER() OVER(ORDER BY " + sqlOrderBy + ") as RowNumber,").append(  
            ") select * from tempPagination ");
        if(offset != -1  && limit !=-1){
            sbSql.append("  where RowNumber between "+(offset+1)+" and "+(offset+limit)+"") ;
        }
       return sbSql.toString();  
      }  


     private static int getSqlAfterSelectInsertPoint(String sql) {  
        int selectIndex = sql.toLowerCase().indexOf("select");  
        final int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");  
        return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);  
     }
    public int getOffset() {
        return offset;
    }
    public void setOffset(int offset) {
        this.offset = offset;
    }
    public int getLimit() {
        return limit;
    }
    public void setLimit(int limit) {
        this.limit = limit;
    }
    public int getTotalSize() {
        return totalSize;
    }
    public void setTotalSize(int totalSize) {
        this.totalSize = totalSize;
    }
    public List<Map<String, Object>> getResultList() {
        return resultList;
    }
    public void setResultList(List<Map<String, Object>> resultList) {
        this.resultList = resultList;
    }
    public JdbcTemplate getjTemplate() {
        return jTemplate;
    }
    public void setjTemplate(JdbcTemplate jTemplate) {
        this.jTemplate = jTemplate;
    }
}

业务调用 Pagination pagination = new Pagination(sqlSb.toString(),start,limit,this.jdbcTemplate);