Using spring's jdbctemplate to realize paging function transfer

Original from: http://java.chinaitlab.com/Spring/38091.html

Recent use of the JdbcTemplate in spring to implement database query and insert operations, the discovery of spring jdbctemplate is not as good as hibernatetemplate, has implemented the paging function. So to implement it yourself, the result set that you get using Getjdbctemplate (). queryForList (String sql) is all.
　　
If your query has 10,000 records, or more, the speed must be slow, of course, you can control the start and end of the query through the cursor in the resultset. I'm using an Oracle database that uses pseudo-column rownum to implement pagination. My paging code is as follows:
　　
Package Com.deity.ranking.util;import java.util.List;
Import Org.springframework.jdbc.core.JdbcTemplate;
Import Org.springframework.jdbc.core.support.JdbcDaoSupport;
/** * Paging function * * @author Allenpan */public class pagination extends jdbcdaosupport{
public static final int numbers_per_page = 10;
Number of records displayed on one page
private int numperpage;
Total Records
private int totalrows;
Total pages
private int totalpages;
Current page number
private int currentpage;
Number of rows to start
private int startIndex;
End of Line
private int lastindex;
Result set store list
Private List resultlist;
JdbcTemplate jtemplate
Private JdbcTemplate jtemplate;
/**
* The constructor that displays 10 records per page, using this function must first set Currentpage,jtemplate initial value to pagination
* @param SQL Oracle Statements
*/
public pagination (String sql) {
if (jtemplate = = null) {
throw new IllegalArgumentException ("Com.deity.ranking.util.Pagination.jTemplate is null,please initial it");
}else if (Sql.equals ("")) {
throw new IllegalArgumentException ("Com.deity.ranking.util.Pagination.sql is empty,please initial it");
}
New pagination (sql,currentpage,numbers_per_page,jtemplate);
}
/** Paging Constructor
* @param SQL gets some basic paging information based on the incoming SQL statement
* @param currentpage Current page
* @param numperpage The number of records per page
* @param jtemplate JdbcTemplate Example
*/
Public pagination (String sql,int currentpage,int numperpage,jdbctemplate jtemplate) {
if (jtemplate = = null) {
throw new IllegalArgumentException ("Com.deity.ranking.util.Pagination.jTemplate is null,please initial it");
}else if (sql = null | | sql.equals ("")) {
throw new IllegalArgumentException ("Com.deity.ranking.util.Pagination.sql is empty,please initial it");
}
Set the number of records to display per page
Setnumperpage (Numperpage);
Set the number of pages to display
Setcurrentpage (currentpage);
Calculate the total number of records
StringBuffer totalsql = new StringBuffer ("SELECT count (*) from (");
Totalsql.append (SQL);
Totalsql.append (") totaltable");
Assigning Values to JdbcTemplate
Setjdbctemplate (jtemplate);
Total number of records
Settotalrows (Getjdbctemplate (). queryForInt (Totalsql.tostring ()));
Calculate Total Pages
Settotalpages ();
Calculate the number of starting lines
Setstartindex ();
Calculate End Line Count
Setlastindex ();
System.out.println ("lastindex=" +lastindex);//////////////////
To construct a paging statement for an Oracle database
StringBuffer paginationsql = new StringBuffer ("SELECT * from");
Paginationsql.append ("Select temp.*, RowNum num from (");
Paginationsql.append (SQL);
Paginationsql.append (") Temp where rownum <=" + lastindex);
Paginationsql.append (") WHERE num >" + startIndex);
Mount result set
Setresultlist (Getjdbctemplate (). queryForList (Paginationsql.tostring ()));
}
/**
* @param args
*/
public static void Main (string[] args) {
TODO auto-generated Method Stub}
public int getcurrentpage () {
return currentpage;
}
public void setcurrentpage (int currentpage) {
This.currentpage = CurrentPage;
}
public int getnumperpage () {
return numperpage;
}
public void setnumperpage (int numperpage) {
This.numperpage = Numperpage;
}
Public List getresultlist () {
return resultlist; }
public void Setresultlist (List resultlist) {
This.resultlist = resultlist;
}
public int gettotalpages () {
return totalpages;
}
Calculate Total Pages
public void Settotalpages () {
if (totalrows% Numperpage = = 0) {
This.totalpages = Totalrows/numperpage;
}else{
This.totalpages = (totalrows/numperpage) + 1;
}
}
public int gettotalrows () {
return totalrows;
}
public void settotalrows (int totalrows) {
This.totalrows = totalrows;
}
public int Getstartindex () {
return startIndex;
}
public void Setstartindex () {
This.startindex = (currentPage-1) * numperpage;
}
public int Getlastindex () {
return lastindex;
}
Public JdbcTemplate getjtemplate () {
return jtemplate;
}
public void Setjtemplate (jdbctemplate template) {
Jtemplate = template;
}
Index at end of calculation
public void Setlastindex () {
System.out.println ("totalrows=" +totalrows);///////////
System.out.println ("numperpage=" +numperpage);///////////
if (Totalrows < numperpage) {
This.lastindex = totalrows;
}else if ((totalrows% numperpage = 0) | | (totalrows% numperpage!= 0 && currentpage < totalpages)) {
This.lastindex = CurrentPage * numperpage;
}else if (totalrows% numperpage!= 0 && currentpage = totalpages) {//Last page
This.lastindex = totalrows;
}
} In my business logic code:
/**
* Find season ranking list from DC
* @param areaid player Area ID
* @param rankdate season
* @param category Category
* @param charactername role name
* @return List
*/
Public List findseasonrankinglist (Long areaid, int rankyear,int rankmonth,
Long categoryid,string charactername) {
SQL statement
StringBuffer sql = new StringBuffer ("Select C.userid userid,d.posname posname,c.gameid gameid,c.amount Amount,C.RANK K from ");
Table Sql.append ("(Select B.userid USERID,");
Sql.append ("B.posid posid,");
Sql.append ("A.district_code districtcode,");
Sql.append ("A.gameid GameID,");
Sql.append ("AMOUNT AMOUNT,");
Sql.append ("rank rank");
Sql.append ("from Tb_fs_rank A");
Sql.append ("Left JOIN tb_character_info B");
Sql.append ("On a.district_code = B.district_code");
Sql.append ("and A.gameid = B.gameid");
Additional conditions
if (areaid!= null && areaid.intvalue ()!= 0) {
Sql.append ("and A.district_code =" + Areaid.intvalue ());
}
if (Rankyear > 1970 && rankmonth > 0) {
Hql.append ("and Sas.id.dt >= to_date ('" + rankyear + "-" + Rankmonth + " -01 00:00:00 '," + "Yyyy-mm-dd HH24:MI:SS");
Hql.append ("and Sas.id.dt <= to_date ('" + rankyear + "-" + Rankmonth + "-" + timetool.findmaxdateinmonth (Rankyear,ra Nkmonth) + "23:59:59 '," + "Yyyy-mm-dd HH24:MI:SS");
Sql.append ("and A.dt = Fn_time_convert (to_date (' + rankyear +"-"+ Rankmonth +" ', "+" ' yyyy-mm '));
}
if (CategoryID!= null && categoryid.intvalue ()!= 0) {
Sql.append ("and a.cid =" + Categoryid.intvalue ());
}
if (charactername!= null &&!charactername.trim (). Equals ("")) {
Sql.append ("and A.gameid = '" + Charactername.trim () + "");
}
Sql.append ("ORDER by RANK ASC) C");
Sql.append ("Left JOIN tb_fs_position D");
Sql.append ("On c.posid = D.posid");
Sql.append ("ORDER by C.rank");
System.out.println ("hql=" +sql.tostring ());////////////////
Use your own paging to program the result set
Pagination PageInfo = new pagination (sql.tostring (), 1,10,getjdbctemplate ());
return Pageinfo.getresultlist ();
Return Getjdbctemplate (). queryForList (Sql.tostring ());
}