This post would demo how to query the database by page by using springboot, mybatis and pageHelper.
If you don’t know how to setup springboot and mybatis, refer to this post
- SpringBoot 2.0.2+
- PageHelper springboot starter 1.2.5
- MyBatis springboot starter 1.3.2
- Java 1.8
3. The Pom.xml
spring boot version:
4. The dependencies explanation
4.1 The pagehelper
The pagehelper is a opensource project aims to help you do pagination with springboot and mybatis easily. It’s a mybatis plugin.
You can refer to this article to know how to use the pagehelper.
4.2 The lombok
The lombok is an IDE plugin to help you do some boliertemplate jobs. In this post we use the lombok to generate the getter/setter of the domain object.
You can refer to this post to learn how to use lombok.
5. The architecture of this app
- The pagehelper is a mybatis plugin
- The mybatis has a springboot starter
6. The database , table and initial data
For demo purpose, I setup one database in localhost as follows:
There is a table ‘tbl_student’ in both of the databases:
And insert five records into that table:
7. The domain class Student
- Here we use the lombok @Data annotation to generate getter/setter and constructors for the Student class.
7. The app codes
7.1 The app code layers
- the service layer: StudentService , support find students by page
- the dao layer: It’s just a mybatis mapper interface
- the xml layer: It’s a mybatis xml file that defines SQL
7.2 The app project layout
- As you can see, we created a folder named mybatis in the src/main/resources
7.3 the application.properties
Supply these properties in your application.properties:
7.2 The mapper xml
The StudentMapper.xml content:
- we define a resultMap and a columnList to be reused by sqls
- we define a sql named findByPage,which matches the mapper interface
- Note : we don’t write sql with limit for pagination, the pagination would be done by the pageHelper plugin
7.3 The mapper interface
The dao.StudentMapper is as follows:
- Note that the findByPage has no params, the pagination query param is set by another way
7.4 The service class
- line 1: We use PageHelper.startPage to set the page number and the pageSize
- line 2: We call the mapper to do the pagination magic
7.5 The testcase
The StudentServiceTest class:
- Because there are 5 records in the table , so if we query the first page and set the pageSize to 2, then the total count should be 5 and the total pages should be 3
- We use the @CommonsLog of lombok to generate log object for the testcase
Run the testcase, we got a green bar.
And the console output of the debug string:
8. The com.github.pagehelper.Page class
After we call studentService.findByPage , we got a **Page** object, the structure of this class is as follows:
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.