Today I wrote a MySQL paging javabean, which is implemented using MySQL LIMIT.
SQL = "SELECT * FROM Test LIMIT 5, 10 ";
This statement reads 10 records from the fifth record. This bean has no function to connect to the database,
You can use your own class to connect to the database. Of course, you can use the bad dbClass. java that I wrote to connect to, ^ _ ^
The source code of the three programs is provided here.
DbClass. java -- used to connect to the MySQL database.
PageQuery. java -- rewrite the ResultSet returned by dbClass to enable paging.
Example. jsp -- jsp file. We can see that I only use two lines to implement the paging function. Of course,
SQL statements are not encouraged to be written directly in jsp, so this is done to make it clear to everyone.
The level of self-knowledge is not high. I just want to introduce myself to others. I hope you can tell me what are the mistakes and omissions.
========================================= Example. jsp ==========================================
<% @ Page language = "java" import = "java. SQL. *, dbclass. *" %>
<% @ Page contentType = "text/html; charset = gb2312" %>
<Jsp: useBean id = "pq" scope = "page" class = "dbclass. PageQuery"/>
<Html>
<Body bgcolor = "#8BA9C9">
<Table bgcolor = "# fecda9" cellspacing = 0>
<%
String query = "SELECT * FROM systempass"; // note that this "FROM" must be capitalized.
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 () {%>
<Tr> <td> <% = rs. getString (9) %> </td> <% = rs. getString (10) %> </td> </tr>
<% }%>
</Table>
</Body>
</Html>
======================================= PageQuery. java ========================================
Package dbclass;
/**
* PageQuery v 1.0
* This class is called TViewPage, written in php by sharetop.
* My colleague Macro used PHP to rewrite this class and added many features.
* I feel that the encapsulation is very good and convenient to use. After using JSP, I will have
* The idea was rewritten using JSP. This time, many functions were saved for the sake of conciseness,
* Try to make it readable and add more functions as soon as possible,
*
* Mender:
* Jeru Liu
* Homepage:
* Http://www.cyberlabs.com /~ Jeru/
* Email: jeru@163.net
*
* This class does not provide the function of connecting to the database, so you need to open the corresponding database externally.
* The external display format must be customized.
*/
Import java. util .*;
Import java. SQL .*;
Import java. io .*;
Import javax. servlet .*;
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;
DbClass db; // object of dbclass
// Constructer do nothing
Public PageQuery (){
// Ten lines are displayed on each page.
MaxLine = 10;
Db = new dbClass ();
}
***************
// 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;
}
// 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
// You can change the style you like
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 ";
Return str;
}
}
==================================== DbClass. java ========================================
/**
* A class use to connect the MySQL database and do some query
* Use mm. MySQL. Drive
* Jeru Liu, November 2, 2000, ver-1.1
*
*/
Package dbclass;
Import java. SQL .*;
Public class dbClass {
// Public: connection parameters
String dbName = "Kernel ";
String Login = "root ";
String Password = "MySQL ";
String DBDriver = "org. gjt. mm. MySQL. Driver ";
String ConnStr = "jdbc: MySQL: // localhost/" + dbName + "? User = "+ Login +"; password = "+ Password;
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
ResultSetMetaData resultsMeta = null;
Int rows = 0;
// Public: constructor to load driver and connect db
Public dbClass (){
// Load mm. MySQL. driver
Try
{
Class. forName ("org. gjt. mm. MySQL. Driver ");
}
// Display corresponding error message when onload error occur
Catch (java. lang. ClassNotFoundException e)
{
System. out. println ("Class not found exception occur. Message is :");
System. out. println (e. getMessage ());
}
// Establish connection to the database throught driver
Try
{
Con = DriverManager. getConnection (ConnStr );
}
// Display SQL error message
Catch (SQLException e)
{
System. out. print ("SQL Exception occur. Message is :");
System. out. print (e. getMessage ());
}
}
// Perform a query with records returned
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;
}
// Perform a query without records returned
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;
}
}
// Return the num of columns
Public int getColumns ()
{
Int columns = 0;
Try
{
This. resultsMeta = this. rs. getMetaData ();
Columns = this. resultsMeta. getColumnCount ();
}
Catch (SQLException e ){}
Return columns;
}
// Return the num of rows
Public int getRows ()
{
Return this. rows;
}
Public String getDBName (){
Return this. dbName;
}
}
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
