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

sbmp_arch1

  • 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

sbmp_arch2

  • 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

sbmp_arch4

  • 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. junit_greenbar

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** object, the structure of this class is as follows:

sbmp_arch3

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.