A brief analysis of the paging technology of jsp reading database
- 2020-05-19 05:29:48
- OfStack
This article describes the paging display of data using javabean and jsp pages. The database used in the example is Mysql.
1. javabean first
The name of the class:
databaseBean.java:
The following is the code for databaseBean.java:
2. Analyze the jsp page
Page name:
admin_show.jsp
Page code:
3, summarize
This pagination display program, there are several algorithms and implementation methods are relatively fixed and classic, for those who have not written a page program, should be inspired.
1. javabean first
The name of the class:
databaseBean.java:
The following is the code for databaseBean.java:
package database_basic;
import java.sql.*;
import java.util.*;
public class databaseBean
{
// This is the default database connection
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(){}
// through set Method provides the flexibility to set up a connection to the database
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!";//strExc The default is Success , if an exception is thrown that the database connection was unsuccessful, then the following catch Is given other throw information
try
{
Class.forName(DBDriver);
conn=DriverManager.getConnection(DBLocation);
}
catch(ClassNotFoundException e)
{
strExc=" Database driver not found, error: <br>" +e.toString();
}
catch(SQLException e)
{
strExc="sql Statement error, error message <br>" +e.toString();
}
catch(Exception e)
{
strExc=" Error: <br>" +e.toString();
}
return (strExc);
}//then end of DBConnect
// disconnect
public String DBDisconnect()
{
String strExc="Success!";//strExc The default is Success , if an exception is thrown that the database disconnects unsuccessfully, then the following catch Is given other throw information
try
{
if(conn!=null)conn.close();
}
catch(SQLException e)
{
strExc=e.toString();
}
return (strExc);
}
// By passing in sql Statement to return 1 A result set
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();
rs=s.executeQuery(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
// By passing in sql Statements and pageSize Count and return the total number of pages
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();
rs=s.executeQuery(sql);// Through incoming sql Get the result set
while(rs.next())
totalRows++;// let rs1 A number, and we're done 1 Again, through totalRows++ The total number of entries in the returned result set is calculated
}
catch(SQLException e){}
rs=null;
// The total number of pages is given by this algorithm (totalRows-1)/pageSize+1 And return the result. totalRows Is the total number of entries returned in the result set, pageSize Is the number of items displayed per page
return((totalRows-1)/pageSize+1);
}
// By passing in sql Statement, the number of entries per page ( pageSize ) and page number, get 1 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 per page is multiplied by the page number to calculate the end 1 The number of the row result, any number greater than this maxrows The results will be drop
s.setMaxRows(pageSize*pageNumber);
rs=s.executeQuery(sql);
}
catch(SQLException e){}
//absoluteLocation = pageSize*(pageNumber-1) This expression evaluates up 1 The last page 1 Number of results (top if this page is available) 1 The number of resulting entries displayed on the page must be pageSize )
absoluteLocation=pageSize*(pageNumber-1);
try
{
// this for The function of the loop is to let the result set rs Position to the end before this page 1 A result of
for(int i=0;i<absoluteLocation;i++)
{
rs.next();
}
}
catch(SQLException e) { }
// At this point the result set is returned by both ends 1 Folder, which is the page ( pageNumber ) to display the results
return(rs);
}
public String execute_sql(String sql){
String strExc;
strExc="Success!";
if(conn!=null)
{
try{
PreparedStatement update;
update=conn.prepareStatement(sql);
update.execute();
}
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:
<%@ page errorPage="error.jsp"%>
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*"%>
// The import database_basic Under the bag databaseBean Class, 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 the connection and, if successful, return it Success! If a failure , The corresponding error message is returned
if(!Exc.equals("Success!"))
{
//basicDB.DBDisconnect();
throw new Exception(Exc);
}
int pageSize=10; // Define the number of data bars to display per page
int currentPage=1; // The current page ( The first 1 The second must be the first 1 Page! !~), The following "current page" appears from the page below pages Parameters of the incoming
int allPage=-1;
String pages=request.getParameter("pages");// Get on the page pages Parameter, which represents the "current page" of the page to be displayed
if(pages!=null) currentPage=Integer.valueOf(pages).intValue();// This is a 1 a Integer Turn type int Examples of type, no 1 The next time I execute this page, pages This parameter is null,currentPage = 1 ; Parameter when the page is executed again pages It's going to be assigned, currentPage = pages the int value
sql="select * from gbook order by id desc";// The result set returned in this way will be adopted desc In descending order, the advantage is that the most recent information is displayed at the front
allPage=basicDB.getTotalPage(sql,pageSize);// You get the total page number
rs=basicDB.getPagedRs(sql,pageSize,currentPage);// Gets the result set for the current page to display
%>
<table border="0" cellspacing="1" cellpadding="3" width="590" bgcolor="#ffffff">
<%
while(rs.next()){
id=rs.getInt("id");// Get to the database (result set) id Serial number
%>
<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><BR><%=rs.getString("content")%> </td>
</tr>
<%}%>
<tr><td height="1"></td></tr>
<tr>
<td colspan=4 align=right bgcolor="#FF6600" style="color:white;">
Now is the first <%=currentPage%> page ,
<%if(currentPage>1){%>
<!-- If not 1 Page, which displays the "home page" link -->
<A HREF="admin_show.jsp?pages=<%=(currentPage-1)%>"> Home page </A>
<%}
for(int i=1;i<=allPage;i++)
{
// show 1 , 2 , 3 , 4 ... In the end 1 Link to the page
out.println("<a href=admin_show.jsp?pages="+i+">"+i+"</a>");
}
%>
<%if(currentPage<allPage){%>
<!-- If not at the end 1 Page, displays the "last page" link -->
<A HREF="admin_show.jsp?pages=<%=(currentPage+1)%>"> At the end of the page </A>
<%}%></td>
</tr>
</table>
}//then end of the class
3, summarize
This pagination display program, there are several algorithms and implementation methods are relatively fixed and classic, for those who have not written a page program, should be inspired.