This paper records the implementation of simple paging function.

Relevant environment:
Spring boot framework;
Template engine: thymeleaf;
Front end framework: layui;
Framework of persistence layer: mybatis;
Paging plug-in: pagehelper;

Write some nonsense.

(please skip it if you are eager to realize the function)

Paging function should be a relatively difficult function for novices (please ignore the big guy). First of all, it should be clear that paging function is a front-end and back-end function that needs to understand the principle and code writing.

At first, I thought that so many paging plug-ins, just copy them, or just write back-end and lead a plug-in to the front-end. If you have the same simple idea as me, please come in and give up your fantasy, find a paging tutorial that is consistent with your own development framework, and write the front-end and back-end code.

Also! Using any framework that is inconsistent with the record at the beginning of this article may conflict with the record process of this article, and it is not allowed to directly refer to the code recorded in this article; of course, there is no problem in finding ideas. (or I won't climb on csdn for three days...)

Page display

1. To introduce maven dependency of paging plug-in, add the following code in pom.xml file:

		<!--PageHelper Plug-in unit-->
		<dependency>
			<groupId>com.github.pagehelper</groupId>
			<artifactId>pagehelper</artifactId>
			<version>4.1.1</version>
		</dependency>
		<dependency>
			<groupId>com.github.jsqlparser</groupId>
			<artifactId>jsqlparser</artifactId>
			<version>1.4</version>
		</dependency>

2. Introducing js and css files of the front end

<!-- layui for Page -->
<link href="layui-v2.5.6/layui/css/layui.css" th:href="@{/layui-v2.5.6/layui/css/layui.css}" rel="stylesheet">

<!-- layui Plugin introduction -->
<script type="text/javascript" src="layui-v2.5.6/layui/layui.js" th:src="@{/layui-v2.5.6/layui/layui.js}"></script>

3. Front end html and js writing

Table table:

 <table id="Usertable" class="table table-striped table-sm" lay-filter="Usertable"></table>

Script js:

Note to add < script type = "text / JavaScript" th: inline = "None" >, otherwise there will be problems in the header.

Here we use the front-end paging plug-in of layui, official website: table module paging function of layui

<script type="text/javascript" th:inline="none">
    $(function() {$ = layui.jquery;
    layui.use(['form', 'laypage', 'layer', 'table', 'element'], function(){
        var form = layui.form
            ,laypage = layui.laypage //paging
            ,layer = layui.layer //Elastic layer
            ,table = layui.table //form
            ,element = layui.element //Element operation
        table.render({
            elem: '#Usertable'
            ,height: 312
            ,url: '/user/findArticle' //data interface
            ,method: 'post' //Default: get request
			,limit: 5
			,limits: [5,10,15]
            ,page: true//Open paging
            ,cellMinWidth: 10 //Define the minimum width of general cells globally, new in layeui 2.2.1
            ,request: {
                pageName: 'page' //Parameter name of page number, default: page
                ,limitName: 'limit' //Parameter name of data quantity per page, default: limit
            },response:{
                statusName: 'code' //Field name of data status, default: code
                ,countName: 'count' //Field name of total data, default: count
                ,dataName: 'data' //Field name of data list, default: Data
            }
            ,cols: [[ //Header
                {type: 'checkbox', fixed: 'left'}
                ,{field: 'id', title: '#',width:50}
                ,{field: 'name', title: 'Name',width:80}
                ,{field: 'syncTime', title: 'SyncTime',templet:'<div>{{ layui.util.toDateString(d.syncTime, "yyyy-MM-dd HH:mm:ss") }}</div>',width:180}
                ,{field: 'code', title: 'Code'}
                ,{field: 'account', title: 'Account',width:130}
                ,{field: 'sex', title: 'Sex',width:60}
                ,{field: 'idCard', title: 'idCard',width:150}
                ,{title: 'operation',width:178, align:'center', toolbar: '#barDemo',width:130}
            ]],
            done:function(res, curr, count) {
                $("[data-field = 'sex']").children().each(function () {
                    if ($(this).text() == '0') {
                        $(this).text("female");
                    } else if ($(this).text() == '1') {
                        $(this).text("male");
                    }
                });
            }
        });
      });
    });
</script>
<script type="text/html" id="barDemo">
	<!--<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail">See</a>-->
	<a class="layui-btn layui-btn-xs" lay-event="edit">edit</a>
	<a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">delete</a>
</script>

4. Encapsulation class

I saw this class in other tutorials, so the name is very strange ha ha

The initial value of return status code should be "0", otherwise the front end will report an error and return status error.

public class ResultVo {

    private String code="0";
    private String msg;
    private long count=0;
    private Object data;

    // set and get methods are omitted
}

5,Controller

Using the PageHelper plug-in, get two parameters from the front end:

Page: the current page number to display

limit: the specified number of data pieces per page

@Controller
@RequestMapping("/user")
public class StaffControllerPage {

    @Autowired
    StaffService staffService;

    @Autowired
    StaffMapper staffMapper;

    /**
     *
     * Query employee list by page
     * @return ok/fail
     */
    @RequestMapping(value = "/findArticle", method = RequestMethod.POST)
    @ResponseBody
    public ResultVo findArticle(@RequestParam(required = false,defaultValue = "1") int page,
                                @RequestParam(required = false,defaultValue = "10") int limit){

        //Instantiate encapsulation class
        ResultVo rv=new ResultVo();
        try {
            //Instance paging plug-in
            PageInfo<B01BaseInfo_Staffs> pdr = new PageInfo<B01BaseInfo_Staffs>();
            pdr = staffService.findArticle(page, limit);

            // ---Second independent query of data volume
            int totalNum = staffService.getTotalNum();
            rv.setCount(totalNum);

            rv.setData(pdr.getList());//Store data to encapsulation class
            System.out.println("===== Information of paging query:"+ rv+"/n");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rv;
    }
}

6,Service

The related logic in service will be explained later. There are two methods: total query and paging query.

@Service
public class StaffService {

    @Autowired
    StaffMapper staffMapper;

    public int getTotalNum()throws SQLException{
        return staffMapper.getTotal();
    }

    public PageInfo<B01BaseInfo_Staffs> findArticle(int page, int limit) throws SQLException{
        PageHelper.startPage(page, limit);
        //PageHelper. Orderby ("article" ID ASC "); / / sorting settings
        System.out.println("page = "+page+" limit = "+limit);
        int start = (int)(page-1)*limit+1;
        int end = (int)page*limit;
        System.out.println("Start number = "+start+" Ending number = "+end);
        int fir = end-start+1; // First sql parameter
        int sec = start-1; // Second parameter
        System.out.println("first = "+fir+" second = "+sec);
        List<B01BaseInfo_Staffs> ArticleInfo = staffMapper.findArticle(fir,sec);
        PageInfo<B01BaseInfo_Staffs> pageinfo = new PageInfo<B01BaseInfo_Staffs>(ArticleInfo);
        return pageinfo;
    }
}

7,mapper

@Service // Added by itself to prevent controller.LoginByAcc from reporting errors
// @Mapper or @ MapperScan assemble interface scan into the container
public interface StaffMapper {

    // Total query
    int getTotal() throws SQLException;

    // Paging query
    List<B01BaseInfo_Staffs> findArticle(@Param("fir") int fir,@Param("sec") int sec)throws SQLException;
}

8,mybatis-xml

<select id="getTotal"  resultType="int">
    select count(id) from BaseInfo_Staffs
</select>

<select id="findArticle" resultType="B01BaseInfo_Staffs">
    select top ${fir} <include refid="columns" />
    from BaseInfo_Staffs
    where id not in (
    select top ${sec} id from BaseInfo_Staffs
    )
</select>

Some explanations:

Seeing the methods and sql statements in service, some students who are used to MySQL may not understand them very well, and I am also very sad...

As we know, the principle of paging is to pass two parameters page and limit from the front end, which respectively represent the page number (that is, the page number) to be displayed on the front end and the amount of data each page can hold.

The back-end task is to make the sql statement only find out the required part of data according to page and limit, which is the real paging principle.

Of course, there is also "pseudo paging", which is to find out all the data and send it to the front end, and select the data to be displayed in the front end for rendering. But this kind of "pseudo paging" just looks like paging. Imagine that in a database with millions of data, the front end only needs 10 pieces of data, but the program selects all of them, which is not reasonable and has lost the significance of paging.

In MySQL database, we can directly use LIMIT ${m}, ${n}, and select n data after M data. But!!!! Everyone, I forgot that SQL server does not support limit statement. I spent the whole afternoon in this pit... Cried

So how does SQL Server implement the same functions as limit?

Throw a link out! - How to realize the Limit m, n function of MySQL in SQL Server

First look at this sql:

select top n id from table 
where id not in (  
  select top m id from table  
)  

what do you mean?

Select the id of the first n rows in the table table whose id is not in the first m rows of the table table. It's a bit convoluted. You can see the picture below.

The logic in service is how to get the values of m and n according to the front-end parameters page and limit.

The corresponding code is this part:

 int start = (int)(page-1)*limit+1;
 int end = (int)page*limit;
 System.out.println("Start number = "+start+" Ending number = "+end);  //Output test
 int fir = end-start+1; // First sql parameter
 int sec = start-1; // Second parameter

In the front-end page, the values of page and limit can be changed to affect the query results and realize the real paging display.

It's over.