JSP data paging instance: MySQL Paging

JSP database operations enable pagination of JSP data. This article provides an example of MySQL paging.

I. Preparations before running

Download a jar file of the mysql jdbc driver) and load it in CLASSPATH.

Create a MySQL database test

The database has a table: note with the field name (varchar)

Ii. Download and install

 
 

  
  
<% @ PageContentType="Text/html; charset = gb2312"%> 
  
  
<% Java. SQL. Connection sqlCon; // database Connection object
  
  
Java. SQL. Statement sqlStmt; // SQL Statement object
  
  
Java. SQL. ResultSet sqlRst; // result set object
  
  
Java. lang. String strCon; // database connection String
  
  
Java. lang. String strSQL; // SQL statement
  
  
Int intPageSize; // number of records displayed on one page
  
  
Int intRowCount; // The total number of records.
  
  
Int intPageCount; // the total number of pages.
  
  
Int intPage; // the page number to be displayed.
  
  
Java. lang. String strPage;
  
  
Int I;
  
  
// Set the number of records displayed on one page
  
  
IntPageSize=2;
  
  
// Obtain the page number to be displayed
  
  
StrPage=Request. GetParameter ("page ");
  
  
If (StrPage= Null ){
  
  
// Indicates that the QueryString parameter does not contain the page parameter. The first page of data is displayed.
  
  
IntPage=1;
  
  
} Else {
  
  
// Converts a string to an integer.
  
  
IntPage=Java. Lang. Integer. parseInt (strPage );
  
  
If (intPage< 1)IntPage=1;
  
  
}
  
  
// Load the JDBC driver
  
  
Class. forName ("org. gjt. mm. mysql. Driver"). newInstance ();
  
  
// Connect to the database
  
  
SqlCon=Java. SQL. DriverManager. getConnection ("jdbc: mysql: // localhost/test ");
  
  
// Create a statement object
  
  
SqlStmt=SqlCon. CreateStatement (java. SQL. ResultSet. TYPE_SCROLL_INSENSITIVE, java. SQL.
  
  
ResultSet. CONCUR_READ_ONLY); // execute the SQL statement
  
  
StrSQL="Select name from note";
  
  
// Execute the SQL statement and obtain the result set
  
  
SqlRst=SqlStmt. ExecuteQuery (strSQL );
  
  
// Obtain the total number of records
  
  
SqlRst. last ();
  
  
IntRowCount=SqlRst. GetRow ();
  
  
// Calculate the total number of pages
  
  
IntPageCount= (IntRowCount + intPageSize-1)/intPageSize;
  
  
// Adjust the page number to be displayed
  
  
If (intPage>IntPageCount)IntPage=IntPageCount;
  
  
%> 
  
  
< Html> 
  
  
< Head> 
  
  
< Meta Http-equiv="Content-Type" Content="Text/html; charset = gb2312"> 
  
  
< Title>JSP database operation routine-JSP data pagination-JDBC 2.0-mysql</Title> 
  
  
</Head> 
  
  
< Body> 
  
  
< Table Border="1" Cellspacing="0" Cellpadding="0"> 
  
  
< Tr> 
  
  
< Th>Name</Th> 
  
  
</Tr> 
  
  
<% If (intPageCount>0)
  
  
{
  
  
// Locate the record pointer to the first record on the page to be displayed.
  
  
SqlRst. absolute (intPage-1) * intPageSize + 1 );
  
  
// Display data
  
  
I=0;
  
  
While (I< IntPageSize&&! SqlRst. isAfterLast () {%> 
  
  
< Tr> 
  
  
< Td> 
  
  
<% = SqlRst. getString (1) %> 
  
  
</Td> 
  
  
</Tr> 
  
  
<% SqlRst. next ();
  
  
I ++;
  
  
}
  
  
}
  
  
%> 
  
  
</Table> 
  
  
The<% = IntPage %>Total pages<% = IntPageCount %>Page
  
  
<% If (intPage< IntPageCount) {%>< A Href="Mysqlpage. jsp? Page = <% = intPage + 1%>">Next Page</><%} %> 
  
  
<% If (intPage>1) {%>< A Href="Mysqlpage. jsp? Page = <% = intPage-1 %>">Previous Page</><%} %> 
  
  
</Body> 
  
  
</Html> 
  
  
<%
  
  
// Close the result set
  
  
SqlRst. close ();
  
  
// Close the SQL statement object
  
  
SqlStmt. close ();
  
  
// Close the database
  
  
SqlCon. close ();
  
  
%> 
 
 

How to run JSP data paging?

Save the code as a file test. jsp

Under Orion Application Server:

Copy to the default-web-app directory of orion, and run the following command:

 
 

  
  
http://localhost:port/test.jsp 
 
 

Access Test

You can run Resin, Tomcat, JWS, and so on. This section describes the implementation of JSP data paging.

1. JSP Database Operations routine JDBC-ODBC)
2. Basic preparation for learning JSP: JDK installation and configuration
3. Java Servlet Introduction: Perfect Combination with JSP
4. Integrate JSP and PHP in Apache
5. Java Servlets (JSP) Development Environment