一、MyBatisPlus概述

MyBatis-Plus（简称 MP）是一个MyBatis的增强工具，在MyBatis的基础上只做增强不做改变，为简化开发、提高效率而生。

MyBatisPlus的愿景是成为MyBatis最好的搭档，就像魂斗罗中的1P、2P，基友搭配，效率翻倍。

二、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

SpringBoot与MybatisPlus MybatisPlus（一）