Implement paging using spring jdbctemplate

Implement paging using spring jdbctemplate

Recently, we used jdbctemplate in spring to query and insert databases. We found that jdbctemplate in spring is not as good as hibernatetemplate and has implemented the paging function. Therefore, you must implement it by yourself. The result set obtained by using getjdbctemplate (). queryforlist (string SQL) is all.

If your query contains 10000 or more records, the speed will be slow. Of course, you can use the cursor in the resultset to control the start and end of the query. Here I use the Oracle database and use the pseudo-column rownum for paging. 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 Functions

* @ 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 number of records
Private int totalrows;

// Total number of pages
Private int totalpages;

// Current page number
Private int currentpage;

// Start row
Private int startindex;

// Number of end rows
Private int lastindex;

// Result set storage list

Private list resultlist;

// Jdbctemplate jtemplate

Private jdbctemplate jtemplate;

/**
* The constructor with 10 records is displayed on each page. To use this function, you must first set currentpage and jtemplate initial values for pagination.
* @ Param SQL Oracle statement
*/

Public pagination (string SQL ){

If (jtemplate = NULL ){
Throw new illegalargumentexception ("com. deity. ranking. util. pagination. jtemplate is null, please initial it first .");

} Else if (SQL. Equals ("")){

Throw new illegalargumentexception ("com. deity. ranking. util. pagination. SQL is empty, please initial it first .");

}
New pagination (SQL, currentpage, numbers_per_page, jtemplate );
}

/** Page Constructor
* @ Param SQL gets some basic paging information based on the input SQL statement.
* @ Param currentpage current page
* @ Param numperpage: number of records per page
* @ Param jtemplate jdbctemplate instance
*/

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 first .");

} Else if (SQL = NULL | SQL. Equals ("")){

Throw new illegalargumentexception ("com. deity. ranking. util. pagination. SQL is empty, please initial it first .");

}

// Set the number of records displayed on each page
Setnumperpage (numperpage );

// Set the number of pages to be displayed
Setcurrentpage (currentpage );

// Calculate the total number of records
Stringbuffer totalsql = new stringbuffer ("select count (*) from (");

Totalsql. append (SQL );
Totalsql. append (") totaltable ");

// Assign a value to the jdbctemplate
Setjdbctemplate (jtemplate );

// Total number of records
Settotalrows (getjdbctemplate (). queryforint (totalsql. tostring ()));

// Calculate the total number of pages
Settotalpages ();

// Calculate the start row
Setstartindex ();

// Calculate the number of end rows
Setlastindex ();

System. Out. println ("lastindex =" + lastindex );//////////////////
// Create a paging statement for the 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 );

// Load the 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 the total number of 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 the 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 contestant Region ID
* @ Param rankdate season
* @ Param 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, D. posname, C. gameid, C. Amount amount, C. Rank rank from ");
// Table
SQL. append ("(select B. userid ,");
SQL. append ("B. posid ,");
SQL. append ("A. district_code districtcode ,");
SQL. append ("A. gameid ,");
SQL. append ("amount ,");
SQL. append ("rank ");
SQL. append ("from tb_fs_rank ");
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, rankmonth) + "23:59:59 '," + "YYYY-MM-DD hh24: MI: SS ");

SQL. append ("and. 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 program to control the result set
Pagination pageinfo = new pagination (SQL. tostring (), 1, 10, getjdbctemplate ());
Return pageinfo. getresultlist ();
// Return getjdbctemplate (). queryforlist (SQL. tostring ());
}