The first file PagedbClass. java
| The code is as follows: |
Copy code |
Package com. kanba. tools;
Import java. SQL .*;
Import com. kanba. connection. Conn;
Public class PagedbClass {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData resultsMeta = null;
Int rows = 0;
Public PagedbClass (){
Con = new Conn (). getConnection ();
}
Public ResultSet executeQuery (String SQL) throws SQLException {
ResultSet rs = null;
Try {
Stmt = con. createStatement ();
Rs = stmt.exe cuteQuery (SQL );
While (rs. next ())
This. rows ++;
Rs = stmt.exe cuteQuery (SQL );
}
Catch (SQLException e ){
System. out. print ("Query:" + e. getMessage ());
}
This. rs = rs;
Return rs;
}
Public boolean executeUpdate (String SQL ){
Try {
Stmt = con. createStatement ();
Stmt.exe cuteUpdate (SQL );
Return true;
}
Catch (SQLException e ){
System. out. print ("Update:" + e. getMessage ());
Return false;
}
}
Public int getColumns (){
Int columns = 0;
Try {
This. resultsMeta = this. rs. getMetaData ();
Columns = this. resultsMeta. getColumnCount ();
}
Catch (SQLException e ){}
Return columns;
}
Public int getRows (){
Return this. rows;
}
Public void closedb (){
Try {
Con. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
} |
The second file paging generic class PageQuery. java
| The code is as follows: |
Copy code |
Package com. kanba. tools;
Import java. SQL .*;
Import javax. servlet. http .*;
Public class PageQuery {
Int Offset; // record Offset
Int Total; // The Total number of records.
Int MaxLine; // number of records displayed per page
ResultSet rs; // read the result
Int TPages; // The total number of pages.
Int CPages; // Current page number
String PageQuery; // display the parameters to be passed by page
String Query; // query statement
String QueryPart; // query part after "FROM"
String FilePath;
PagedbClass db; // object of dbclass
// Constructer do nothing
Public PageQuery (){
// Ten lines are displayed on each page.
MaxLine = 30;
Db = new PagedbClass ();
}
***************
// Main working function, which reads the corresponding records from the table based on the given conditions
Public ResultSet myQuery (String query, HttpServletRequest req) throws SQLException {
String query_part, OS;
Int begin, offset;
// Intercept query statements after "FROM"
Begin = query. indexOf ("FROM ");
Query_part = query. substring (begin, query. length (). trim ();
// Calculate the offset
OS = req. getParameter ("offset ");
If (OS = null) Offset = 0;
Else Offset = Integer. parseInt (OS );
// Get the file name
FilePath = req. getRequestURI ();
Query = query;
QueryPart = query_part;
// Calculate the total number of records
String SQL = "SELECT Count (*) AS total" + this. QueryPart;
Rs = db.exe cuteQuery (SQL );
If (rs. next ())
Total = rs. getInt (1 );
// Set the current page number and total page number
TPages = (int) Math. ceil (double) this. Total/this. MaxLine );
CPages = (int) Math. floor (double) Offset/this. MaxLine + 1 );
// Retrieve the required records based on conditions
If (Total> 0 ){
SQL = Query + "LIMIT" + Offset + "," + MaxLine;
Rs = db.exe cuteQuery (SQL );
}
Return rs;
}
Public void close (){
Db. closedb ();
}
// Display the total number of pages
Public int getTotalPages (){
Return TPages;
}
// Display the current page number
Public int getCurrenPages (){
Return CPages;
}
// *********** Display the page turning prompt bar *************
// Display the home page, next page, last page, and last page
Public String PageLegend (){
String str = "";
Int first, next, prev, last;
First = 0;
Next = Offset + MaxLine;
Prev = Offset-MaxLine;
Last = (this. TPages-1) * MaxLine;
If (Offset> = MaxLine)
Str + = "<A href =" + FilePath + "? Offset = "+ first +"> homepage </A> ";
Else str + = "homepage ";
If (prev> = 0)
Str + = "<A href =" + FilePath + "? Offset = "+ prev +"> Previous Page </A> ";
Else str + = "front page ";
If (next <Total)
Str + = "<A href =" + FilePath + "? Offset = "+ next +"> next page </A> ";
Else str + = "";
If (TPages! = 0 & CPages <TPages)
Str + = "<A href =" + FilePath + "? Offset = "+ last +"> last page </A> ";
Else str + = "Last page ";
Str + = "page:" + getCurrenPages () + "/" + getTotalPages () + "page ";
Str + = MaxLine + "entries/page" + "Total" + Total + "entries ";
String pageNum;
Return str;
}
}
|
Database Connection file Conn. java
| The code is as follows: |
Copy code |
Package com. kanba. connection
Import java. SQL .*;
Public class Conn {
Public Conn (){}
// This is the code used to connect to the database. It can also be implemented through connection pools and other technologies.
String driverClass = "org. gjt. mm. mysql. Driver ";
String url = "jdbc: mysql: // localhost/test? User = root & password = 0 & useUnicode = true & characterEncoding = gb2312 ";
String username = "root ";
String password = "pwd ";
Connection conn = null; // Initialization
Public Connection getConnection ()
{
Try {
Class. forName (driverClass); // load the driver
// System. out. println ("jiazaichenggong ");
} Catch (ClassNotFoundException e ){
E. printStackTrace ();
}
Try {
Conn = DriverManager. getConnection (url); // create a connection conn
} Catch (SQLException e ){
E. printStackTrace ();
}
Return conn;
}
}
Paging test file page. jsp
<% @ Page language = "java" import = "java. SQL. *, com. kanba. tools. *" %>
<% @ Page contentType = "text/html; charset = gb2312" %>
<Jsp: useBean id = "pq" scope = "page" class = "com. kanba. tools. PageQuery"/>
<Html>
<Body bgcolor = "#8BA9C9">
<Table bgcolor = "# fecda9" cellspacing = 0>
<%
String query = "SELECT * FROM users"; // note that this "FROM" must be capitalized, and the total number of rows must be determined by the query statement after the FROM statement in the program.
ResultSet rs = pq. myQuery (query, request );
String bar = pq. PageLegend (); // read the paging prompt bar
Out. println ("<tr> <td colspan = 2>" + bar + "</td> </tr> ");
Out. println ("<tr> <td colspan = 2> While (rs. next ()){
// Description: rs. getString (2) is the position corresponding to the column name. Do not use rs. getString ("colmn_name").
%>
<Tr> <td> <% = rs. getString (1) %> </td> <% = rs. getString (2) %> </td> </tr>
<%}
Rs. close; pq. close ();
%>
</Table>
</Body>
</Html> |
