This article describes the Hibernate framework data paging technology. Share it for your reference, as follows:
1. Basic ideas of data paging mechanism:
(1) Determine the recording span, that is, determine the number of records displayed on each page, which can be determined according to the actual situation.
(2) Get the total number of records, that is, get the total number of records to be displayed on the page. The purpose is to determine the total number of distributions based on this number.
(3) Determine the total number of pages after paging. You can use the formula: "Total number of pages = (total number of records - 1) / Number of records displayed per page + 1".
(4) Display data according to the current number of pages. If the number of pages is less than 1, it is made equal to 1; if it is greater than the maximum number of pages, it is made equal to the maximum number of pages.
(5) Display query results through For and While loop statement distribution.
2. Get the previous n records:
SQL syntax:
SELECT TOP nFROM tableWHERE ...ORDER BY ...
For example: Get the first 4 records
select top 4 * from car
3. Obtain paging data:
String sql = "select top"+pagesize+"* from car where id notin (select top "+(page-1)*pagesize+"id from car order by id ASC)order by id ASC
The parameters are as follows:
pagesize: Number of records displayed per page
page: Current page count
car: Data table name
4.MySQL database pagination
MySQL database provides a LIMIT function, which allows easy data paging.
The LIMIT function is used to limit the number of rows returned by a SELECT query statement.
grammar:
SELECT ...FROM tableWHERE...ORDER BY ...LIMIT [offset], rows
The parameters are as follows:
offset: Specifies the offset of the first row to be returned. The offset of the start line is 0. Is optional.
rows: Specifies the number of returned rows.
5.MySQL gets paginated data
/**** @param page Which page * @param pagesize Number of records displayed per page* @return Return result set*/public ResultSet findOrder(int page, int pagesize) { String strSql = "select * from car order by id limit " + (page - 1) * pagesize + "," + pagesize + ""; // Define SQL query statement Statement pstmt = null; ResultSet rs = null; // Define query result set object try { pstmt = conn.createStatement(); rs = pstmt.executeQuery(strSql); // Execute the query statement} catch (Exception e) { e.printStackTrace(); } finally { try { if (pstmt != null) { rs.close(); pstmt.close(); } } catch (Exception e) { e.printStackTrace(); } } return rs; // Return result set}6. Data paging example
6.1Paging project structure:
6.2Car.java program list:
package com.cdd.util;/** * Vehicle information* @author Xu Qiao Hui * */public class Car { private String Id; private String name;; private String brand; private String engineNum; private String state; private String remarks; public Car(int size){} public Car(){} public Car(String id, String name, String brand, String engineNum, String state, String remarks) { super(); Id = id; this.name = name; this.brand = brand; this.engineNum = engineNum; this.state = state; this.remarks = remarks; } public String getId() { return Id; } public void setId(String id) { Id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getBrand() { return brand; } public void setBrand(String brand) { this.brand = brand; } public String getEngineNum() { return engineNum; } public void setEngineNum(String engineNum) { this.engineNum = engineNum; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getRemarks() { return remarks; } public void setRemarks(String remarks) { this.remarks = remarks; }}6.3GetConn.java program list:
package com.cdd.util;import java.sql.*;public class GetConn { static { try { Class.forName("com.mysql.jdbc.Driver"); // Implement loading database driver in static blocks} catch (ClassNotFoundException e) { e.printStackTrace(); } } public Connection getConn() { Connection connection = null; String url = "jdbc:mysql://localhost:3306/oa"; String userName = "root"; String password = "1120"; try { connection = DriverManager.getConnection(url, userName, password); System.out.println("ok"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return connection; } public static void main(String[] args) { GetConn getConn = new GetConn(); getConn.getConn(); }}6.4PaginationUtil.java program list:
package com.cdd.util;import java.util.*;import java.sql.*;public class PaginationUtil { GetConn getConn = new GetConn(); Connection conn = getConn.getConn(); // Public List findGrade(int page, int pagesize) { String strSql = "select * from car order by id limit " + (page - 1) * pagesize + "," + pagesize + ""; // Define SQL query statement Statement pstmt = null; ResultSet rs = null; // Define the query result set object List lstList = new ArrayList(); // Define the collection object try { pstmt = conn.createStatement(); rs = pstmt.executeQuery(strSql); // Execute the query statement while (rs.next()) { // Loop through the query result set Car car = new Car(); // Create car car.setId(rs.getString("Id")); car.setName(rs.getString("name")); car.setBrand(rs.getString("brand")); car.setEngineNum(rs.getString("engineNum")); car.setState(rs.getString("state")); car.setRemarks(rs.getString("remarks")); lstList.add(car); // Add an object to the collection} } catch (Exception e) { e.printStackTrace(); } finally { try { if (pstmt != null) { rs.close(); pstmt.close(); } } catch (Exception e) { e.printStackTrace(); } } return lstList; // Return query collection object} /** * * @param page Which page * @param pagesize Number of records displayed per page* @return Return result set*/ public ResultSet findOrder(int page, int pagesize) { String strSql = "select * from car order by id limit " + (page - 1) * pagesize + "," + pagesize + ""; // Define SQL query statement Statement pstmt = null; ResultSet rs = null; // Define query result set object try { pstmt = conn.createStatement(); rs = pstmt.executeQuery(strSql); // Execute the query statement} catch (Exception e) { e.printStackTrace(); } finally { try { if (pstmt != null) { rs.close(); pstmt.close(); } } catch (Exception e) { e.printStackTrace(); } } return rs; // Return the result set} public int allPage(int pagesize) { int allp = 0; try { Statement pstmt = conn.createStatement(); pstmt.execute("select count(*) from car"); ResultSet rs = pstmt.getResultSet(); System.out.print("00"); rs.next(); int all = rs.getInt(1); System.out.print(all); allp = (all - 1) / pagesize + 1; System.out.println(allp); } catch (SQLException e) { e.printStackTrace(); } return allp; } public static void main(String[] args) { PaginationUtil pageinationUtil = new PaginationUtil(); List list = pageinationUtil.findGrade(2, 6); for (int i = 0; i < list.size(); i++) { Car car = (Car) list.get(i); System.out.println(car.getId() + " " + car.getName()); } }}6.5index.jsp program list:
<%@ page language="java" import="java.util.*,com.cdd.util.*;" pageEncoding="gbk"%><% String path = request.getContextPath(); String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + path + "/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html> <head> <base href="<%=basePath%>"> <title>My JSP 'index.jsp' starting page</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css"> --> </head> <body> <center> <h5> Vehicle information pagination display</h5> </center> <table align="center" bordercolor="#CC00CC"> <tr> <td> License plate number</td> <td> Vehicle name</td> <td> Brand</td> <td> Engine number</td> </tr> <% PaginationUtil paginationUtil = new PaginationUtil(); int pageNo = 0; if (request.getParameter("No") == null) { pageNo = 1; } else { pageNo = Integer.parseInt(request.getParameter("No")); } List cc = paginationUtil.findGrade(pageNo, 3); Iterator i = cc.iterator(); while (i.hasNext()) { Car car = (Car) i.next(); out.print("<tr><td>" + car.getId() + "</td>" + "<td>" + car.getName() + "</td>" + "<td>" + car.getBrand() + "</td>" + "<td>" + car.getEngineNum() + "</td></tr>"); } int all = paginationUtil.allPage(3); %> </table> <center> Total <%=all%> pages, the current page is the <%=pageNo%> page<% if (pageNo > 1) { %> <a href="index.jsp?No=<%=pageNo - 1%>">Previous page</a> <% } %> <% if (pageNo < all) { %> <a href="index.jsp?No=<%=pageNo + 1%>">Next page</a> <% } %> </center> </body></html>6.6 Access address:
http://x-pc:8080/Paging/index.jsp
6.7 Screenshot of the running result:
7. Hibernate Pagination
7.1HQL Pagination
HQL mainly implements data paging through the setFirstResult() method and the setMaxResults() method.
(1) The setFirstResult(int index) method is used to retrieve the data start index position, and the index position starts at 0.
(2) The setMaxResults(int amount) method is used to calculate the maximum number of records loaded at each time, and by default, it is from the set start index position to the last.
For example: Retrieve 5 records starting from index position 2
Query q = session.createQuery("form car");q.setFirstResult(2);q.setMaxResults(5);7.2QBC Pagination
For example: Retrieve 5 records starting from index position 2
Criteria c = session.createCriteria("form car");c.setFirstResult(2);c.setMaxResults(5);7.3 Data paging method:
/*** Use hql statement for page query* @param hql hql statement that needs to be query* @param offset first record index* @param pageSize Number of records to be displayed per page* @return All records of the current page*/public List findByPage(final String hql, final int offset, final int pageSize){ //Execute query through a HibernateCallback object List list = getHibernateTemplate() .executeFind(new HibernateCallback() { //Methods that must be implemented in the HibernateCallback interface public Object doInHibernate(Session session) throws HibernateException, SQLException { //Execute Hibernate pagination query List result = session.createQuery(hql) .setFirstResult(offset) .setMaxResults(pageSize) .list(); return result; } }); return list;}/** * Use hql statement for page query* @param hql hql statement that needs to be queryed* @param value If hql has a parameter that needs to be passed in, value is the parameter of the hql statement that is passed in * @param offset The first record index* @param pageSize The number of records that need to be displayed per page* @return All records of the current page*/public List findByPage(final String hql , final Object value , final int offset, final int pageSize){ //Execute query through a HibernateCallback object List list = getHibernateTemplate() .executeFind(new HibernateCallback() { //Methods that must be implemented to implement the HibernateCallback interface public Object doInHibernate(Session session) throws HibernateException, SQLException { //Execute Hibernate pagination query List result = session.createQuery(hql) //Pause parameters for hql statement.setParameter(0, value) .setFirstResult(offset) .setMaxResults(pageSize) .list(); return result; } }); return list;}/** * Use hql statement for pagination query* @param hql hql statement that needs to be queried* @param values If hql has multiple parameters to be passed in, values are the parameter array of hql passed in hql* @param offset first record index* @param pageSize Number of records to be displayed per page* @return All records on the current page*/public List findByPage(final String hql, final Object[] values, final int offset, final int pageSize){ //Execute query through a HibernateCallback object List list = getHibernateTemplate() .executeFind(new HibernateCallback() { //Methods that must be implemented to implement the HibernateCallback interface public Object doInHibernate(Session session) throws HibernateException, SQLException { //Execute Hibernate pagination query Query query = session.createQuery(hql); //Pause parameters for the hql statement for (int i = 0 ; i < values.length ; i++) { query.setParameter( i, values[i]); } List result = query.setFirstResult(offset) .setMaxResults(pageSize) .list(); return result; } }); return list;}I hope that the description in this article will be helpful to everyone's Java programming based on the Hibernate framework.