【Spring Boot】MyBatis之通用Mapper与PageHelper

1. 前言

本文主要介绍Mybatis的辅助工具：通用Mapper与PageHelper。

通用Mapper完成了基本的Dao实现，省去了很多sql的编写。

PageHelper可以帮助我们对查询结果集自动分页。

通用Mapper官方文档：天堂有路你不走
pagehelper 官方文档：地狱无门你闯进来

2. 引入依赖

<!-- 通用Mapper -->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>${mybatis.mapper.version}</version>
</dependency>

<!-- 分页助手 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>${mybatis.pagehelper.version}</version>
</dependency>

3. 配置

mapper:
  mappers:
  - tk.mybatis.mapper.common.Mapper
  not-empty: true
  style: camelhump
  wrap-keyword: "`{0}`"
  safe-delete: true
  safe-update: true
  identity: MYSQL
pagehelper:
  auto-dialect: true
  helper-dialect: mysql
  reasonable: true
  params: count=countSql

4. 使用

4.1 DAO

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

@Component
public interface UserMapper extends Mapper<User>, MySqlMapper<User> {
}

4.2 DAO的调用

4.2.1 基础CURD

//插入
userMapper.insertUseGeneratedKeys(user);
//批量插入
userMapper.insertList(userList);
//删除
userMapper.deleteByPrimaryKey(primaryKey);
//更新
userMapper.updateByPrimaryKeySelective(user);
//查询
userMapper.selectByPrimaryKey(1L);
//查询全部
userMapper.selectAll();

4.2.2 分页

int currentPage = 1;
int pageSize = 5;
String orderBy = "id desc";
int count = userMapper.selectCount(null);
PageHelper.startPage(currentPage, pageSize, orderBy);
List<User> users = userMapper.selectAll();  //在数据库中查询会加上分页条件limit
PageInfo<User> userPageInfo = new PageInfo<>(users);

4.2.3 Example条件查询

Example example = new Example(User.class);
// 过滤
example.createCriteria().andLike("name", "%Save1%").orEqualTo("phoneNumber", "17300000001");
// 排序
example.setOrderByClause("id desc");
int count = userMapper.selectCountByExample(example);
// 分页
PageHelper.startPage(1, 3);
// 查询
List<User> userList = userMapper.selectByExample(example);
PageInfo<User> userPageInfo = new PageInfo<>(userList);