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);
JdbcTemplate + sqlserver 2005 分页+Extjs
标签：
