springboot,mybatis,pageHelper and jQuery DataTable

1. Introduction

In previous post I have demostrated how to query the database by page by using springboot, mybatis and pageHelper.

Today I would continue to use springboot myBatis and pageHelper, but I would create a dynamic html page by using JQuery DataTable, and I would demo how to use a spring RestController to support the jQuery DataTable pagination.

The result:

It’s recommended to read the previous post at first. And then you can do the additional task in this post.

2. Environments

• SpringBoot 2.0.2+
• PageHelper springboot starter 1.2.5
• MyBatis springboot starter 1.3.2
• DataTable 1.10.16
• jQuery 3.3.1
• Java 1.8

3. The Pom.xml

Add these dependencies to the pom.xml:

<!--springboot web starter and tomcat embeded-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.apache.tomcat.embed</groupId>
    <artifactId>tomcat-embed-jasper</artifactId>
    <scope>provided</scope>
</dependency>
<!--web jars-->
<dependency>
    <groupId>org.webjars</groupId>
    <artifactId>jquery</artifactId>
    <version>3.3.1</version>
</dependency>

4. The initial data

There are 11 records in the tbl_student table:

INSERT INTO `tbl_student` (`ID`, `NAME`, `BRANCH`, `PERCENTAGE`, `PHONE`, `EMAIL`)
VALUES
    (1, 'jack', 'it', 20, 1211232, '[email protected]'),
    (2, 'mark', 'dev', 30, 333, '[email protected]'),
    (3, 'tony', 'dev', 2, 444, '[email protected]'),
    (4, 'nancy', 'dev', 23, 788, '[email protected]'),
    (5, 'tommy', 'it', 32, 2423, '[email protected]'),
    (6, 'tom', 'it', 23, 11, '[email protected]'),
    (7, 'Aka', 'dev', 1, 2, '[email protected]'),
    (8, 'al', 'dev', 2, 3, '[email protected]'),
    (9, 'ben', 'dev', 3, 4, '[email protected]'),
    (10, 'Mike', 'it', 5, 6, '[email protected]'),
    (11, 'Cow', 'it', 3, 23, '[email protected]');

• We init the table with 11 records to get 2 pages in the DataTable.

7. The domain class

This time we add a DataTableResultInfo to be used by DataTable.

@Data
public class DataTableResultInfo {
    private Page<Student> data;
    private int draw;//the NO.of requests
    private int length;
    private long recordsTotal;
    private long recordsFiltered;
}

• Here we use the lombok @Data annotation to generate getter/setter and constructors for the Student class.
• This object would be initialized in the RestController and returned as the html ajax result

8. The app codes

8.1 The app project layout

• As you can see, we created a folder named static in the src/main/resources to store the html files

8.2 The Controller class

We create a StudentRestController to produce data for the jQuery DataTable.

@RestController
@RequestMapping("/studentsApi")
public class StudentRestController {
    @Autowired
    private StudentService studentService;


    @RequestMapping(value = "/queryByPage", method = RequestMethod.GET)
    public DataTableResultInfo queryByPage(
            @RequestParam(value = "start", required = false, defaultValue="0") Integer start,
            @RequestParam(value = "length", required = false, defaultValue="10") Integer length,
            @RequestParam(value = "draw",required = false,defaultValue = "0") Integer draw) {

        int pageNo = (start)/length+1;
        Page<Student> pageInfo = studentService.findByPage(pageNo,length);

        DataTableResultInfo dataTableResultInfo = new DataTableResultInfo();
        dataTableResultInfo.setData(pageInfo);
        dataTableResultInfo.setDraw(draw);
        dataTableResultInfo.setLength(length);
        dataTableResultInfo.setRecordsTotal(pageInfo.getTotal());
        dataTableResultInfo.setRecordsFiltered(pageInfo.getTotal());

        return dataTableResultInfo;
    }
}

• We retrieve the start/length/draw params from the request
• We call the studentService to get the page result
• We adapt the result to DataTableResultInfo object and return it

8.3 The index.html file

In the html file, we would use the DataTable to display our data.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>springboot mybatis pageHelper and DataTable</title>
    <script src="/webjars/jquery/3.3.1/jquery.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.js"></script>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.css">

    <meta http-equiv="cache-control" content="no-cache" />
</head>
<body>
myTable is here:

<table id="myTable" class="display">
    <thead>
    <tr>
        <th>id</th>
        <th>Name</th>
        <th>Branch</th>
        <th>Percentage</th>
        <th>Phone</th>
        <th>Email</th>
    </tr>
    </thead>
    <tbody>

    </tbody>
</table>

<script>
    $(document).ready(function() {
        $('#myTable').DataTable({
            "serverSide": true,
            lengthChange : false,
            ordering : false,
            paging : true,
            pagingType : "full_numbers",
            processing : true,
            autoWidth : true,
            ajax: {
                url: '/studentsApi/queryByPage',
                dataSrc: 'data'
            },

            columns: [
                { data: 'id' },
                { data: 'name' },
                { data: 'branch' },
                { data: 'percentage' },
                { data: 'phone' },
                { data: 'email' }
            ]
        });
    });
</script>
</body>
</html>

• In the html header, we import the jquery and DataTable
• In the body, we create a table with some headers
• In the javascript, we init the table and call the ajax to get the data from the spring restful service

This is the result:

It’s so easy, do you think so?

The example source code has been uploaded to github, you can visit here to view the example source codes.

• jQuery DataTable