Analysis on paging technology for jsp database reading

This article describes how to use javabean and jsp pages to display data by page. In this example, the database used is Mysql.

1. Check javabean first.
Class Name:
DatabaseBean. java:
The code for databaseBean. java is as follows:
Copy codeThe Code is as follows:
Package database_basic;
Import java. SQL .*;
Import java. util .*;
Public class databaseBean
{
// This is the default database connection method.
Private String DBLocation = "jdbc: mysql: // localhost/onestoptech? User = root & password = password & useUnicode = true & characterEncoding = GB2312 ";
Private String DBDriver = "org. gjt. mm. mysql. Driver ";
Private Connection conn = null;
Public databaseBean (){}
// The set method can be used to flexibly set database connections.
Public void setDBLocation (String location) {DBLocation = location ;}
Public void setDBDriver (String driver) {DBDriver = driver ;}
Public void setconn (Connection conn) {this. conn = conn ;}
Public String getDBLocation () {return (DBLocation );}
Public String getDBDriver () {return (DBDriver );}
Public Connection getconn () {return (conn );}
/* Public String DBConnect (){}
Public String DBDisconnect (){}
Public ResultSet query (String SQL ){}
Public int getTotalPage (String SQL, int pageSize ){}
Public ResultSet getPagedRs (String SQL, int pageSize, int pageNumber ){}
Public String execute_ SQL (String SQL ){}*/
// Establish a connection
Public String DBConnect ()
{
String strExc = "Success! "; // The default value of strExc is Success. If an exception is thrown, that is, the database connection is unsuccessful, other throws are assigned to the following catch statements.
Try
{
Class. forName (DBDriver );
Conn = DriverManager. getConnection (DBLocation );
}
Catch (ClassNotFoundException e)
{
StrExc = "the database driver is not found. error message: <br>" + e. toString ();
}
Catch (SQLException e)
{
StrExc = "SQL statement error, error prompt <br>" + e. toString ();
}
Catch (Exception e)
{
StrExc = "error message: <br>" + e. toString ();
}
Return (strExc );
} // Then end of DBConnect
// Disconnect
Public String DBDisconnect ()
{
String strExc = "Success! "; // The default value of strExc is Success. If an exception is thrown, that is, the database disconnection fails, other throws are assigned to the following catch statements.
Try
{
If (conn! = Null) conn. close ();
}
Catch (SQLException e)
{
StrExc = e. toString ();
}
Return (strExc );
}
// Return a result set by passing in an SQL statement
Public ResultSet query (String SQL) throws SQLException, Exception
{
ResultSet rs = null;
If (conn = null)
{
DBConnect ();
}
If (conn = null)
{
Rs = null;
}
Else
{
Try
{
Statement s = conn. createStatement ();
Rsw.s.exe cuteQuery (SQL );
}
Catch (SQLException e) {throw new SQLException ("Cound not execute query .");}
Catch (Exception e) {throw new Exception ("Cound not execute query .");}
} // Then end of if
Return (rs );
} // Then end of the function executeQuery
// Calculate and return the total number of pages by inputting an SQL statement and pageSize (number of results displayed on each page ).
Public int getTotalPage (String SQL, int pageSize)
{
ResultSet rs = null;
Int totalRows = 0;
If (conn = null)
{
DBConnect ();
}
If (conn = null)
{
Rs = null;
}
Else
Try
{
Statement s = conn. createStatement ();
Rsw.s.exe cuteQuery (SQL); // obtain the result set through the input SQL
While (rs. next ())
TotalRows ++; // calculate the total number of items in the returned result set through totalRows ++.
}
Catch (SQLException e ){}
Rs = null;
// The total number of pages (totalRows-1)/pageSize + 1 is obtained by this algorithm and the result is returned. TotalRows refers to the total number of entries in the returned result set, and pageSize refers to the number of entries displayed on each page.
Return (totalRows-1)/pageSize + 1 );
}
// Input an SQL statement to display the pageSize and page number of each page to obtain a result set.
Public ResultSet getPagedRs (String SQL, int pageSize, int pageNumber)
{
ResultSet rs = null;
Int absoluteLocation;
If (conn = null)
{
DBConnect ();
}
If (conn = null)
{
Rs = null;
}
Else
Try
{
Statement s = conn. createStatement ();
// PageSize * pageNumber the number of entries displayed on each page multiplied by the page number to calculate the number of the result in the last row. Any result with a number greater than this maxrows will be dropped
S. setMaxRows (pageSize * pageNumber );
Rsw.s.exe cuteQuery (SQL );
}
Catch (SQLException e ){}
// AbsoluteLocation = pageSize * (pageNumber-1) This expression calculates the number of the last result of the previous page (if this page exists, the number of results displayed on the previous page must be pageSize)
AbsoluteLocation = pageSize * (pageNumber-1 );
Try
{
// This for loop is used to locate the result set rs to the last result before this page.
For (int I = 0; I <absoluteLocation; I ++)
{
Rs. next ();
}
}
Catch (SQLException e ){}
// The returned result set is clipped to both ends, which is the result to be displayed on the page (pageNumber ).
Return (rs );
}
Public String execute_ SQL (String SQL ){
String strExc;
StrExc = "Success! ";
If (conn! = Null)
{
Try {
PreparedStatement update;
Update = conn. prepareStatement (SQL );
Update.exe cute ();
}
Catch (SQLException e)
{
StrExc = e. toString ();
}
Catch (Exception e)
{
StrExc = e. toString ();
}
}
Else
{
StrExc = "Connection Lost! ";
}
Return (strExc );
} // Execute_ SQL

