How to start paging query (JavaWeb)

Keywords: Programming Javascript JQuery Database JSON

Technology coverage (JavaWeb, HTML, Ajax, JQuery, Bootstrap)

When contacting this part of knowledge, we often do some small demos to practice. Inevitably, we need to touch a certain amount of data. We often need to echo the data from the database to the page. However, with the increase of data volume, if we do not deal with the query or display of data to some extent, there will be various problems, such as:

  • Client: if the data is displayed on one page at the same time, the user experience is poor and the operation is extremely inconvenient
  • Server end: once a request is made, all data can be queried. The data transmission volume is too large, which leads to timeout or slow response speed. The server is overloaded

Paging mode

Front end JS paging - not recommended

We can request to get all the data, and then use JavaScript to display the data page by page. In terms of data display, it's really beautiful, and this way of page is much simpler than back-end page

However, if there is a certain amount of data, this method is really awkward. Instead of solving any problems of our server, it will make users spend too much time waiting for response data and experience poorly. However, it is still a paging method

Here we will focus on the back-end paging, so we will simply demonstrate and paste the code. Because we use the BootStrap front-end framework in html, we use the BootStrap table front-end paging plug-in

Front end JS paging demonstration code:

![11.1-01-003](G:\official account\markdown file\11-Pagination and condition query\Paging query\11.1-01-003.png)<!DOCTYPE html>
<!-- Language of web page -->
<html lang="zh-CN">
  <!-- Specify character set -->
  <meta charset="utf-8">
  <!-- Use Edge Rendering method of the latest browser -->
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <!-- viewport Viewport: Web pages can be automatically adapted according to the set width. A container is virtual inside the browser. The width of the container is the same as that of the device.
  width: The default width is the same as the width of the device
  initial-scale: Initial zoom ratio, 1:1 -->
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <!-- The 3 above meta Label*Must*Put it first, anything else*Must*Follow! -->
  <title>User information management system</title>

  <!-- Import CSS Global style for -->
  <link href="css/bootstrap.min.css" rel="stylesheet">
  <!--Import table plug in style sheet-->
  <link href="css/bootstrap-table.min.css" rel="stylesheet">

<div class="container">
  <h3 style="text-align: center">User information list</h3>

  <!--Save toolbar-->
  <div id="toolbar"></div>
  <!--Store generated forms-->
  <table id="userInfo_table" class="table table-hover">

