Read notes on implementing JSP paging display database

js| Notes | page | data | database | This article describes the use of JavaBean and JSP pages to achieve data paging display, the example used in the database is MySQL.

1, first look at the JavaBean

Class Name:

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 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 Set method allows you to flexibly set up a database connection
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 default is success, if an exception is thrown, that is, the database connection is unsuccessful, 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 prompted:<br>" +e.tostring ();
}
catch (SQLException e)
{

strexc= "SQL statement error, error hint <br>" +e.tostring ();
}
catch (Exception e)
{
Strexc= "false Hint:<br>" +e.tostring ();
}

return (STREXC);
}//then End of Dbconnect

Disconnect
Public String Dbdisconnect ()
{
String strexc= "success!"; /strexc defaults to Success, if an exception is thrown, that is, if the database disconnect is unsuccessful, the following catch is given additional throw information

Try
{
if (conn!=null) conn.close ();
}
catch (SQLException e)
{
Strexc=e.tostring ();
}
return (STREXC);

}



Returns a result set by passing in an SQL statement
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

Calculates and returns a total number of pages by passing in the SQL statements and pagesize (numbers 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 result set through incoming SQL

while (Rs.next ())
totalrows++;//let RS a number, after a number of times, through the totalrows++ also calculated the return result set total number of entries
}
catch (SQLException e) {}

Rs=null;
The total number of pages (TOTALROWS-1)/pagesize+1 is obtained by this algorithm, and the result is returned. Totalrows refers to the total number of entries that are returned in the result set, pagesize is the number of entries displayed per page
Return ((totalRows-1)/pagesize+1);
}


By passing in the SQL statement, the number of entries (pageSize) and page numbers displayed per page, get 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 displayed per page multiplied by the page number, the last line of results is calculated, and any number greater than this maxrows will be drop
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 (the number of result entries displayed on the previous page is definitely pageSize if this page is available)
absolutelocation=pagesize* (PAGENUMBER-1);


Try
{

The purpose of this for loop is to have the result set RS navigate to the last result before this page
for (int i=0;i<absolutelocation;i++)
{
Rs.next ();
}
}
catch (SQLException e) {}
The result set returned at this time is a two-clip, which is the result of the page (pagenumber) to display
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, Analysis 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.*"%>
Import the Databasebean class under the Database_basic package, 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 a connection, if successful, return success! if failure, then return the corresponding error message

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 one to be shown is the first page of!~), and the next "current page" is passed through the pages parameter in the page that appears below
int allpage=-1;
String pages=request.getparameter ("pages"), or get pages in the page, this parameter represents the page that is to be displayed "current Page"

if (pages!=null) currentpage=integer.valueof (pages). Intvalue ()//This is an Integer int-type example, the first time the page is executed, Pages this parameter is null,currentpage=1; When the page is executed again, the parameters pages will be assigned and the int value of Currentpage=pages

Sql= "SELECT * from Gbook ORDER by id DESC";//This returns the result assembly in a DESC descending order, with the benefit that the latest information is displayed in the previous

Allpage=basicdb.gettotalpage (sql,pagesize);/Get total page number

Rs=basicdb.getpagedrs (sql,pagesize,currentpage)//Get the result set to display for 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" >
&LT;TD >name:<%=rs.getstring ("leaver")%></td>
&LT;TD >time:<%=rs.getstring ("Leave_date")%></td>
&LT;TD >email:<%=rs.getstring ("Email")%></td>
&LT;TD ><div style= "Width:150;overflow:hidden;" >home:<%=rs.getstring ("homepage")%></div></td>
</tr>
<tr bgcolor= "#FFE3B9" >
&LT;TD colspan=4><font color= "#FF6600" >content:</font><br><br><%=rs.getstring (" Content ")%> </td>
</tr>
<%}%>

&LT;TR&GT;&LT;TD height= "1" ></td></tr>
<tr>
&LT;TD colspan=4 align=right bgcolor= "#FF6600" style= "Color:white"; >
Now is page <%=currentPage%>,
<%if (currentpage>1) {%>
<!--if it is not on the first page, show the "Home" link-->
<a href= "admin_show.jsp?pages=<%= (currentPage-1)%>" > Home </A>
<%}
for (int i=1;i<=allpage;i++)
{
Show 1, 2, 3, 4 ... Links 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, show the "Last" link-->
<a href= "admin_show.jsp?pages=<%= (currentpage+1)%>" > last page </A>
<%}%></td>
</tr>
</table>

}//then end of the class


3, Summary
This implementation of the paging display program, there are several algorithms and implementation methods are relatively fixed and classic, for those who do not write too much page procedure, should be inspired.



*************************************************************************************************************** *********
This analysis report is completed by Wang Zhiwei
The original "Implementation JSP paging display database (MySQL)" See: Xiaoxiang Network Forum Forum->web Programming--jsp-> implementation JSP paging display database (MySQL) Author: Fred Huang
*************************************************************************************************************** **********