JdbcTemplate + SQL Server 2005 Paging +extjs

JdbcTemplate + SQL Server 2005 Paging +extjs

 PackageCom.ur.util;ImportJava.util.List;ImportJava.util.Map;ImportOrg.springframework.jdbc.core.JdbcTemplate; Public  class pagination {     Public Static Final intNumbers_per_page = -;Private intoffset =0;Private intLimit = -;Private intTotalSize;PrivateList<map<string, object>> resultlist;PrivateJdbcTemplate jtemplate; Public pagination() {    } Public pagination(String SQL,intOffsetintLimit,jdbctemplate jtemplate) {setoffset (offset);        Setlimit (limit); Setjtemplate (jtemplate);intOrderbyindex = Sql.tolowercase (). LastIndexOf ("ORDER BY");if(Orderbyindex <=0) {Throw NewUnsupportedoperationexception ("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)); } PublicStringgetlimitstring(String SQL,intOffsetintLimit) {returngetlimitstring (SQL, offset >0, Offset,limit); } PublicStringgetlimitstring(String SQL,BooleanHasoffset,intOffsetintLimit) {intOrderbyindex = Sql.tolowercase (). LastIndexOf ("ORDER BY");if(Orderbyindex <=0) {Throw NewUnsupportedoperationexception ("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);intInsertpoint = getsqlafterselectinsertpoint (sql); StringBuilder Sbsql =NewStringBuilder (Sql.length () + -). Append ("with Temppagination as ("). Append (Sqlremoveorderby). Insert (Insertpoint + at,"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) +"") ; }returnSbsql.tostring (); }Private Static int Getsqlafterselectinsertpoint(String SQL) {intSelectindex = Sql.tolowercase (). IndexOf ("SELECT");Final intSelectdistinctindex = Sql.tolowercase (). IndexOf ("SELECT distinct");returnSelectindex + (Selectdistinctindex = = Selectindex?) the:6); } Public int GetOffset() {returnOffset } Public void SetOffset(intOffset) { This. offset = offset; } Public int Getlimit() {returnLimit } Public void Setlimit(intLimit) { This. limit = limit; } Public int gettotalsize() {returnTotalSize; } Public void settotalsize(intTotalSize) { This. totalsize = TotalSize; } PublicList<map<string, object>>getresultlist() {returnResultlist; } Public void setresultlist(list<map<string, object>> resultlist) { This. resultlist = resultlist; } PublicJdbcTemplategetjtemplate() {returnJtemplate; } Public void setjtemplate(JdbcTemplate jtemplate) { This. jtemplate = jtemplate; }}

Business call pagination pagination = new pagination (sqlsb.tostring (), start,limit,this.jdbctemplate);

JdbcTemplate + SQL Server 2005 Paging +extjs