一、MyBatisPlus概述
MyBatis-Plus（简称 MP）是一个MyBatis的增强工具，在MyBatis的基础上只做增强不做改变，为简化开发、提高效率而生。
MyBatisPlus的愿景是成为MyBatis最好的搭档，就像魂斗罗中的1P、2P，基友搭配，效率翻倍。
官方网址：MyBatis-Plus
二、SpringBoot集成MyBatisPlus
1、创建SpringBoot项目，添加MyBatisPlus起步依赖
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- MyBatisPlus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.0</version> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.26</version> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency>
2、在SpringBoot配置文件中配置数据源
# 数据源 spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql:///mybatis?serverTimezone=UTC username: root password: 123456
3、编写实体类
@Data public class User { public Integer id; public String username; public String sex; public String address; public Integer account; }
4、编写Mapper接口，继承BaseMapper
public interface UserMapper extends BaseMapper<User> { }
5、在 SpringBoot启动类中添加 @MapperScan 注解，扫描Mapper文件夹
@SpringBootApplication @MapperScan("com.zj.mapper") public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } }
6、测试Mapper方法
@SpringBootTest public class TestUserMapper { @Resource private UserMapper userMapper; @Test public void test(){ User user = userMapper.selectById(10); System.out.println(user); } }
二、MyBatisPlus CRUD
2.1 添加
1、配置文件开启SQL日志打印
# 开启SQL日志 mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2、测试添加方法
@Test public void testAdd(){ User user = new User(null,"梅川内酷","男","临沂市",3000); userMapper.insert(user); // MyBatisPlus插入成功后，可直接直接获取主键的值 System.out.println(user.getUserId()); }
2.2 相关注解
我们将user类中的字段修改为和数据库表中的字段不一致。将表名称改为tb_user, 这样表中的字段、表名都和实体类不一致。这样mybatosplus无法自动将字段和属性对应。也就无法生成正确的sql语句。此时需要使用注解来指定映射关系。
@TableName
作用：指定类为哪个表的实体类
位置：类上方
@TableId
作用：指定实体类的属性为主键
位置：属性上方
属性：
- value：主键字段名
- type：主键策略
| 值 | 描述 |
| NONE | 无状态,不设置主键类型 |
| AUTO | 自增主键 |
| INPUT | 插入前自行设置主键值 |
| ASSIGN_ID | 默认策略，使用雪花算法自动生成主键ID，主键类型为整形或字符串。（雪花算法：微博开源的分布式ID生成算法，使用一个64位的Long类型数字作为全局唯一ID。在分布式系统中的应用十分广泛，且ID引入了时间戳，基本上保持自增） |
| ASSIGN_UUID | 自动生成排除中划线的UUID作为主键，主键类型为 |
@TableField
作用：在属性和列名不同的情况下，指定映射关系
位置：非主键属性上方
@Data @AllArgsConstructor @TableName("tb_user") public class User { @TableId(value = "id",type = IdType.AUTO) public Integer UserId; @TableField("username") public String userName; @TableField("sex") public String userSex; @TableField("address") public String userAddress; @TableField("account") public Integer userAccount; }
2.3 修改
@Test public void testUpdate(){ User user = new User(1,"梅川丘酷","男","烟台市",4000); userMapper.updateById(user); }
2.4 删除
根据id删除
@Test public void testDeleteById(){ userMapper.deleteById(8); }
批量删除
@Test public void testDeleteBatch(){ ArrayList<Integer> ids = new ArrayList<>(); ids.add(9); ids.add(10); ids.add(11); userMapper.deleteBatchIds(ids); }
根据字段的条件删除
@Test public void testDeleteMap(){ Map<String,Object> map = new HashMap<String,Object>(); map.put("sex","男"); ;//删除所有字段（字段是数据库列名不是类的属性名）值是男的数据 userMapper.deleteByMap(map); }
2.5 查询
根据id查询
@Test public void testFindById(){ User user = userMapper.selectById(1); System.out.println(user); }
==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user WHERE id=? ==> Parameters: 5(Integer) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 5, 翟玲娇, 女, 长沙市, 1000 <== Total: 1
根据id批量查询
@Test public void testFindBatch(){ ArrayList<Integer> ids = new ArrayList<>(); ids.add(5); ids.add(6); ids.add(12); List<User> users = userMapper.selectBatchIds(ids); }
==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user WHERE id IN ( ? , ? , ? ) ==> Parameters: 5(Integer), 6(Integer), 12(Integer) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 5, 翟玲娇, 女, 长沙市, 1000 <== Row: 6, 张晓, 女, 青岛市, 2500 <== Row: 12, 唐宛凝, 女, 石家庄市, 3334 <== Total: 3
根据字段条件查询
@Test public void testFindByMap(){ Map<String,Object> map = new HashMap<String,Object>(); //条件之间是and关系 map.put("username","张晓"); map.put("address","青岛市"); List<User> users = userMapper.selectByMap(map); }
==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user WHERE address = ? AND username = ? ==> Parameters: 青岛市(String), 张晓(String) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 6, 张晓, 女, 青岛市, 2500 <== Total: 1
2.7 条件构造器
Mybatis-Plus通过QueryWrapper对象让用户自由的构建SQL条件，简单便捷，没有额外的负担，能够有效提高开发效率。
条件参数说明:
| 查询方式 | 说明 |
| or | 或条件语句 |
| and | 且条件语句 |
| like | 模糊查询 like |
| notLike | 模糊查询 not Like |
| exists | exists 条件语句 |
| notExists | not Exists 条件语句 |
| isNull | null 值查询 |
| isNotNull | is Not Null 查询 |
| in | in 查询 |
| notIn | not in 查询 |
| groupBy | 分组查询 |
| orderBy | 排序查询 |
| having | 分组后筛选 |
| eq | 等于 = |
| ne | 不等于 <> |
| between | between 条件语句 |
| gt | 大于> |
| ge | 大于等于>= |
| lt | 小于< |
| le | 小于等于<= |
示例1：查询账户余额在1000-3000之间的用户的信息
@Test public void testFindWrapper(){ //创建条件构造器 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.ge("account", 1000).lt("account", 3000); List<User> users = userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } }
==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user WHERE (account >= ? AND account < ?) ==> Parameters: 1000(Integer), 3000(Integer) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 5, 翟玲娇, 女, 长沙市, 1000 <== Row: 6, 张晓, 女, 青岛市, 2500 <== Total: 2
示例2：查询账户小于1000或者大于等于9000的用户
@Test public void testFindWrapper(){ //创建条件构造器 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.lt("account", 2000).or().ge("account", 9000); List<User> users = userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } }
==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user WHERE (account < ? OR account >= ?) ==> Parameters: 2000(Integer), 9000(Integer) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 5, 翟玲娇, 女, 长沙市, 1000 <== Row: 16, 郑怜雪, 女, 重庆市, 555900 <== Row: 19, 黎飒, 男, 深圳市, 9000 <== Total: 3
示例3：查询名字中有张的用户，按照账户升序排序。
@Test public void testFindWrapper(){ //创建条件构造器 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("username", "张").orderByAsc("account"); List<User> users = userMapper.selectList(wrapper); }
==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user WHERE (username LIKE ?) ORDER BY account ASC ==> Parameters: %张%(String) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 6, 张晓, 女, 青岛市, 2500 <== Row: 21, 张三, 男, 昆明市, 5000 <== Total: 2
2.8 分页查询
1、在配置类或启动类配置分页插件
// 注册插件 @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; }
2、测试分页查询
@Test public void testFindPage(){ //分页条件;第一个参数表示从第几条开始，第二个参数表示取几条数据。 Page page = new Page(0,2); IPage<User> iPage = userMapper.selectPage(page, null);//null表示查询全部 System.out.println("结果集:"+iPage.getRecords()); System.out.println("总页数："+iPage.getPages()); System.out.println("总条数:"+iPage.getTotal()); System.out.println("当前页："+iPage.getCurrent()); System.out.println("每页条数:"+iPage.getSize()); }
==> Preparing: SELECT COUNT(*) AS total FROM tb_user ==> Parameters: <== Columns: total <== Row: 8 <== Total: 1 ==> Preparing: SELECT id AS UserId,username,sex AS userSex,address AS userAddress,account AS userAccount FROM tb_user LIMIT ? ==> Parameters: 2(Long) <== Columns: UserId, username, userSex, userAddress, userAccount <== Row: 5, 翟玲娇, 女, 长沙市, 1000 <== Row: 6, 张晓, 女, 青岛市, 2500 <== Total: 2 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5ff2b8ca] 结果集:[User(UserId=5, userName=翟玲娇, userSex=女, userAddress=长沙市, userAccount=1000), User(UserId=6, userName=张晓, userSex=女, userAddress=青岛市, userAccount=2500)] 总页数：4 总条数:8 当前页：1 每页条数:2
2.9 全局配置
假如数据库的所有表都以tb_开头，主键都是自增的。如果针对每一个实体类都要添加相关注解比较麻烦，可以在SpringBoot配置文件中进行全局配置，该配置在所有的实体类中都生效。
mybatis-plus: # 全局配置 global-config: db-config: #主键类型 id-type: AUTO # 设置表名前缀 table-prefix: tb_ # 是否使用驼峰转下划线命名，默认开启 ,例如 实体类中的UserAddress属性等于数据库中的 user_address字段 table-underline: true
