Mybatis 学习之 分页实现

1. Mybatis

1.1. 代码实现

package com.example.demo;import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@MapperScan(value = "com.example.demo.mapper")
@SpringBootApplication
public class DemoApplication {public static void main(String[] args) {SpringApplication.run(DemoApplication.class, args);}}

package com.example.demo.mapper;import com.example.demo.model.SysUser;
import com.example.demo.model.page.BasePageReq;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;@Mapper
public interface UserMapper {List<SysUser> page(@Param(value = "pageReq") BasePageReq pageReq);}

<?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.example.demo.mapper.UserMapper"><select id="page" resultType="com.example.demo.model.SysUser"><bind name="startIndex" value="(pageReq.pageNum-1)*pageReq.pageSize"/>SELECT ID AS id, NAME AS name, AGE AS ageFROM SYS_USERORDER BY ID ASCLIMIT #{startIndex}, #{pageReq.pageSize}</select></mapper>

mybatis:mapper-locations: classpath:mapper/*.xmltype-aliases-package: com.example.demo.model

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.3.0</version>
</dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><scope>provided</scope>
</dependency>
<dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>30.1-jre</version>
</dependency>

2. Mybatis Plus

2.1. 代码实现

package com.example.demo;import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@MapperScan(value = "com.example.demo.mapper")
@SpringBootApplication
public class DemoApplication {public static void main(String[] args) {SpringApplication.run(DemoApplication.class, args);}}

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;Page<SysUser> list = userMapper.page(new Page<>(pageReq.getPageNum(), pageReq.getPageSize()));

package com.example.demo.mapper;import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.model.SysUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;@Mapper
public interface UserMapper {Page<SysUser> page(@Param(value = "pageReq") IPage<SysUser> pageReq);}

<?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.example.demo.mapper.UserMapper"><select id="page" resultType="com.example.demo.model.SysUser">SELECT ID AS id, NAME AS name, AGE AS ageFROM SYS_USERORDER BY ID ASC</select></mapper>

mybatis-plus:mapper-locations: classpath*:mapper/*.xmltype-aliases-package: com.example.demo.modelconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.1</version>
</dependency>
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><scope>provided</scope>
</dependency>
<dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>30.1-jre</version>
</dependency>

2.2. 特别注意

• Mybatis Plus 分页功能中使用了 CCJSqlParser，其中 CCJSqlParser#Statement 在处理 复杂 SQL 时，存在 性能问题

3. PageHelper

3.1. 代码实现

package com.example.demo;import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@MapperScan(value = "com.example.demo.mapper")
@SpringBootApplication
public class DemoApplication {public static void main(String[] args) {SpringApplication.run(DemoApplication.class, args);}}

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;Page<SysUser> list = PageHelper.startPage(pageReq.getPageNum(), pageReq.getPageSize()).doSelectPage(() -> userMapper.page());

package com.example.demo.mapper;import com.example.demo.model.SysUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;@Mapper
public interface UserMapper {List<SysUser> page();}

<?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.example.demo.mapper.UserMapper"><select id="page" resultType="com.example.demo.model.SysUser">SELECT ID AS id, NAME AS name, AGE AS ageFROM SYS_USERORDER BY ID ASC</select></mapper>

mybatis-plus:mapper-locations: classpath*:mapper/*.xmltype-aliases-package: com.example.demo.modelconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.1</version>
</dependency>
<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.15</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper</artifactId><version>5.0.0</version>
</dependency>
<dependency><groupId>com.github.jsqlparser</groupId><artifactId>jsqlparser</artifactId><version>4.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.24</version><scope>provided</scope>
</dependency>
<dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><version>30.1-jre</version>
</dependency>

3.2. 特别注意

• PageHelper 分页功能中使用了 CCJSqlParser，其中 CCJSqlParser#Statement 在处理 复杂 SQL 时，存在 性能问题

---

参考资料

• Mybatis PageHelper编译SQL引发的一次性能问题.18286262