springboot,mybatis and pageHelper example
1. Introduction
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
2. Environments
- 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:
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>2.0.2.RELEASE</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
all dependencies:
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<!--pagehelper -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
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:
CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_bin;
There is a table ‘tbl_student’ in both of the databases:
CREATE TABLE `tbl_student` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`NAME` varchar(100) NOT NULL,
`BRANCH` varchar(255) NOT NULL,
`PERCENTAGE` int(3) NOT NULL,
`PHONE` int(10) NOT NULL,
`EMAIL` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
And insert five records into that 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]');
7. The domain class Student
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private String branch;
private int percentage;
private int phone;
private String email;
}
- 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:
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8
spring.datasource.username = root
spring.datasource.password = 123456
#mybatis entity scan packages
mybatis.type-aliases-package=com.bswen.sbmp.domain
#Mapper.xml location
mybatis.mapper-locations=classpath*:/mybatis/*Mapper.xml
logging.level.com.bswen.sbmp=debug
# debug for PageHelper library
logging.level.com.github.pagehelper=debug
logging.level.org.springframework.web=debug
logging.level.org.springframework.transaction=debug
logging.level.org.mybatis=debug
#pagehelper plugin configuration
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
7.2 The mapper xml
The StudentMapper.xml content:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.bswen.sbmp.dao.StudentMapper" >
<!--<cache />-->
<resultMap id="baseResultMap" type="com.bswen.sbmp.domain.Student" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="phone" property="phone" jdbcType="VARCHAR" />
<result column="email" property="email" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, name, phone, email
</sql>
<select id="findByPage" resultMap="baseResultMap">
select
<include refid="Base_Column_List" />
from tbl_student
</select>
</mapper>
- 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:
@Mapper
public interface StudentMapper {
Page<Student> findByPage();
}
- Note that the findByPage has no params, the pagination query param is set by another way
7.4 The service class
The service.StudentService:
@Service
@Transactional(readOnly = true)
public class StudentService {
@Autowired
private StudentMapper studentMapper;
public Page<Student> findByPage(int pageNo, int pageSize) {
PageHelper.startPage(pageNo, pageSize); //line 1
return studentMapper.findByPage(); //line 2
}
}
- 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:
@RunWith(SpringRunner.class)
@CommonsLog
@SpringBootTest
public class StudentServiceTest {
@Autowired
private StudentService studentService;
@Test
public void testFindByPage() {
Page<Student> students = studentService.findByPage(1, 2);//Query pageNo=1, pageSize=2
assertEquals(students.getTotal(),5);
assertEquals(students.getPages(),3);
log.debug(students.toString());
}
}
- 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:
Page{count=true, pageNum=1, pageSize=2, startRow=0, endRow=2, total=5, pages=3, reasonable=true, pageSizeZero=false}[Student(id=1, name=jack, branch=null, percentage=0, phone=1211232, [email protected]), Student(id=2, name=mark, branch=null, percentage=0, phone=333, [email protected])]
8. The com.github.pagehelper.Page class
After we call studentService.findByPage , we got a **Page
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.