Implement paging in Oracle (Servlet implementation)

Article by: http://blog.csdn.net/zklxuankai/article/details/7604474

Package com. XK. view;

Import java. Io. ioexception;
Import java. Io. printwriter;
Import java. SQL. connection;
Import java. SQL. drivermanager;
Import java. SQL. preparedstatement;
Import java. SQL. resultset;
Import java. SQL. sqlexception;

Import javax. servlet. servletexception;
Import javax. servlet. http. httpservlet;
Import javax. servlet. http. httpservletrequest;
Import javax. servlet. http. httpservletresponse;

Public class managerusers extends httpservlet
{
@ Override
Protected void doget (httpservletrequest req, httpservletresponse resp)
Throws servletexception, ioexception
{
Resp. setcontenttype ("text/html; charset = UTF-8 ");
Printwriter out = resp. getwriter ();

Connection Ct = NULL;
Preparedstatement PS = NULL;
Resultset rs = NULL;

// Four parameters: the current page of pagenow, the number of rows on each page of pagesize, the total number of pages on pagecount, and the total number of rows on rowcount

// Pagecount = rowcount % pagesize = 0? Rowcount/pagesize: (rowcount/pagesize + 1 );

Int pagenow = 1;

String spagenow = Req. getparameter ("pagenow ");

If (spagenow! = NULL)
{
Pagenow = integer. parseint (spagenow );

}

Int pagesize = 4;
Int rowcount = 1;
Int pagecount = 1;

Try
{
// 1. Load the driver
Class. forname ("oracle. JDBC. Driver. oracledriver ");
// 2. Establish a connection
Ct = drivermanager. getconnection ("JDBC: oracle: thin: @ localhost: 1521: orcl", "system", "oracle123 ");
// 3. Generate preparedstatement

PS = CT. preparestatement ("select count (*) from Scott. EMP ");

Rs = ps.exe cutequery ();

Rs. Next ();

Rowcount = Rs. getint (1); // obtain the total number of rows.

Pagecount = rowcount % pagesize = 0? Rowcount/pagesize: (rowcount/pagesize + 1); // you can view the total number of recorded pages.

// Pagination Calculation

PS = CT. preparestatement ("select * from (select T. *, rownum Rn
From (select * from Scott. EMP order by empno) t where rownum <= "+ pagesize * pagenow +") Where rn> = "+ (pagesize * (pageNow-1) + 1 ));
// 4. Execute the query

Rs = ps.exe cutequery ();

Out. println ("<H1 align = 'center'> Manage employees Out. println ("<Table border = '1' bordercolor = 'green' cellsapcing = 0 align = 'center' width = '000000'> <tr> <TH> account </Th> <TH> name </Th> "+
"<TH> Work </Th> <TH> superiors </Th> <TH> Employment date </Th> <TH> salary increase </ th> <TH> Department </Th>"
+ "<TH> modify user </Th> <TH> delete user </Th> </tr> ");

While (Rs. Next ())
{

Out. println ("<tr> <TH>" + Rs. getint (1) + "</Th> <TH>" + Rs. getstring (2) + "</Th> <TH>" + Rs. getstring (3)
+ "</Th> <TH>" + Rs. getint (4) + "</Th> <TH>" + Rs. getdate (5) + "</Th> <TH>" + Rs. getint (6) +
"</Th> <TH>" + Rs. getint (7) + "</Th> <TH>" + Rs. getint (8) +
"</Th> <TH> <font color = 'blue'> modify a user </font> </Th> <TH> <font color = 'blue'> delete a user </font> </Th> <tr> ");

}

Out. println ("</table> ");

For (INT I = 1; I <= pagecount; I ++)
{
Out. println ("<a href = '/review/managerusers? Pagenow = "+ I +" '> <"+ I +"> </a> ");
}

}
Catch (exception E)
{
E. printstacktrace ();
}
Finally
{

// 5. Close the resource

Try
{

If (RS! = NULL ){

Rs. Close ();

}

If (RS! = NULL ){

Rs. Close ();

}

If (RS! = NULL ){

Rs. Close ();

}

}
Catch (sqlexception E)
{
E. printstacktrace ();
} 

}
}
}