Pagination
Task Description
In the previous excercise, we have seleceted the data from the database and showed on the webpage,
But most likely in our real life, the website would contain huge amount of data, we certainly couldn't show them all in one page,
which would be user-unfriendly, and it would be also a huge burden for the server as the data amount is too large.
Under this circumstance, we would need pagination for our pages.
we would only show 10-20 rows or records on each page,
when the user click next page, fetch another 10-20 rows of records to display.
Analysis
1. We need to know the total number of records.
this could be done by querying in the database,
variable TotalNumber
2. We need to know how many records we need to have on each page.
variable PageSize;
in this way we could get to know the total number of pages.
PageNumber =TotalNumber/PageSize;
3. Query the database with parameters to get the records for certain pages.
we need to create a new method
public ResultSet SelectActor(int pageNumber, int PageSize);
Code and Test Result
Test Result:
Code:
1. NewFile.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <%@ page import= "src.util.*"%> <%@ page import= "java.sql.*"%> <%@ page import = "com.yang.Bean.actor" %> <%@ page import = "com.yang.dao.NewsDao" %> <!DOCTYPE html> <html> <head> <meta charset="ISO-8859-1"> <title>Insert title here</title> </head> <body> <form name = "SearchForm" method = "post" action = "doSearch.jsp"> <table> <tr align = "center" valign ="middle" bgcolor ="#CCCCCC" height="22"> <td>first Name</td> <td> Last Name</td> </tr> <% NewsDao my = new NewsDao(); ResultSet rs = (ResultSet) session.getAttribute("pageResultSet"); if (rs ==null){ %> <tr align ="center" valign = "middle"> <td colspan ="4"> No records! </td> </tr> <% } else my = (NewsDao) session.getAttribute("db"); while(rs !=null &&rs.next()) { %> <tr> <td><%=rs.getString("first_name") %> </td> <td><%=rs.getString("last_name") %> </td> </tr> <% } %> <tr> <td align ="center" colspan ="4"> <input type ="submit" name ="search" value = "Search the FirstPage"> </td> </tr> <tr bgcolor ="lightgrey"> <td colspan ="4" align = "center"> On Each Page <%=my.getPageSize() %> Records Currently on Page: <%=my.getCurrentPage() %> Total Pages : <%=my.getTotalPages() %> </td> </tr> <tr bgcolor ="lightgrey"> <td align="center" colspan ="4"> <%if (my.getCurrentPage()>1) {%> <a href ="doSearch.jsp?currentpage=1">FirstPage</a> <a href ="doSearch.jsp?currentpage= <%=my.getCurrentPage()-1 %>">Previous Page</a> <%} %> <%if (my.getCurrentPage()< my.getTotalPages()) {%> <a href ="doSearch.jsp?currentpage=<%=my.getCurrentPage() +1 %>">Next Page</a> <a href ="doSearch.jsp?currentpage= <%=my.getTotalPages()%>">Final Page</a> <%} %> </td> </tr> </table> </form> </body> </html>
2. doSearch.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%> <jsp:useBean id ="db" class= "com.yang.dao.NewsDao" scope ="page" /> <%@page import ="java.sql.*" %> <% String strpage = request.getParameter("currentpage"); if(strpage ==null||strpage.equals("")) strpage="1"; int currentpage = 1; try{ currentpage = Integer.parseInt(strpage); } catch(Exception e){ } db.SetPageInfo(currentpage); ResultSet rs = db.SelectActor(currentpage, 8); session.setAttribute("db", db); session.setAttribute("pageResultSet", rs); response.sendRedirect("NewFile.jsp"); %>
3. NewsDao.Java
package com.yang.dao; //Step 1, import the needed packages import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Date; import java.util.Properties; import com.yang.Bean.actor; import src.util.ConfigManager; /*USE mysql; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '831015'; FLUSH PRIVILEGES; */ public class NewsDao { // show 8 rows of records on each page. private int pageSize = 8; // Total number of records private int TotalNum = 0; //Total number of records private int PagesNum = 0; //CurrentPage Number private int Page_Current = 1; // JDBC driver name and database URL String JDBC_Driver = null; String url = null; String UserName = null; String Password = null; Connection conn = null; PreparedStatement stmt = null; public void SetPageInfo(int page) { String SQL ="Select * from actor"; //System.out.println(SQL); this.getConnection(); try { stmt = conn.prepareStatement(SQL); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } ResultSet rs = null; try { rs = stmt.executeQuery(); rs.last(); this.TotalNum = rs.getRow(); System.out.println(this.TotalNum); this.PagesNum = (TotalNum%pageSize==0)?(TotalNum/pageSize) :(TotalNum/pageSize)+1; System.out.println("PagesNum"+PagesNum); if(page<1) { this.Page_Current=1; } else if(page>PagesNum) { this.Page_Current=PagesNum; } else { this.Page_Current=page; } System.out.println("Page_current" + Page_Current); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public int getPageSize() { return pageSize; } public int getCurrentPage() { return Page_Current; } public int getTotalPages() { return PagesNum; } public int addActor(actor a) { String SQL = "insert into actor (actor_id, first_name, last_name, last_update) values(?,?,?,?)"; this.getConnection(); try { stmt = conn.prepareStatement(SQL); stmt.setInt(1, a.getId()); stmt.setString(2, a.getFirstName()); stmt.setString(3, a.getLastName()); stmt.setTimestamp(4, new Timestamp(a.getDate().getTime())); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("Executing the Update Query..."); int i = 0; try { i = stmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(i>0) { System.out.println("Successfully Inserted the data"); } return i; } public int DeleteActor(actor a) { String SQL = "delete from actor where actor_id =?"; this.getConnection(); try { stmt = conn.prepareStatement(SQL); stmt.setInt(1, a.getId()); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("Executing the Update Query..."); int i = 0; try { i = stmt.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(i>0) { System.out.println("Successfully Deleted record"); } return i; } public ResultSet SelectActor(int pageNumber, int PageSize) { String name =""; String SQL = "select * from actor limit " + (pageNumber-1)*PageSize+ "," + PageSize; //System.out.println(SQL); this.getConnection(); try { stmt = conn.prepareStatement(SQL); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } ResultSet rs = null; try { rs = stmt.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } public ResultSet GetParts(String subsql, String subsqlValue) { if (subsql ==null) subsql = ""; if(subsqlValue ==null) subsqlValue =""; String SQL = "select * from actor where " + subsql + "= '" +subsqlValue+"'"; System.out.println(SQL); this.getConnection(); try { stmt = conn.prepareStatement(SQL); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } ResultSet rs = null; try { rs = stmt.executeQuery(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return rs; } public void getConnection() { ConfigManager m = new ConfigManager(); JDBC_Driver = m.getString("jdbc.driver"); url = m.getString("jdbc.connection.url"); UserName = m.getString("jdbc.connection.username"); Password = m.getString("jdbc.connection.password"); try { //System.out.println("Connecting to Database..."); Class.forName(JDBC_Driver); conn = DriverManager.getConnection(url, UserName, Password); //System.out.println("Connected to Database..."); } catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); } } }
