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:
 
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.

Related articles: