JSP paging Class---Unified use

First, set up the database paging class

Package Newsbean;
Import java.sql.*;

public class dbconnection{
Use MS JDBC here
String sdbdriver = "Com.microsoft.jdbc.sqlserver.SQLServerDriver";
Specify Database name/url
Private final String url = "jdbc:microsoft:sqlserver://";
Private final String servername= "LocalSqlServer";
Private final String portnumber = "1433";
Private final String databasename= "Mybusidb";
String sConnStr = "Jdbc:microsoft:sqlserver://localsqlserver:1433;databasename=ourcompany";
Private final String dbusername = "sa";
Private final String Dbpassword = "111111";
Informs the driver to use Server a side-cursor,
Which permits more than one active statement
On a connection.
Private final String SelectMethod = "cursor";

Connection conn = null;
ResultSet rs = null;
Statement stmt = null;

These three parameters are used to record the paging
int irowcount = 0; Returns the total number of rows
int ipagecount = 0; Return Total Pages
int ipage = 0;

Public DbConnection () {
Try
{
Class.forName (Sdbdriver);
}
catch (ClassNotFoundException e)
{
System.err.println ("DbConnection ():" + e.getmessage ());
}
}

Construct a connection string
Private String getConnectionUrl () {
Return url+servername+ ":" +portnumber+ ";d atabasename=" +databasename+ "; selectmethod=" +selectmethod+ ";
}

================ consider paging in a class ======================================
Internal set total number of bars.
private void Setrowcount (int irowcount)
{
This.irowcount = Irowcount;
}
Returns the total number of bars that are set internally
public int GetRowCount ()
{
return this.irowcount;
}

Internal set total number of pages.
private void Setpagecount (int ipagecount)
{
This.ipagecount = Ipagecount;
}
Returns the total number of pages in internal settings
public int Getpagecount ()
{
return this.ipagecount;
}

Set the current page internally ...
private void Setpage (int ipage)
{
This.ipage = IPage;
}
Returns the total number of pages in internal settings
public int GetPage ()
{
return this.ipage;
}
Show page-Flipping information
Parameters: Total number of pages, rows, current page
You should consider adding a query parameter list into this method--2007-4-12
Public String Showchangepage ()
{
return this.ipage + "/" + This.ipagecount + ">>>";
}

Consider the page-flipping selection query
Public ResultSet ExecQuery (String sql,int ipagesize,int ipage)
{//======irowcount== ipagecount==ipagesize===ipage================
Try
{
conn = Drivermanager.getconnection (getConnectionUrl (), Dbusername,dbpassword);
stmt = Conn.createstatement (resultset.type_scroll_sensitive,resultset.concur_updatable);
rs = stmt.executequery (SQL);
Rs.last ();

Gets the row number to move to the end, retrieving the current line number.
int irowcount= rs.getrow ();
Calculate Total page Total 100 lines per page 10 ===〉 Total page = 11.4 Page ==> 12 page
int Ipagecount = (irowcount + iPageSize-1)/ipagesize;
if (ipage>ipagecount) ipage = Ipagecount;
if (ipage <= 0) ipage = 1;
if (ipagecount>0) {
The specified line is already in the method body
Rs.absolute ((iPage-1) * ipagesize + 1);
}
Set your own ipagecount and irowcount,ipage to avoid confusion?!
Setpagecount (Ipagecount);
Setrowcount (Irowcount);
Setpage (IPage);
Stmt.close ();???
}//end try
catch (SQLException ex)
{
System.err.println ("Dbconnection.execquery ():" + ex.getmessage ());
}//end catch

Return RS;

}//end ExecQuery

//======================================================
Close Database
public void Closedb ()
{
Try
{

System.out.println ("Dbconnection.closedb (here!)");
if (rs!=null)
{
Rs.close ();
rs = null;
}
Else
{
System.out.println ("RS closed!");
}//?????

if (stmt!=null)
{
Stmt.close ();
stmt = null;
}
Else
{
System.out.println ("stmt closed!");
}
if (conn!=null)
{
Conn.close ();
conn = null;
}
Else
{
SYSTEM.OUT.PRINTLN ("conn closed!");
}
}
catch (Exception ex)
{
System.err.println ("Dbconnection.closedb ()" + ex.getmessage ());
System.out.println ("Dbconnection.closedb ()" + ex.getmessage ());
}
}

}//end Class
--------------------------------------------------------------------------------------------------------------

Second, the JSP program to use this class of process
1, <jsp:usebean id= "conn" class= "Newsbean." DbConnection "/>
2, processing query parameters IPage, Txtsearchkeyword, etc.
3, the article list:

int ipagesize = 15;//The number of rows per read, passed as a parameter to the <jsp:setproperty ID ... It's useless!
String Sql=null;
ResultSet RS =null;
int irowcount = 0;
int ipagecount = 0;
try{
//===========================
int i = 0;
L Construct SQL statements ...
Sql= "Select top number, Sms_no, Company_card_name, Reg_date, Dead_date, Onuse, Province,"
+ "City from Dbo.company_card where 1=1";
if (! (Txtsearchcompanycard.equals ("))) sql = SQL +" and company_card_name like '% "+ txtsearchcompanycard +"% ";
if (! (Txtsearchcity.equals ("))) sql = SQL +" and city = ' "+ txtsearchcity +" ' ";
sql = SQL + "ORDER by number DESC";

Get resultset
rs = Conn.execquery (sql,ipagesize,ipage);

Get Total Records
Irowcount = Conn.getrowcount ();
Get Total Pages
Ipagecount = Conn.getpagecount ();

Cycle
do{
%>
<tr>
<td>[<%=rs.getrow ()%>]</td>
<td><%=rs.getstring ("Sms_no")%></td>
<td><%=rs.getstring ("Company_card_name")%></td>
<td><%=rs.getdate ("Reg_date")%></td>
<td><%=rs.getstring ("province")%></td>
<td><%=rs.getstring ("City")%></td>
<td><a href= "#" onclick= "return Domodify (' <%=rs.getint (" number ")%> ')" > Modify </a></td>
</tr>
<%
}while (++i<ipagesize && rs.next ());
}catch (Exception e) {
Out.print ("RS Err:" + e.getmessage ());
Out.print (sql + "<br/>");
}%>