MyBatisPlus基操

先跑起来

依赖

		<dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.0</version>
        </dependency>

yml

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: xx2255
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: false

数据库

entity

@Data
public class User {
    private Integer id;
    private String name;
    private Integer age;
}

mapper

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

测试

 @Autowired
    private UserMapper userMapper;

    @Test
    void test() {
        userMapper.selectList(null).forEach(System.out::println);
    }

结果

User(id=1, name=张三, age=19)
User(id=2, name=李四, age=18)
User(id=3, name=王马子, age=17)

注解

@TableName

映射数据库的表名

@TableName(value = "user")

@TableId

主键映射，type属性是设置主键生成策略，可以设置：

@TableId(value = "id",type = IdType.AUTO)

	AUTO(0),
    NONE(1),
    INPUT(2),
    ASSIGN_ID(3),
    ASSIGN_UUID(4),

值	描述
AUTO	数据库自增
NONE	无状态，实验证明无状态就用雪花
INPUT	码农手动赋值
ASSIGN_ID	MP分配雪花ID，Long、String
ASSIGN_UUID	分配UUID，String

官方说：

另外全局也可以配一下，温馨提醒，踩坑需谨慎

mybatis-plus.global-config.db-config.id-type=assign_id

@TableField

value：映射数据库表非主键的字段

@TableField(value = "name")

exist：是否为数据库字段

@TableField(exist = false)

select：是否查这个字段

@TableField(value = "name",select = false)

fill：比如每次更新时间很烦，除了设置数据库的方式外，MP提供这种更新方法，FieldFill：

	DEFAULT,
    INSERT,
    UPDATE,
    INSERT_UPDATE;

设置后，每次创建一条数据的时候createTime、updateTime都会自动填充值，每次更新时updateTime更新

@TableField(fill = FieldFill.INSERT)
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;

这里枚举设置好后还得自己写个方法实现MetaObjectHandler ，给两个方法写需要赋的值。注意不要忘了给spring注入

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        this.setFieldValByName("createTime",new Date(), metaObject);
        this.setFieldValByName("updateTime",new Date(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime",new Date(), metaObject);
    }
}

@Version

用来标记乐观锁，通过version字段保证数据的安全性，当修改数据的时候，会以version作为条件，当条件成立的时候才会修改。
修改数据库字段，加上version字段，实体类也加上version

	@Version
    private int version;

为了让乐观锁生效，得弄个配置类，返回一个乐观锁拦截器

@Configuration
public class MyBatisPlusConfig {
    
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
}

换了台电脑，刚那台冒烟了。懒得新建数据库和实体类，所以我这里用现成的

		Shop shop = shopMapper.selectById(777);
        shop.setShopName("123");
        shopMapper.updateById(shop);

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@33f2df51] will not be managed by Spring
==>  Preparing: SELECT id,shopName,inventory,version FROM shop WHERE id=? 
==> Parameters: 777(Integer)
<==    Columns: id, shopName, inventory, version
<==        Row: 777, 777, 777, 1
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11c3ff67]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@69f0b0f4] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@33f2df51] will not be managed by Spring
==>  Preparing: UPDATE shop SET shopName=?, inventory=?, version=? WHERE id=? AND version=? 
==> Parameters: 123(String), 777(Integer), 2(Integer), 777(Integer), 1(Integer)
<==    Updates: 1

更新成功
模拟一下两个线程同时修改一条数据

		Shop shop = shopMapper.selectById(999);
        shop.setShopName("001");

        Shop shop1 = shopMapper.selectById(999);
        shop1.setShopName("002");

        shopMapper.updateById(shop);
        shopMapper.updateById(shop1);

如果忽略乐观锁，那么最后的结果应该是002，有了version，乐观锁生效

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6056232d] will not be managed by Spring
==>  Preparing: UPDATE shop SET shopName=?, inventory=?, version=? WHERE id=? AND version=? 
==> Parameters: 001(String), 999(Integer), 2(Integer), 999(Integer), 1(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@66933239]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@470d183] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6056232d] will not be managed by Spring
==>  Preparing: UPDATE shop SET shopName=?, inventory=?, version=? WHERE id=? AND version=? 
==> Parameters: 002(String), 999(Integer), 2(Integer), 999(Integer), 1(Integer)
<==    Updates: 0

@EnumValue

通用枚举类注解，把数据库字段映射成实体类的枚举类型成员变量，枚举类里的code对应数据库里的值

public enum  StatusEnum {
    //两个状态，0是醒着，1是睡着了
    WORK(0,"醒着"),
    SLEEP(1,"睡着了");

    StatusEnum(Integer code, String msg){
        this.code = code;
        this.msg = msg;
    }
    
    @EnumValue
    private Integer code;
    private String msg;
}

实体类加上枚举类型，并且名字要跟数据库字段保持一致

private StatusEnum status;

