MySql implements page query function

First of all, we need to make it clear why we use paginated query. Because the data is huge, it is impossible to display all the data on the page. If all the data is displayed on the page, the query speed will be slow. Therefore, paginated query solves the problems of ① data query; ② performance optimization, etc. (other issues are welcome to be supplemented).

Pagination queries are also divided into true pagination and false pagination:

True paging: directly display the data found in the database in pages. The advantage is that changing the database data will not affect the query results. The disadvantage is that the speed is slightly slower.

False paging: Encapsulate all queried data into the list collection cache and execute the presentation layer method call. Since the data is encapsulated as a collection and put into memory, the speed is faster, but the disadvantage is that there will be mismatches after the database is changed.

The two types of paging have their own advantages and disadvantages. Please use them according to the specific situation.

The following is the real paging method:

1. Create JavaBean

import java.io.Serializable;
/**
 * User entity class * @author 
 *
 */
public class UserBean implements Serializable {
  /**User ID*/
  private int id;
  /**Username*/
  private String name;
  public UserBean() {
  }
  public UserBean(int id, String name) {
    this.id = id;
    this.name = name;
  }
  public int getId() {
    return id;
  }
  public void setId(int id) {
    this.id = id;
  }
  public String getName() {
    return name;
  }
  public void setName(String name) {
    this.name = name;
  }
  @Override
  public String toString() {
    return "UserBean [id=" + id + ", name=" + name + "]";
  }
}

2. JavaBean for displaying paging data

/**
 * JavaBean object used to display paging data * @author
 *
 */
import java.util.List;
public class PagenationBean {
  /** Current page number*/
  private Integer currPage;
  /**Total number of pages*/
  private Integer totalPage;
  /** Table data for display */
  private List<UserBean> dataList;
  public Integer getCurrPage() {
    return currPage;
  }
  public void setCurrPage(Integer currPage) {
    this.currPage = currPage;
  }
  public Integer getTotalPage() {
    return totalPage;
  }
  public void setTotalPage(Integer totalPage) {
    this.totalPage = totalPage;
  }
  public List<StuBean> getDataList() {
    return dataList;
  }
  public void setDataList(List<StuBean> dataList) {
    this.dataList = dataList;
  }
}

3. Dao layer implementation class

 @Override
  public int getTotalCount() { //Calculate the total number of data this.setConnection();
    int totalCount = 0;
    try {
      ps = con.prepareStatement("select count(*) from t_user");
      rs = ps.executeQuery();
      if (rs.next()) {
        totalCount = rs.getInt(1);
      }
    } catch (Exception e) {
      e.printStackTrace();
    finally
      this.closeConnection();
    }
    return totalCount;
  }
  @Override
  public List<UserBean> getUserListByStartIndex(int StartIndex) { //According to the first parameter of limit passed in, get the 10 data behind this parameter List<UserBean> userList = new ArrayList<>();
    UserBean userBean= null;
    this.setConnection();
    int totalCount = 0;
    try {
      ps = con.prepareStatement("select * from t_user limit ? , 10");
      ps.setInt(1, StartIndex);
      rs = ps.executeQuery();
      while (rs.next()) {
        userBean = new StuBean();
        userBean.setId(rs.getInt("id"));
        userBean.setName(rs.getString("name"));
        stuList.add(userBean);
      }
    } catch (Exception e) {
      e.printStackTrace();
    finally
      this.closeConnection();
    }    
    return userList;
  }

4. Service layer implementation class

private IUserDao isd = new UserDaoImpl();
  @Override
  public int getTotalPage() {
    //Get the number of data int totalCount = isd.getTotalCount();
    //Calculate the total number of pages: int totalPage = (totalCount + 10 -1)/10;
    return totalPage;
  }
  @Override
  public List<UserBean> getUserListByCurrPage(int currPage) {
    //Calculate the starting index through the current page int StartIndex = (currPage - 1) * 10;
    List<UserBean> userList = isd.getStuListByStartIndex(StartIndex);
    return userList;
  }

5. Put the queried data into the page for display.

In the above method, 10 pieces of data are displayed in pages, and the calculation and analysis are as follows:

Total number of data items: totalCount

Number of entries per page: pageSize

Total number of pages: totalPage

StartIndex

Current page number currPage

Total pages calculation formula:

totalCount % pageSize

If the remainder is 0 ——> totalPage = totalCount / pageSize

If the remainder is not 0 ——> totalPage = totalCount / pageSize + 1

Conclusion: totalPage = (totalCount + pageSize -1)/pageSize

Summarize

The above is the MySql page query function introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in:

Tips for optimizing the page flipping of hot posts in Discuz! through MySQL
JAVA/JSP Learning Series 8 (Rewriting the MySQL page turning example)
JAVA/JSP Learning Series 6 (MySQL Page Turning Example)
Will the index be used in the MySQL query condition?
Detailed explanation of the code for querying data of a certain day, month, or year in MySQL
A simple example of MySQL joint table query
How to solve the problem of case insensitivity in MySQL queries
An example of how to query data in MySQL and update it to another table based on conditions

<<: How to purchase and initially build a server

>>: Detailed explanation of JavaScript clipboard usage