<!-- jQuery Import, 1 is recommended.9 Version above -->
<script src="js/jquery-2.1.0.min.js"></script>
<!-- Import bootstrap Of js file -->
<script src="js/bootstrap.min.js"></script>
<!--Import table plug in-->
<script src="js/bootstrap-table.min.js"></script>
<style type="text/css">
  td, th {
    text-align: center;

    $(function () {
            url: 'user/userList',
            toolbar: '#toolbar',
            method: 'GET',
            striped: true,                        //Display color separation between lines
            cache: false,                         //Use cache or not
            toolbarAlign: "right",                //Toolbar alignment
            sidePagination: "client",             //Paging mode: client paging, server paging
            search: true,                         //Whether to display table search? This search is a client search and will not enter the server
            uniqueId: "id",
            pageNumber: 1,                        //Initialize load first page
            pageSize: 10,                         //Record lines per page
            pageList: [5, 10, 15, 20],            //Number of rows per page to choose from
            pagination: true,                     // Paging
            sortable: true,                       // Enable sorting or not
            sortOrder: "asc",                     //sort order
            showColumns: true,                    //Show column selection button or not
            showRefresh: true,                    //Show refresh button or not
            clickToSelect: true,                  //Enable Click to select row
            // height: 500, / / line height
            showToggle: true,                     //Show toggle buttons for detail view and list view
            cardView: false,                      //Show detailed view or not
            detailView: false,                    //Show parent-child table or not
            queryParamsType: '',//Format request parameters
            queryParams: function queryParams(params) { //Set custom query parameters
                /*When requesting remote data, you can send other parameters by modifying queryParams.
                If queryparamstype ='limit ', params object contains: limit, offset, search, sort, order.
                Otherwise, it contains: pageSize, pageNumber, searchText, sortName, sortOrder.
                Return false to stop the request.
                Default: function (params) {return params}*/
                return params;

            columns: [{
                title: "All election",
                field: "select",
                checkbox: true,
                width: 20, //width
                align: "center", //level
                valign: "middle" //vertical
            }, {
                field: 'uid',
                title: 'number'
            }, {
                field: 'username',
                title: 'User name'
            }, {
                field: 'nickname',
                title: 'Nickname?'
            }, {
                field: 'email',
                title: 'mailbox'
            }, {
                field: 'telephone',
                title: 'Telephone'
            }, {
                field: 'gender',
                title: 'Gender'
            }, {
                field: 'birthday',
                title: 'Birthday'
                field: 'id',
                title: 'operation',
                // width: 120,
                align: 'center',
                valign: 'middle',
                formatter: actionFormatter
    //Formatting the action bar
    function actionFormatter(value, row, index) {
        var id =;
        var result = "";
        result += "<button style='cursor: pointer;margin-right: 5px' class='btn btn-primary' title='modify' onclick=''>modify</button>";
        result += "<button style='cursor: pointer' class='btn btn-primary' title='delete' onclick=''>delete</button>";
        return result;


(2) Back end paging - Recommended

The biggest difference between back-end paging and front-end paging is that it does not need to request a large amount of data from the back-end at one time, but according to the user's settings, a certain amount of data is requested at one time, and then these data are echoed to the page. Back-end paging is also the correct way to open paging, which avoids obtaining a lot of data from the database at one time and beautifies the front-end display effect , optimize the user experience

Implementation of back end paging

(1) Overall analysis

According to what we said above, what we need is for the front end to submit requests to the back end, and the back end to respond to the data required by the front end, and display it in the front end page

In the front page, we naturally need a paging bar

We will roughly modify it as needed, adding a front page and a last page, as well as a page number and statistical text of data records

The basic problems involved in our data are the above figure and the echo of response data in the table

① Naturally, we need to traverse and echo the list collection containing user information sent from the back end

  • That is, you need to receive and process a List collection

② The total number of records, and the value will be given after the background query in the database

  • That is, an int totalCount variable is required (the variable name is determined by itself)

③ The total page number can be calculated according to the total number of records and the number of pieces displayed on each page (to be more specific later)

  • An int totalPage variable is required

④ The current page number, according to the current page number, can let the background know what data you need

  • An int currentPage variable is required

⑤ The number of items displayed on each page can be temporarily written as fixed. When improving, it can be handed over to the client for selection and provided to the back end

  • An int pageSize variable is required

⑥ The starting position of each query is limited by the LIMIT statement, which can be obtained by combining the number of items displayed on each page

  • An int start variable is required

(2) Back end implementation

(1) Paging object

Because the front-end needs to receive the data information sent from the background, we can simply analyze what we need for the above, and assemble it into a paging object to facilitate our data transmission

//Add generics for later calls
public class PageBean<T> {
    private int totalCount;//Total number of records
    private int totalPage;//PageCount
    private int currentPage;//Current page number
    private int pageSize;//Number of entries per page
    private List<T> list;//Data set displayed per page

    //Omit corresponding construction, get set method


(2) Servlet code

  • First, you need to get the values of two String types: currentPage and pageSize from the front end
  • If the front-end does not pass, the values of these two variables are set by default. If the passed values are reasonable, the type of these two variables will be changed to int (this can be ignored in the earlier stage, or the verification can be set in the front-end)
  • Call service to query PageBean paging object and receive its return value
  • Serialize the PageBean object to json format, and return
public class RouteServlet extends BaseServlet {

     * Paging query method
     * @param request
     * @param response
     * @throws ServletException
     * @throws IOException
    public void routeQuery(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String currentPageStr = request.getParameter("currentPage"); //Current page number
        String pageSizeStr = request.getParameter("pageSize"); // Number of entries per page

        //The current page number, if not transferred, defaults to page 1
        int currentPage = 0;
        if (currentPageStr != null && currentPageStr.length() > 0) {
            currentPage = Integer.parseInt(currentPageStr);
        } else {
            currentPage = 1;

        //The number of records per page is displayed. If it is not transferred, 8 records will be displayed by default
        int pageSize = 0;
        if (pageSizeStr != null && pageSizeStr.length() > 0) {
            pageSize = Integer.parseInt(pageSizeStr);
        } else {
            pageSize = 8;

        //Get condition query parameters
        Map<String, String[]> condition = request.getParameterMap();

        //Call service to query PageBean object
        RouteService service = new RoutrServiceImpl();
        PageBean<User> userPageBean = service.pageQuery(currentPage, pageSize);

        //Serialize the PageBean object to json, and return
        ObjectMapper mapper = new ObjectMapper();
        mapper.writeValue(response.getOutputStream(), userPageBean);


Note: in the above code, I have extracted the Servlet, which is convenient for future expansion methods. It is also possible for a new friend to directly create a common Servlet and write it directly in it. Familiar friends, please ignore my sentence

We need to import the relevant jar package of jackson spring mysql druid

(3) Service code

The two values of currentPage and pageSize have been determined. We also need to determine:

Total number of records totalCount and total number of pages totalPage and the List set that needs to be echoed to the front page

  • The total number of records can be queried directly through dao layer

  • The total number of pages can be determined by (total records / number of items displayed on each page). Please note that one more page is needed if division is not allowed

  • Query the data list that needs to be displayed on the front-end page. We need to use LIMIT in SQL query to LIMIT it, so we need to provide the starting point of query and how many pieces to query each time, so that we can accurately find out which data should be displayed on this page. For example, we can get where to start each page

    That is: int start = (currentPage - 1) * pageSize

public class RouteServiceImpl implements RouteService {

    private RouteDao routeDao = new RouteDaoImpl();

     * Paging query
     * @param currentPage
     * @param pageSize
     * @param condition
     * @return
    public PageBean<User> pageQuery(int currentPage, int pageSize, Map<String, String[]> condition) {

        //Create pageBean object
        PageBean<User> pageBean = new PageBean<User>();
        //Setting parameters
        //Total records of calling dao query
        int totalCount = routeDao.findTotalCount(condition);
        //Call dao to query List collection
        int start = (currentPage - 1) * pageSize;
        List<User> list = routeDao.findByPage(start, pageSize, condition);

        //Calculate total page number
        int totalPage = (totalCount % pageSize) == 0 ? totalCount / pageSize : (totalCount / pageSize + 1);

        return pageBean;

(4) Dao code

  * Query the data set of the current page according to start pageSize
  * @param start
  * @param pageSize
  * @return
public List<User> findByPage(int start, int pageSize) {

    String sql = "SELECT * FROM user_info LIMIT ? , ?";
    return template.query(sql, 
     new BeanPropertyRowMapper<User>(User.class), start, pageSize);

(3) Front end implementation

Document loading completed

$(function () {
    //Temporarily passing two fixed values
    var currentPage = 1;
    var pageSize = 8;

    //Call specific function methods here

function load(currentPage, pageSize){
	//The specific echo code is explained in detail below

Note: the following code is written in the load method

(1) ajax asynchronous submission

$.get("route/routeQuery", {currentPage:currentPage,pageSize:pageSize}, function (data){
    //Pass currentPage and pageSize to the backend, and the callback function returns a data
    //Here is the specific code

Let's follow this process sequence to explain

(2) Statistics of data records and total page numbers

In this step, as long as the background code is written, there will be no big problem

 $("pageCount").html("total" + data.totalCount + "records, total" + data.totalPage + "page");

(3) User information echo

In HTML, we use code splicing to achieve this requirement. At this time, the data of a User in the returned list set is traversed and displayed in our table

('#userInfo_table tr:gt(0)').remove();
	var s = '';
	for (var i = 0; i < data.list.length; i++) {
		s += '<tr><td>' + '<input type="checkbox" name="checkItem"/>' + 			'</td><td>' + data.list[i].uid + '</td><td>' + 							data.list[i].username + '</td><td>' + data.list[i].nickname + 			  '</td><td>' + data.list[i].email + '</td><td>' + 							data.list[i].telephone + '</td><td>'+ data.list[i].gender + 			'</td><td>' + data.list[i].birthday + '</td><td>'+ 
            '<button type="submit" class="btn btn-primary" id="updateBtn">				modify</button>' + '&nbsp' + 
            '<button type="submit" class="btn btn-primary" id="deleteBtn" 				">delete</button>' + '</td>';

In this way, our data echo is reflected. On the first page, there are exactly 8 data (Ps: some data was deleted in the previous test, so the number is not 0)

(4) Implementation of first and last page and page turning

var lis = " ";

//Click home code
var firstPage = '<li><a href="javascript:load(1,8)">home page</a></li>';

//Calculate the page number of the previous page
var previousNum = data.currentPage - 1;
if(previousNum <= 0){
    previousNum = 1;

//Specific code of the previous page
var previousPage = '<li class="threeword"><a 					href="javascript:load('+previousNum+',8)">&laquo;</a></li>';

lis += firstPage;
lis += previousPage;The last page and the next page are almost the same as the first page and the previous page

(5) Treatment of page numbers

How to deal with the page number is a little more complicated than the previous points. We need users to click to display the correct user information. Secondly, we need to consider how to ensure that only a few pages around the page number we need are displayed. There can't be as many pages as there are

We still need to regulate, like this:

	8 pages in total, the first 4 and the last 3
    If there are not enough 4 in the front, 8 in the back
    If less than 8 in the back, 8 in the front

var start;
var end;

//More than 8 pages in total
if (data.totalPage < 8) {
	start = 1;
    end = data.totalPage;
	//More than 8 pages in total
    start = data.currentPage - 4;
    end = data.currentPage + 3;

    //If there are not enough four in front
    if (start < 1) {
    	start  = 1;
        end = start + 7;

	//If less than 3 in the back, 8 in the front
    if (end > data.totalPage ) {
		end = data.totalPage;
		start = end - 7;

for (var i = start; i <= end; i++) {
	if (data.currentPage == i){
		var li = '<li class="active"><a 											href="javascript:load('+i+',8)">'+ i + '</a></li>';
        var li = '<li><a href="javascript:load('+i+',8)">' + i + '</a>				  </li>';
    lis += li;

Note: you can use onclick to optimize the execution of this form

Effect display

home page



This article is almost finished here. This style is a layout of a style in a horse that I refer to. HTML + Ajax is used instead of JSP, and the back-end code is rewritten accordingly. However, it is the simplest paging, which is more suitable for Java Web stage The friends who have just contacted pagination know more or less, and hope to give you some help. Don't spray if you don't like it

In the same way, here I wish you a happy new year, and I hope you can all be healthy and safe!


If there are any shortcomings or mistakes in the article, please leave a message and share your ideas. Thank you for your support!

If you can help, then pay attention to me! If you prefer the way of reading WeChat articles, you can pay attention to my public number.

We don't know each other here, but we are working hard for our dreams Mei

A public figure that persists in pushing original development technology articles: ideal two days

Posted by Sonic_Rage on Sun, 26 Jan 2020 04:19:06 -0800