yml里mybatis plus加上扫描枚举包的配置

type-enums-package: cn.xx.emnus

第二种方法时通过实现IEnum接口，不用写注解

//实现IEnum接口
public enum  StatusEnum implements IEnum<Integer> {
    //两个状态，0是醒着，1是睡着了
    WORK(0,"醒着"),
    SLEEP(1,"睡着了");

    StatusEnum(Integer code, String msg){
        this.code = code;
        this.msg = msg;
    }

    //不写注解
    private Integer code;
    private String msg;

    @Override
    public Integer getValue() {
        return this.code;
    }
}

@TableLogic

做逻辑删除用的
数据库添加deleted字段，实体类添加对应映射并加上注解

	@TableLogic
    private Integer deleted;

yml里mybatis plus的加上配置，删除和没删除分别是用什么数字表示

global-config:
    db-config:
      logic-delete-value: 0
      logic-not-delete-value: 1

测试，数据库数据还存在但状态被改了，代码进行查询操作时并没有把删了的数据查出来，说明逻辑删除起总用了，看它sql语句，每次都会带上deleted条件。

JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@5ceecfee] will not be managed by Spring
==>  Preparing: UPDATE shop SET deleted=0 WHERE id=? AND deleted=1 
==> Parameters: 1(String)
<==    Updates: 1

查询

selectList
不加任何条件全部查询

shopMapper.selectList(null);

看源码，它参数其实是Wrapper接口
单条件查询

		QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id","12365");
        System.out.println(shopMapper.selectList(queryWrapper));

多条件等值查询

		QueryWrapper queryWrapper = new QueryWrapper();
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("shopName","面条");
        map.put("inventory",10);
        queryWrapper.allEq(map);
        System.out.println(shopMapper.selectList(queryWrapper));

大于

queryWrapper.gt("inventory",10);

小于

queryWrapper.lt("inventory",10);

不等于

queryWrapper.ne("inventory",10);

大于等于

queryWrapper.ge("inventory",10);

小于等于

queryWrapper.le("inventory",10);

模糊查询

// %面%
 queryWrapper.like("shopName","面");
 //%面
 queryWrapper.likeLeft("shopName","面");
  //面%
 queryWrapper.likeRight("shopName","面");

inSQL

queryWrapper.inSql("id","select id from shop where id < 10");
queryWrapper.inSql("inventory","select inventory from shop where inventory > 10");

SELECT id,shopName,inventory,version,status,deleted FROM shop WHERE deleted=1 AND (id IN (select id from shop where id < 10) AND inventory IN (select inventory from shop where inventory > 10))

排序

//升序
queryWrapper.orderByAsc("inventory");
//降序
queryWrapper.orderByDesc("inventory");

having

queryWrapper.orderByAsc("inventory");
queryWrapper.having("id > 8");

多ID查询

//WHERE id IN ( ? , ? , ? )
shopMapper.selectBatchIds(Arrays.asList(1,3,4));

selectByMap只能做等值判断，相比queryWrapper 就很l了。

count

shopMapper.selectCount(queryWrapper);

分页
首先加个拦截器的配置，不然不起作用

@Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }

 //分页，3是当前第3页，2是每页2条
        Page<Shop> page = new Page<Shop>(3,2);
        Page<Shop> result = shopMapper.selectPage(page,queryWrapper);
        result.getRecords().forEach(System.out::println);

selectObjs
把所有id查出来

shopMapper.selectObjs(queryWrapper);

selectOne
适用于只查出一条数据

shopMapper.selectOne(queryWrapper);

自定义SQL（多表联查）
比如要通过某人id查他名下所有产品
数据库product：

数据库user：

写个productVo

@Data
public class ProductVo {
    private int category;
    private int count;
    private String shopName;
    private int userId;
    private String name;
}

自定义mapper接口的接口及sql

@Select("select category,count,shopName,userId,name from product ,user where userId = user.id and user.id = #{id}")
List<ProductVo> productList(Integer id);

测试

shopMapper.productList(1).forEach(System.out::println);

结果

ProductVo(category=1, count=10, shopName=手机, userId=1, name=张三)
ProductVo(category=1, count=2, shopName=电脑, userId=1, name=张三)

添加

		Shop shop = new Shop();
        shop.setShopName("包子饺子");
        shopMapper.insert(shop);

删除

//byid删除
        shopMapper.deleteById(1);
        //by好多id删除
        shopMapper.deleteBatchIds(Arrays.asList(11,12,14,19));
        //通过自定义条件删除
        shopMapper.delete(queryWrapper);
        //把条件放到map里，当做条件进行删除

修改

不带条件

		Shop shop = shopMapper.selectById(1);
        shop.setShopName("牛排");
        shopMapper.updateById(shop);

带条件

Shop shop = shopMapper.selectById(1);
        shop.setShopName("牛排");
        shopMapper.update(shop,queryWrapper);