详细分析Mybatis与MybatisPlus中分页查询的差异（附Demo）

前言

更多的知识点推荐阅读：

1. 【Java项目】实战CRUD的功能整理（持续更新）
2. java框架 零基础从入门到精通的学习路线 附开源项目面经等（超全）

本章节主要以Demo为例，直奔主题，对于更多的基本知识可看我的框架架构

1. Mybatis

主要的逻辑从外往深走，具体如下：

1. 创建一个Java类，实现业务逻辑，在该类中调用上述接口中的方法获取分页数据并构建PageResult对象返回
2. 创建一个Java接口，声明两个方法，分别用于执行这两个查询语句
3. 创建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.example.system.mapper.UserMapper"><select id="fetchCustomPageResults"resultType="com.example.system.model.UserModel" >SELECT * FROM users<where><if test="criteria.name != null and criteria.name !=''">AND name LIKE CONCAT('%',#{criteria.name},'%')</if><if test="criteria.createTime != null">AND create_time BETWEEN #{criteria.createTime[0]}, #{criteria.createTime[1]},</if><if test="criteria.status != null">AND status = #{criteria.status}</if></where>ORDER BY id DESCLIMIT #{criteria.pageNumber}, #{criteria.pageSize}</select><select id="fetchCustomTotalCount" resultType="Long" >SELECT COUNT(1) FROM users<where><if test="criteria.name != null and criteria.name !=''">AND name LIKE CONCAT('%',#{criteria.name},'%')</if><if test="criteria.createTime != null">AND create_time BETWEEN #{criteria.createTime[0]}, #{criteria.createTime[1]},</if><if test="criteria.status != null">AND status = #{criteria.status}</if></where></select></mapper>

@Mapper
public interface CustomUserMapper extends BaseMapperX<UserModel> {/*** 获取自定义分页结果*/List<UserModel> fetchCustomPageResults(@Param("criteria") UserCriteria criteria);/*** 获取自定义总记录数*/Long fetchCustomTotalCount(@Param("criteria") UserCriteria criteria);}@Service
@Slf4j
public class CustomUserService {@Overridepublic PageResult<UserModel> fetchCustomUserPage(UserCriteria criteria) {return new PageResult<>(userMapper.fetchCustomPageResults(criteria),userMapper.fetchCustomTotalCount(criteria));}
}

对应实现类如下：

@Service
@Slf4j
public class CustomUserServiceImpl implements CustomUserService{private final CustomUserMapper userMapper;@Autowiredpublic CustomUserService(CustomUserMapper userMapper) {this.userMapper = userMapper;}@Overridepublic PageResult<UserModel> fetchCustomUserPage(UserCriteria criteria) {return new PageResult<>(userMapper.fetchCustomPageResults(criteria),userMapper.fetchCustomTotalCount(criteria));}
}

以上的Demo提供一个框架思路

2. MybatisPlus

具体代码如下：

<?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.system.mapper.AdminUserMapper"><select id="fetchCustomPageResults"resultType="com.example.system.model.UserModel" >SELECT * FROM users<where><if test="criteria.name != null and criteria.name !=''">AND username LIKE CONCAT('%',#{criteria.name},'%')</if><if test="criteria.createTime != null">AND create_time BETWEEN #{criteria.createTime[0]}, #{criteria.createTime[1]},</if><if test="criteria.status != null">AND status = #{criteria.status}</if></where>ORDER BY id DESC</select></mapper>

@Mapper
public interface CustomAdminUserMapper extends BaseMapperX<UserModel> {IPage<UserModel> fetchCustomPageResults(IPage<UserModel> page, @Param("criteria") UserCriteria criteria);}

其实现类如下：

@Service
@Slf4j
public class CustomUserServiceImpl implements CustomUserService{private final CustomAdminUserMapper userMapper;@Autowiredpublic CustomAdminUserService(CustomAdminUserMapper userMapper) {this.userMapper = userMapper;}@Overridepublic PageResult<UserModel> fetchCustomUserPage(UserCriteria criteria) {// 必须使用 MyBatis Plus 的分页对象IPage<UserModel> page = new Page<>(criteria.getPageNo(), criteria.getPageSize());userMapper.fetchCustomPageResults(page, criteria);return new PageResult<>(page.getRecords(), page.getTotal());}
}

3. 实战

看Demo示例可能有些抽象，结合自身项目给一个示例

Controller类如下：

@GetMapping("/listByOrderId")
@ApiOperationSupport(order = 8)
@ApiOperation(value = "分页", notes = "传入docheck")
public R<List<DoCheckVO>> list(Long orderId) {List<DoCheck> doChecks = docheckService.listByOrderId(orderId);return R.data(DocheckWrapper.build().listVO(doChecks));
}

对应的Service类如下：

List<DoCheck> listByOrderId(Long orderId);

其中的实现类如下：

@Override
public List<DoCheck> listByOrderId(Long orderId) {return docheckMapper.listByOrderId(orderId);
}

Mapper类如下：

List<DoCheck> listByOrderId(Long orderId);

对应的Xml代码如下：

<select id="listByOrderId" resultType="org.springblade.equipment.entity.DoCheck">select * from equipment_check_order_docheck  where is_deleted = 0 and check_order_id=#{orderId}
</select>

其中xml的上文如下：