First, let’s introduce a simple and neat paginator: bootstrap-paginator. You can refer to: Bootstrap Paginator. Detailed explanation of the use of the paginator paging plugin. This article is used for learning.
Effect screenshot:
GitHub official download address: https://github.com/lyonlai/bootstrap-paginator
Let’s introduce the implementation process of JSP paging display based on this paging tool in detail (Note: Compared with the original web page, I have hidden a lot of unnecessary content, and this example only focuses on the implementation of paging display)
1. Why do you need to display paging?
This blog post explains it thoroughly: the principles of paging technology and the significance and methods of paging (I)
2. JSP page part , here directly connect to the SqlServer2005 database with JDBC in the JSP page to query data (in actual implementation, it is not recommended to encapsulate complex business logic in the JSP page, the JSP page should only be responsible for display; the response to the client, business logic calls, and result forwarding should be completed by Servlet)
The code is as follows:
<%@ page import="PaginationExample.*" %><%@ page import="java.util.*"%><%@ page import="java.sql.*"%><%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%! private static final int pageSize = 20; //Set the number of records displayed per page (currently 20 records displayed per page)%><% request.setCharacterEncoding("UTF-8"); //Set the content submitted by the client to the servlet as UTF-8 encoding response.setCharacterEncoding("UTF-8"); //Set the content sent back to the servlet to the client as UTF-8 encoding response.setContentType("text/html;charset=UTF-8"); //Inform the browser to parse the content in UTF-8 format String pageNoStr = request.getParameter("pageNoStr"); //Receive the number of pages to be displayed by the client int pageNo = 1; //The number of pages to be displayed int totalPages = 1; //Total number of pages//Check and set pageNo if (pageNoStr != null && !pageNoStr.equals("")) { try { pageNo = Integer.parseInt(pageNoStr); if (pageNo < 1) { // If pageNo is less than 1, the first page is displayed by default pageNo = 1; } } catch (NumberFormatException e) { // When the pageNo obtained (current number of pages) is illegal, the first page is displayed by default pageNo = 1; } } else { // Other cases where pageNo has not been retrieved are displayed by default pageNo = 1; } /* ======================================================================================================================== */ /* Get the total number of pages in the database after the records are paged by the specified number (pageSize)*/ Connection totalConn = null; Statement totalStmt = null; ResultSet totalRs = null; try { totalConn = DBUtil.getConnection(); // Generate sql statement String sqlGetTotalPages = "select count(*) from alldata"; //Get the total number of records totalStmt = totalConn.createStatement(); totalRs = totalStmt.executeQuery(sqlGetTotalPages); totalRs.next(); int countResult = totalRs.getInt(1); //Get the total number of pages totalPages = countResult % pageSize == 0 ? countResult / pageSize : (int)(countResult / pageSize) + 1; } catch (SQLException e) { System.out.println("An error occurred in history query, the operation was not completed!"); e.printStackTrace(); } finally { DBUtil.close(totalRs); DBUtil.close(totalStmt); DBUtil.close(totalConn); } /* If the number of pages is greater than the total number of pages, the last page is displayed by default*/ if (pageNo > totalPages) { pageNo = totalPages; } /* Get the observation records to be displayed in the current page in the database, and use a List to store the records*/ List<Record> records = new ArrayList<Record>(); Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; int startIndex = (pageNo - 1) * pageSize + 1; int endIndex = pageNo * pageSize; try { conn = DBUtil.getConnection(); String sql = "select * from (select row_number() over(order by data_taizhan_num, data_date asc) as 'num', * from alldata) as temp where num between " + startIndex + " and " + endIndex; pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery(); while (rs.next()) { //Take out the data of each record and encapsulate it into a Record object Record r = new Record(); r.setTaizhan_num(rs.getString(2)); r.setDate(rs.getTimestamp(3)); r.setTem(rs.getString(4)); r.setHum(rs.getString(5)); r.setPa(rs.getString(6)); r.setRain(rs.getString(7)); r.setWin_dir(rs.getString(8)); r.setWin_sp(rs.getString(9)); records.add(r); //Put the encapsulated Record object into the list container} } catch (SQLException e) { System.out.println("Query error, operation not completed!"); e.printStackTrace(); } finally { DBUtil.close(rs); DBUtil.close(pstmt); DBUtil.close(conn); }System.out.println(totalPages); System.out.println(pageNo);/* ==================================================================================================*/ %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html lang="zh-CN"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <%-- Run the latest rendering mode in IE--%> <meta name="viewport" content="width=device-width, initial-scale=1"> <%-- Initialize mobile browsing display-%> <meta name="Author" content="Dreamer-1."> <link rel="stylesheet" href="css/bootstrap.css"> <link rel="stylesheet" href="css/recordSearchResult.css"> <script type="text/javascript" src="js/jquery-1.12.3.min.js"></script> <script type="text/javascript" src="js/bootstrap.min.js"></script> <script type="text/javascript" src="js/bootstrap-paginator.min.js"></script> <title>- Search record-</title> </head> <body> <div> <div> <!-- Display data records using forms--> <form> <table > <% if (records == null || records.size() == 0) { out.println("<tr><td><h4><strong>No records that meet the requirements, why not try to change the search criteria~</strong></h4></td></tr>"); } else { %> <tr> <td><h4><strong>Observation station</strong></h4></td> <% Record r = records.get(0); if (r.getTem() != null) { out.println("<td><h4><strong>Temperature(℃)</strong></h4></td>"); } if (r.getHum() != null) { out.println("<td><h4><strong>Humidity(%)</strong></h4></td>"); } if (r.getPa() != null) { out.println("<td><h4><strong>Pressure(hPa)</strong></h4></td>"); } if (r.getRain() != null) { out.println("<td><h4><strong>Rainfall(mm)</strong></h4></td>"); } if (r.getWin_dir() != null) { out.println("<td><h4><strong>Wind direction(°)</strong></h4></td>"); } if (r.getWin_sp() != null) { out.println("<td><h4><strong>Wind speed(m/s)</strong></h4></td>"); } %> <td><h4><strong>Observation time</strong></h4></td> </tr> <% } %> <% if (records != null && records.size() != 0) { for (Record r : records) { %> <tr> <td><%= r.getTaizhan_num() %></td> <% if (r.getTem() != null) { out.println("<td>" + r.getTem() + "</td>"); } if (r.getHum() != null) { out.println("<td>"+ r.getHum() +"</td>"); } if (r.getPa() != null) { out.println("<td>" + r.getPa() + "</td>"); } if (r.getRain() != null) { out.println("<td>" + r.getRain() + "</td>"); } if (r.getWin_dir() != null) { out.println("<td>" + r.getWin_dir() + "</td>"); } if (r.getWin_sp() != null) { out.println("<td>" + r.getWin_sp() + "</td>"); } %> <td><%= r.getDate() %></td> </tr> <% } %> </table> <!-- Pagination display div --> <div align="center"> <ul id="paginator"></ul> </div> </form> <% } %> </div> </div> <script type='text/javascript'> var options = { bootstrapMajorVersion: 3, //bootstrap version size: 'normal', itemTexts: function (type, page, current) { switch (type) { case "first": return "Home"; case "prev": return "<i class='fa fa-caret-left'></i> Previous page"; case "next": return "Next page<i class='fa fa-caret-right'></i>"; case "last": return "last page"; case "page": return page; } }, tooltipTitles: function (type, page, current) { switch (type) { case "first": return "Homepage"; case "prev": return "Previous page"; case "next": return "next page"; case "last": return "last page"; case "page": return "Thread" + page + "page"; } }, pageUrl: function(type, page, current){ return "showInfoSearchResult.jsp?pageNoStr="+page; //Skip to the selected page}, numberOfPages: 6, //Number of options to display "Which page" is currentlyPage: <%= pageNo %>, //Current pages totalPages: <%= totalPages %> //Total pages} $('#paginator').bootstrapPaginator(options); </script> </body></html>3. Regarding the Record and DBUtil classes used in this example:
The Record class is an ordinary Java class that encapsulates data and only provides get/set methods. Its properties correspond to the fields contained in the database table one by one. The code is as follows:
package PaginationExample;import java.sql.*;/** * Encapsulate meteorological data information* @author zhong * */public class Record { private String taizhan_num; //Station name private String tem; //Temperature private String hum; //Humidity private String pa; //Pressure private String rain; //Rainfall private String win_dir; //Wind direction private String win_sp; //Wind speed private Timestamp date; //Observation date (original format) /** * Get the name of the station that generated the observation record; * @return station name*/ public String getTaizhan_num() { return taizhan_num; } /** * Set the name of the station that produces the observation record; * @param taizhan_num The station name to be set*/ public void setTaizhan_num(String taizhan_num) { this.taizhan_num = taizhan_num; } /** * Get the temperature; * @return Temperature value*/ public String getTem() { return tem; } /** * Set the temperature; * @param tem The temperature value to be set*/ public void setTem(String tem) { this.tem = tem; } /** * Get humidity; * @return Humidity value*/ public String getHum() { return hum; } /** * Set humidity; * @param hum Hum To be set*/ public void setHum(String hum) { this.hum = hum; } /** * Get pressure; * @return Pressure value*/ public String getPa() { return pa; } /** * Set pressure; * @param pa To be set*/ public void setPa(String pa) { this.pa = pa; } /** * Get rainfall; * @return Rainfall value */ public String getRain() { return rain; } /** * Set rainfall; * @param rain To be set rain value */ public void setRain(String rain) { this.rain = rain; } /** * Get wind direction; * @return Wind direction value */ public String getWin_dir() { return win_dir; } /** * Set wind direction; * @param win_dir To be set wind direction value */ public void setWin_dir(String win_dir) { this.win_dir = win_dir; } /** * Get wind speed; * @return Wind speed value */ public String getWin_sp() { return win_sp; } /** * Set the wind direction; * @param win_sp The wind direction value to be set*/ public void setWin_sp(String win_sp) { this.win_sp = win_sp; } /** * Get the observation date; * @return observation date*/ public Timestamp getDate() { return date; } /** * Set the observation date; * @param date Observation date value*/ public void setDate(Timestamp date) { this.date = date; } }Screenshot of the corresponding alldata table:
The DBUtil class is a database tool class that provides database-related connections, Statements, etc. with the following code:
package PaginationExample;import java.sql.*;import org.apache.tomcat.jdbc.pool.DataSource;import org.apache.tomcat.jdbc.pool.PoolProperties;/** * Database tool class (using tomcat jdbc pool) * @author zhong * */public class DBUtil { private static DataSource ds; static { //Configure tomcat jdbc pool (connection pool) PoolProperties p = new PoolProperties(); p.setUrl("jdbc:sqlserver://localhost:1433; DatabaseName=weather"); //Set the connected url p.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //Load the database driver p.setUsername("sa"); //Username for remote connection p.setPassword("2003NianDeDiYiChangXue"); //Password p.setJmxEnabled(true); p.setTestWhileIdle(false); p.setTestOnBorrow(true); p.setValidationQuery("SELECT 1"); p.setTestOnReturn(false); p.setValidationInterval(30000); p.setTimeBetweenEvictionRunsMillis(30000); p.setMaxActive(100); p.setInitialSize(10); p.setMaxWait(10000); p.setRemoveAbandonedTimeout(60); p.setMinEvictableIdleTimeMillis(30000); p.setMinIdle(10); p.setLogAbandoned(true); p.setRemoveAbandoned(true); p.setJdbcInterceptors( "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"); ds = new DataSource(); ds.setPoolProperties(p); } private DBUtil() {} /** * Get a database connection (Connection); * @return Database Connection */ public static Connection getConnection() { Connection conn = null; try { conn = ds.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * Close the incoming Connection; * @param conn Connection to be closed */ public static void close(Connection conn) { try { if (conn != null) { conn.close(); conn = null; } } catch (SQLException e) { e.printStackTrace(); } } /** * Close the incoming Statement; * @param stmt Statement to be closed */ public static void close(Statement stmt) { try { if (stmt != null) { stmt.close(); stmt = null; } } catch (SQLException e) { e.printStackTrace(); } } /** * Close the incoming ResultSet; * @param rs ResultSet to be closed */ public static void close(ResultSet rs) { try { if (rs != null) { rs.close(); rs = null; } } catch (SQLException e) { e.printStackTrace(); } } }4. Supplementary Note:
①: When SQLServer implements paging, it is necessary to use the ROW_NUMBER() function to generate a column that records the row number separately, so as to facilitate the subsequent paging of the corresponding row number interval. example:
I saw that there was a column in front of which had a row number named num with the field name of the row number;
(If the primary key id in the table is an automatically incremented number, you can also use the id to retrieve the record in segments, but the premise is that the id must be continuously and automatically incremented)
For more information on implementing paging by ROW_NUMBER() function, please refer to: SQL Server's implementation method of using row_number paging
②: MySQL pagination is much simpler to implement, just use the limit keyword. example:
select * from table1 order by id asc limit 3, 2 means that after sorting the data in table1 by id value (asc order), starting from the third row, take the next two rows of records (i.e. the fourth and fifth row records)
③: For the specific usage of bootstrap-paginator, you can refer to the official document (located in the document folder after decompression). The official document is very well written and simple and easy to understand.
When using the bootstrap V3 version, you should use the <ul> tag to display bootstrap-paginator, and indicate the version of bootstrap used in the configuration item (see my jsp example page writing method).
(The bootstrap V2 version can be used directly using the <div> tag in the sample document)
④: Commonly used formulas for pagination: Suppose the number of pages to be displayed is n, and m pieces of data are displayed on each page, then the starting position (that is, the startIndex in the jsp example) of the data to be retrieved is: (n-1)*m+1, and the end position (endIndex) is: n*m
If you still want to study in depth, you can click here to study and attach 3 exciting topics to you:
Bootstrap learning tutorial
Bootstrap practical tutorial
Bootstrap plug-in usage tutorial
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.