2. Analyze the jsp page
Page name:
Admin_show.jsp
Page code:
Copy codeThe Code is as follows:
<% @ Page errorPage = "error. jsp" %>
<% @ Page contentType = "text/html; charset = gb2312" %>
<% @ Page import = "java. util. *" %>
<% @ Page import = "java. SQL. *" %>
// Import the databaseBean class under the database_basic package. The alias is basicDB.
<Jsp: useBean id = "basicDB" class = "database_basic.databaseBean" scope = "page"/>
<%
String SQL;
ResultSet rs;
Int id;
String reply, Exc;
Exc = basicDB. DBConnect (); // establish a connection. If yes, Success is returned! If the error message fails, the system returns the error message.
If (! Exc. equals ("Success! "))
{
// BasicDB. DBDisconnect ();
Throw new Exception (Exc );
}
Int pageSize = 10; // defines the number of data entries displayed on each page
Int currentPage = 1; // current page (the first page must be displayed !~), The "Current page" will be passed in by the pages parameter in the following page
Int allPage =-1;
String pages = request. getParameter ("pages"); // obtain the pages parameter. This parameter indicates the current page to be displayed"
If (pages! = Null) currentPage = Integer. valueOf (pages ). intValue (); // This is an example of converting Integer type to int type. When the page is executed for the first time, the parameter pages is null, currentPage = 1; when this page is executed again, the pages parameter will be assigned a value. currentPage = the int value of pages
SQL = "select * from gbook order by id desc"; // The returned result Assembly uses desc in descending order. The advantage is that the latest information is displayed in the front.
AllPage = basicDB. getTotalPage (SQL, pageSize); // obtain the total page number.
Rs = basicDB. getPagedRs (SQL, pageSize, currentPage); // obtain the result set to be displayed on the current page.
%>
<Table border = "0" cellspacing = "1" cellpadding = "3" width = "590" bgcolor = "# ffffff">
<%
While (rs. next ()){
Id = rs. getInt ("id"); // obtain the id number in the database (result set ).
%>
<Tr bgcolor = "# FF6600" style = "color: white">
<Td> Name: <% = rs. getString ("leaver") %> </td>
<Td> Time: <% = rs. getString ("leave_date") %> </td>
<Td> Email: <% = rs. getString ("email") %> </td>
<Td> <div style = "width: 150; overflow: hidden;"> Home: <% = rs. getString ("homepage") %> </div> </td>
</Tr>
<Tr bgcolor = "# FFE3B9">
<Td colspan = 4> <font color = "# FF6600"> Content: </FONT> <BR> <% = rs. getString ("content") %> </td>
</Tr>
<% }%>
<Tr> <td height = "1"> </td> </tr>
<Tr>
<Td colspan = 4 align = right bgcolor = "# FF6600" style = "color: white;">
Page <% = currentPage %>,
<% If (currentPage> 1) {%>
<! -- If it is not on the first page, the "Homepage" link is displayed -->
<A href = "admin_show.jsp? Pages = <% = (currentPage-1) %> "> Home </A>
<%}
For (int I = 1; I <= allPage; I ++)
{
// Display 1, 2, 3, 4 ...... Link to the last page
Out. println ("<a href = admin_show.jsp? Pages = "+ I +"> "+ I +" </a> ");
}
%>
<% If (currentPage <allPage) {%>
<! -- If the last page is not displayed, the "Last page" link is displayed. -->
<A href = "admin_show.jsp? Pages = <% = (currentPage + 1) %> "> last page </A>
<% }%> </Td>
</Tr>
</Table>
} // Then end of the class

3. Summary
In this paging display program, there are several algorithms and implementation methods that are relatively fixed and classic. For those who have not written paging programs, they should be inspired.