Skip to content
BSWEN
Blog

MyBatis and PageHelper example

Cowrie
Cowrie
Dev @ Bswen

1. Introduction

This post demonstrates how to query a database with pagination using Spring Boot, MyBatis, and the PageHelper plugin.

2. Environments

  • Spring Boot 2.0.2+
  • PageHelper Spring Boot Starter 1.2.5
  • MyBatis Spring Boot Starter 1.3.2
  • Java 1.8

3. The Pom.xml

Spring Boot version:

pom.xml
<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:

pom.xml
<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

PageHelper is an open-source project designed to simplify pagination in Spring Boot and MyBatis applications. It functions as a MyBatis plugin.

For detailed usage instructions, refer to this article.

4.2 The Lombok

Lombok is an IDE plugin that helps reduce boilerplate code. In this post, we use Lombok to generate getters and setters for the domain object.

5. The architecture of this app

sbmp_arch1

  • PageHelper is a MyBatis plugin.
  • MyBatis has a Spring Boot starter.

6. The database, table, and initial data

For demonstration purposes, I set up a local database as follows:

create_database.sql
CREATE DATABASE `test` CHARACTER SET utf8 COLLATE utf8_bin;

There is a table tbl_student in the database:

create_table.sql
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;

Insert five records into the table:

insert_data.sql
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

Student.java
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;
}
  • The @Data annotation from Lombok generates getters, setters, and constructors for the Student class.

8. The app codes

8.1 The app code layers

sbmp_arch2

  • Service layer: StudentService, which supports finding students by page.
  • DAO layer: A MyBatis mapper interface.
  • XML layer: A MyBatis XML file defining SQL queries.

8.2 The app project layout

sbmp_arch4

  • A folder named mybatis is created in src/main/resources.

8.3 The application.properties

Add these properties to your application.properties:

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

8.4 The mapper XML

The StudentMapper.xml content:

StudentMapper.xml
<?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" >
    <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>
  • A resultMap and columnList are defined for reuse in SQL queries.
  • The findByPage SQL query matches the mapper interface.
  • Pagination is handled by the PageHelper plugin, so no LIMIT clause is needed.

8.5 The mapper interface

The StudentMapper interface:

StudentMapper.java
@Mapper
public interface StudentMapper {
    Page<Student> findByPage();
}
  • The findByPage method has no parameters; pagination parameters are set elsewhere.

8.6 The service class

The StudentService class:

StudentService.java
@Service
@Transactional(readOnly = true)
public class StudentService {
    @Autowired
    private StudentMapper studentMapper;


    public Page<Student> findByPage(int pageNo, int pageSize) {
        PageHelper.startPage(pageNo, pageSize); // Set pagination parameters
        return studentMapper.findByPage(); // Execute the query
    }
}
  • PageHelper.startPage sets the page number and size.
  • The mapper executes the paginated query.

8.7 The test case

The StudentServiceTest class:

StudentServiceTest.java
@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());
    }
}
  • With 5 records in the table, querying the first page with a page size of 2 should return a total count of 5 and 3 pages.
  • Lombok’s @CommonsLog generates a log object for the test case.

Running the test case produces a green bar: junit_greenbar

The console output:

Terminal window
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])]

9. The com.github.pagehelper.Page class

After calling studentService.findByPage, a Page<Student> object is returned. Its structure is as follows: sbmp_arch3

Summary

This post demonstrated how to implement pagination in a Spring Boot application using MyBatis and the PageHelper plugin. Key steps included configuring the build.gradle file, setting up the database, and writing service and mapper classes. The PageHelper plugin simplifies pagination by eliminating the need for manual LIMIT clauses in SQL queries. The example source code is available on GitHub.

Final Words + More Resources

My intention with this article was to help others who might be considering solving such a problem. So I hope that’s been the case here. If you still have any questions, don’t hesitate to ask me by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!

Tags: