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