Jsp paging class-uniform use

1. Create a database paging class

Package newsbean;
Import java. SQL .*;

Public class DBConnection {
// Ms jdbc is used here
String sDBDriver = "com. microsoft. jdbc. sqlserver. SQLServerDriver ";
// Specify the 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 finals 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 pages
Int iRowCount = 0; // The total number of returned rows.
Int iPageCount = 0; // The total number of returned 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 + "; databaseName =" + databaseName + "; selectMethod =" + selectMethod + ";";
}

// =========================================================== ==================================
// Set the total number of items internally ..
Private void setRowCount (int irowcount)
{
This. iRowCount = irowcount;
}
// Return the total number of internal settings
Public int getRowCount ()
{
Return this. iRowCount;
}

// Set the total number of internal pages ..
Private void setPageCount (int ipagecount)
{
This. iPageCount = ipagecount;
}
// Return the total number of pages set internally
Public int getPageCount ()
{
Return this. iPageCount;
}

// Set the current page internally ..
Private void setPage (int ipage)
{
This. iPage = ipage;
}
// Return the total number of pages set internally
Public int getPage ()
{
Return this. iPage;
}
// Display paging information
// Parameter: Total number of pages, total number of rows, current page
// Add a list of query parameters to enter this method --
Public String showChangePage ()
{
Return this. iPage + "/" + this. iPageCount + ">>> ";
}

// Takes into account the paging 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.exe cuteQuery (SQL );
Rs. last ();

// Move the total number of rows to the end and retrieve the number of the current row.
Int iRowCount = rs. getRow ();
// Calculate the total number of pages. 100 rows: 10 rows per page ==> total number of pages = 11.4 pages ==> 12 pages
Int iPageCount = (iRowCount + iPageSize-1)/iPageSize;
If (iPage> iPageCount) iPage = iPageCount;
If (iPage <= 0) iPage = 1;
If (iPageCount> 0 ){
// The specified row has been included in the method body.
Rs. absolute (iPage-1) * iPageSize + 1 );
}
// Set iPageCount, iRowCount, and iPage to avoid confusion ?!
SetPageCount (iPageCount );
SetRowCount (iRowCount );
SetPage (iPage );
// Stmt. close ();???
} // End try
Catch (SQLException ex)
{
System. err. println ("DBConnection.exe cQuery ():" + ex. getMessage ());
} // End catch

Return rs;

} // End execQuery

// ================================================ ======================
// Close the 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
Bytes --------------------------------------------------------------------------------------------------------------

Ii. Use this process in jsp programs
1. <jsp: useBean id = "conn" class = "newsbean. DBConnection"/>
2. process query parameters such as iPage and txtSearchKeyword.
3. Article list:

Int iPageSize = 15; // The number of rows read each time, passed as a parameter <jsp: setProperty id ...... useless!
String SQL = null;
ResultSet rs = null;
Int iRowCount = 0;
Int iPageCount = 0;
Try {
// ==================================
Int I = 0;
// L construct an SQL statement...
SQL = "SELECT top 200 number, sms_no, company_card_name, regi_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 ";

// Obtain the resultset
Rs = conn.exe cQuery (SQL, iPageSize, iPage );

// Obtain the total number of records
IRowCount = conn. getRowCount ();
// Obtain the total number of pages
IPageCount = conn. getPageCount ();

// Loop
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/> ");
} %>