MyBatis-pius知识

配置

mybatis日志

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

主键策略

1：默认ID_WORKER

MyBatis-Plus默认的主键策略是：ID_WORKER  全局唯一ID

2：自增策略

•  需要在创建数据表的时候设置主键自增
• 实体字段中配置 @TableId(type = IdType.AUTO)

@TableId(type = IdType.AUTO)
private Long id;

想影响所有实体的配置，可以设置全局主键配置

#全局设置主键生成策略
mybatis-plus.global-config.db-config.id-type=auto

3：自动填充

项目中经常会遇到一些数据，每次都使用相同的方式填充，例如记录的创建时间，更新时间等。

我们可以使用MyBatis Plus的自动填充功能，完成这些字段的赋值工作

• 在User表中添加datetime类型的新的字段 create_time、update_time
• 实体上添加注解

@Data
public class User {
    ......
        
    @TableField(fill = FieldFill.INSERT)
    private Date createTime;

    //@TableField(fill = FieldFill.UPDATE)
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

• 实现元对象处理接口

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    private static final Logger LOGGER = LoggerFactory.getLogger(MyMetaObjectHandler.class);

    @Override
    public void insertFill(MetaObject metaObject) {
        LOGGER.info("start insert fill ....");
// 插入时自动填充 create_time 字段,该属性上必须有 @TableField(fill = FieldFill.INSERT) 注解
        this.setFieldValByName("createTime", new Date(), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        LOGGER.info("start update fill ....");
 // 更新时自动填充 update_time 字段,该属性上必须有 @TableField(fill = FieldFill.UPDATE) 注解
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
}

查询

//根据id查询
userMapper.selectById(1L)

//通过多个id批量查询
userMapper.selectBatchIds(Arrays.asList(1, 2, 3))


//通过map封装查询条件
 HashMap<String, Object> map = new HashMap<>();
 map.put("name", "Helen");
 map.put("age", 18);
 List<User> users = userMapper.selectByMap(map);

分页

配置类

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

page对象

Page<User> page = new Page<>(1, 5);

IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, null);

逻辑删除

1：添加deleted字段

ALTER TABLE `user` ADD COLUMN `deleted` boolean

2：实体类添加deleted字段

@TableLogic
@TableField(fill = FieldFill.INSERT)
private Integer deleted;

3：元对象处理接口添加deleted字段默认值

@Override
public void insertFill(MetaObject metaObject) {
    ......
    this.setFieldValByName("deleted", 0, metaObject);
}

4：注册bean

@Bean
public ISqlInjector sqlInjector() {
    return new LogicSqlInjector();
}

• 测试后发现，数据并没有被删除，deleted字段的值由0变成了1
• 测试后分析打印的sql语句，是一条update
• 注意：被删除数据的deleted 字段的值必须是 0，才能被选取出来执行逻辑删除的操作

复杂查询

1：ge、gt、le、lt、isNull、isNotNull

 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
//name为空
        .isNull("name")
//age大于12
        .ge("age", 12)
//email非空
        .isNotNull("email");
//SQL语句为UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL
    int result = userMapper.delete(queryWrapper);

2：eq、ne

 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name", "Tom");
//sql：SELECT id,name,age,email,create_time,update_time,deleted,version FROM user WHERE deleted=0 AND name = ? 
    User user = userMapper.selectOne(queryWrapper);

3：between、notBetween

    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.between("age", 20, 30);
//sql：SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ? 
    Integer count = userMapper.selectCount(queryWrapper);

4：like、notLike、likeLeft、likeRight

  QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper
//name中不包含字母e
        .notLike("name", "e")
//email以t结尾
        .likeRight("email", "t");
//sql：SELECT id,name,age,email,create_time,update_time,deleted,version 
FROM user WHERE deleted=0 AND name NOT LIKE ? AND email LIKE ? 
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表

5：last

直接拼接到 sql 的最后

注意：只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.last("limit 1");
//sql：SELECT id,name,age,email,create_time,update_time,deleted,version 
FROM user WHERE deleted=0 limit 1 
  List<User> users = userMapper.selectList(queryWrapper);

6：指定要查询的列

 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("id", "name", "age");

sql：SELECT id,name,age FROM user WHERE deleted=0 

    List<User> users = userMapper.selectList(queryWrapper);