JSP read database to achieve paging technology analysis

Time:2021-5-9

This article describes the use of JavaBeans and JSP pages to achieve pagination of data display. The database used in the example is mysql.

1. Let’s look at JavaBeans first
Class name:
databaseBean.java:
The following is the code of databasebean.java:

Copy codeThe code is as follows:
package database_basic;
import java.sql.*;
import java.util.*;
public class databaseBean
{
//This is the default database connection method
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(){}
//The connection of database can be set flexibly through set method
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){}*/
//Make a connection
public String DBConnect()
{
String strExc=”Success!”;// Strexc is success by default. If an exception is thrown, that is, the database connection is unsuccessful, other throw information will be given in the following catches
try
{
Class.forName(DBDriver);
conn=DriverManager.getConnection(DBLocation);
}
catch(ClassNotFoundException e)
{
Strexc = “database driver not found, error prompt: < br >” + e.tostring();
}
catch(SQLException e)
{
Strexc = “SQL statement error, error prompt < br >” + e.tostring();
}
catch(Exception e)
{
Strexc = “error prompt: < br >” + e.tostring();
}
return (strExc);
}//then end of DBConnect
//Disconnect
public String DBDisconnect()
{
String strExc=”Success!”;// The default value of strexc is success. If an exception is thrown, that is, the disconnection of the database is unsuccessful, other throw information will be given in the following catches
try
{
if(conn!=null)conn.close();
}
catch(SQLException e)
{
strExc=e.toString();
}
return (strExc);
}
//Return a result set by passing in SQL statements
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
//Calculate and return the total number of pages by passing in SQL statements and PageSize (the number of results displayed per page)
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);// Get the result set through the incoming SQL
while(rs.next())
totalRows++;// Let RS count one by one, and then calculate the total number of entries in the returned result set through totalrows +
}
catch(SQLException e){}
rs=null;
//The algorithm gets the total pages-1 / PageSize + 1 and returns the result. Totalrows is the total number of entries in the returned result set, and PageSize is the number of entries displayed on each page
return((totalRows-1)/pageSize+1);
}
//By passing in SQL statements, the number of items (PageSize) and page number displayed on each page, a result set is obtained
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 multiply the number of items displayed on each page by the page number to calculate the number of the last row of results. Any result whose number is greater than the maxRows will be dropped
s.setMaxRows(pageSize*pageNumber);
rs=s.executeQuery(sql);
}
catch(SQLException e){}
//Absolutelocation = PageSize * (pagenumber-1) this expression calculates the number of the last result on the previous page (if there is this page, the number of result entries displayed on the previous page must be PageSize)
absoluteLocation=pageSize*(pageNumber-1);
try
{
//The function of this for loop is to locate the result set RS to the last result before this page
for(int i=0;i<absoluteLocation;i++)
{
rs.next();
}
}
catch(SQLException e) { }
//At this time, the returned result set is clamped at both ends, which is the result to be displayed in the page number
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. Analyzing JSP pages
Page name:
admin_show.jsp
Page code:

Copy codeThe code is as follows:
<%@ page errorPage=”error.jsp”%>
<%@ page contentType=”text/html;charset=gb2312″%>
<%@ page import=”java.util.*”%>
<%@ page import=”java.sql.*”%>
//Import database_ The databasebean class under the basic package, alias is basicdb
<jsp:useBean scope=”page”/>
<%
String sql;
ResultSet rs;
int id;
String reply,Exc;
Exc=basicDB.DBConnect();// Establish a connection, if successful, return success! If it fails, the corresponding error message will be returned
if(!Exc.equals(“Success!”))
{
//basicDB.DBDisconnect();
throw new Exception(Exc);
}
int pageSize=10; // Define the number of data displayed per page
int currentPage=1; // Current page (the first page must be displayed for the first time! ~), Later “current page” is passed in by the pages parameter in the following page
int allPage=-1;
String pages=request.getParameter(“pages”);// Get the pages parameter in the page, which represents the “current page” to be displayed
if(pages!= null) currentPage=Integer.valueOf(pages).intValue();// This is an example of converting integer type to int type. The first time the page is executed, the page parameter is null and the current page = 1; When the page is executed again, the parameter pages will be assigned a value, CurrentPage = the int value of pages
sql=”select * from gbook order by id desc”;// In this way, the returned result set is sorted in desc descending order. The advantage is that the latest information is displayed in the front
allPage=basicDB.getTotalPage(sql,pageSize);// Get the total number of pages
rs=basicDB.getPagedRs(sql,pageSize,currentPage);// Get the result set to be displayed on the current page
%>
<table border=”0″ cellspacing=”1″ cellpadding=”3″ width=”590″ bgcolor=”#ffffff”>
<%
while(rs.next()){
id=rs.getInt(“id”);// Get the ID number in the database (result set)
%>
<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 it’s page <% = CurrentPage% >,
<%if(currentPage>1){%>
<!– If it is not on the first page, the “home page” link will be displayed
<A HREF=”admin_ Show. JSP? Pages = <% = (currentPage-1)% > “> Home Page < / a >
<%}
for(int i=1;i<=allPage;i++)
{
//Show links from 1, 2, 3, 4… To the last page
out.println(“<a href=admin_show.jsp?pages=”+i+”>”+i+”</a>”);
}
%>
<%if(currentPage<allPage){%>
<!– If it is not on the last page, the “last page” link will be displayed — >
<A HREF=”admin_ Show. JSP? Pages = <% = (CurrentPage + 1)% > “> last page < / a >
<%}%></td>
</tr>
</table>
}//then end of the class

3. Summary
This realization of pagination display program, there are several algorithms and implementation methods are relatively fixed and classic, for those who have not written pagination program, should be inspired.

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]