mysql| Page | Shows the page display with JavaBean to achieve MySQL
0 0 0 0 0 0 0 0 0 0 0 0 0-0
0 Author: Liu Yan Date: 2000-02-04 jeru@163.net
http://www.cyberlabs.com/~jeru/0
0 Welcome to visit Java People for more information
0 0 0 0 0 0 0 0 0 0 0 0 0-0
Today wrote a MySQL paging JavaBean, is used in MySQL LIMIT to achieve.
sql = "SELECT * from Test LIMIT 5, 10";
From the fifth record to read down 10 records, this bean does not connect the database function,
You can use your own class to link the database, of course, you can use my bad writing Dbclass.java to connect, ^_^
This gives the source code for three programs.
Dbclass.java--Used to connect to the MySQL database.
Pagequery.java-Rewrites the ResultSet returned by Dbclass to provide paging functionality.
example.jsp-JSP file, you can see, I only used two lines to achieve the paging function, of course,
SQL statements are not encouraged to write directly in the JSP, here in order to let everyone see clearly, so do it.
Self-Knowledge level is not high, just want to initiate, what mistakes and omissions are also looking at the master pointed out.
=========================== example.jsp ===================================
<%@ page language= "java" import= "java.sql.*, dbclass.*"%>
<%@ page contenttype= "text/html; charset=gb2312 "%>
<jsp:usebean id= "PQ" scope= "page" class= "Dbclass. Pagequery "/>
<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 page-tip 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><td><%=rs.getstring (a)%></td></ Tr>
<%}%>
</table>
</body>
=========================== Pagequery.java ===================================
Package dbclass;
/**
* Pagequery v 1.0
* This class is originally called Tviewpage, author Sharetop, written in PHP.
* Colleague Macro has rewritten this class with PHP and added a lot of features.
* I feel the encapsulation is very good, very convenient to use, after using JSP, there will be
* Ideas with JSP to rewrite, this time for the sake of simplicity, I save a lot of functions,
* Try to make it good to read, later to be free to add more features,
*
* Mender:
* Jeru Liu
* Homepage:
* http://www.cyberlabs.com/~jeru/
* Email:jeru@163.net
*
* This class does not provide the ability to connect to the database, so you need to open the appropriate database externally.
* You need to customize the data display format externally.
*/
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; Total Records
int maxline; Record number of records displayed per page
ResultSet rs; read out the results
int tpages; Total pages
int cpages; Current page
String Pagequery; Pagination shows the parameters to pass
String Query; Query statement
String Querypart; Query section after "from"
String FilePath;
Dbclass DB; Object of Dbclass
Constructer do nothing
Public Pagequery () {
Show 10 rows per page
Maxline = 10;
db = new Dbclass ();
}
Read Records ***************
Primary work function, reading the corresponding record from the table according to the conditions given
Public ResultSet myquery (String query, HttpServletRequest req) throws SQLException {
String Query_part, OS;
int begin, offset;
To intercept the query statement after "from"
Begin = Query.indexof ("from");
Query_part = query.substring (Begin, Query.length ()). Trim ();
Calculate offset
OS = Req.getparameter ("offset");
if (OS = null) Offset = 0;
else Offset = integer.parseint (OS);
Get file name
FilePath = Req.getrequesturi ();
query = query;
Querypart = Query_part;
Calculate the total number of record bars
String SQL = "Select Count (*) as Total" + this. Querypart;
rs = Db.executequery (SQL);
if (Rs.next ())
Total = Rs.getint (1);
Set current page and total pages
Tpages = (int) Math.ceil (double) this. Total/this. Maxline);
Cpages = (int) Math.floor (double) offset/this. MAXLINE+1);
According to the conditions, take out the required records
if (Total > 0) {
SQL = Query + "LIMIT" + Offset + "," + maxline;
rs = Db.executequery (SQL);
}
Return RS;
}
Show Total Pages
public int gettotalpages () {
return tpages;
}
Displays the current number of pages
public int getcurrenpages () {
return cpages;
}
Show page Flip Tip bar *************
Display first, Next, Prev, last
You can change it into the style you like.
Public String Pagelegend () {
String str = "";
INT-I, Next, Prev, last;
i = 0;
Next = Offset + Maxline;
prev = Offset-maxline;
Last = (this. TPAGES-1) * Maxline;
if (Offset >= maxline)
STR + + "<a href=" + FilePath + "? offset=" + First + "> Home </A>";
else str + + "Home";
if (prev >= 0)
STR + + "<a href=" + FilePath + "? offset=" + prev + "> front page </A>";
else str + + "front page";
if (Next < total)
STR + + "<a href=" + FilePath + "? offset=" + Next + "> Back page </A>";
else str + + "Back Page";
if (tpages!= 0 && cpages < tpages)
STR + + "<a href=" + FilePath + "? offset=" + Last + "> End </A>";
else str = "Last";
STR + + "page:" + getcurrenpages () + "/" + gettotalpages () + "page";
STR + + Maxline + "strip/page" + "altogether" + Total + "bar";
return str;
}
}
=========================== Dbclass.java ===================================
/**
* A class use to connect the MySQL database and do some query
* Use MM. Mysql.drive
* Jeru Liu, November 2, 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+ ";p assword=" +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.executequery (SQL);
while (Rs.next ())
This.rows + +;
rs = stmt.executequery (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.executeupdate (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;
}
}
