MybatisPlus 用法

MybatisPlus 原理

Mp 初体验：

1. 创建mysql表格
2. 创建entity，加上注解@Data
3. 创建Mapper，继承BaseMapper，这时注意泛型要与实体类对应
4. 直接使用

MP原理：

MP是怎么识别到该操作那张表的？

1. 扫描实体类
2. 根据实体类来分析，要操作数据库的那张表
3. 再自动生成操作表的sql语句

所以说，重点是实体类要与表中的表名对起来

常见wrapper的用法

eq等值查询 与 ne不等值查询

// 查询name=flx   age=26的人
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
wrapper.eq("name", "flx")
       .eq("age", 26);
       
List<User> userList = userMapper.selectList(queryWrapper);


// 查询 name!=flx age!=26的人
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ne("name","flx")
            .ne("age",10)

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

当只想进行简单的条件链接时，只需将查询条件直接连写即可，拼装后的sql就是用and连接的

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
wrapper.eq("name", "flx")
       .eq("age", 26);
       
List<User> userList = userMapper.selectList(queryWrapper);

// sql
where name=flx and age=26

gt 大于 与 ge 大于等于、lt小于与le小于等于

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// age > 10
queryWrapper.gt("age",10)
List<User> userList = userMapper.selectList(queryWrapper);

// age >= 10
queryWrapper.ge("age",10)
List<User> userList = userMapper.selectList(queryWrapper);

// age < 10
queryWrapper.lt("age",10)
List<User> userList = userMapper.selectList(queryWrapper);

// age <= 10
queryWrapper.le("age",10)
List<User> userList = userMapper.selectList(queryWrapper);

between 在某个区间内 与 notBetween不在某个区间内

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// 年龄在18岁到25岁之间的
queryWrapper.between("age", 18,25);
List<User> userList = userMapper.selectList(queryWrapper);


// 年龄不在18岁到25岁之间的
queryWrapper.notBetween("age", 18,25);
List<User> userList = userMapper.selectList(queryWrapper);

in在范围内的值 与 notIn不在范围内的值

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

//查询到list集合
List<Type> typeList = typeService.selectById(id);
//结果集
List<String> typeList = new ArrayList<>();
//遍历集合取值
typeList.forEach(item->{
      typeList.add(item.getType());
});

// in 在范围内的值
queryWrapper.in("type", typeList);
List<User> userList = userMapper.selectList(queryWrapper);


// notIn 在范围内的值
queryWrapper.notIn("type", typeList);
List<User> userList = userMapper.selectList(queryWrapper);

inSql、notInSql(几乎用不到)

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

// SELECT * FROM user WHERE (id IN (select id from user where name = flx))
queryWrapper.inSql("id", "select id from user where name = flx");
List<User> userList = userMapper.selectList(queryWrapper);

orderBy、orderByAsc(升序)、orderByDesc(降序)

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

// SELECT id,name,email,age FROM user ORDER BY name ASC
queryWrapper.orderByAsc("name");
List<User> userList = userMapper.selectList(queryWrapper);

// SELECT id,name,email,age FROM user ORDER BY name ASC
queryWrapper.orderByAsc("name","age");
List<User> userList = userMapper.selectList(queryWrapper);


queryWrapper.orderByDesc("name");
List<User> userList = userMapper.selectList(queryWrapper);

like 、likeLeft、likeRight、notLike

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//  "%flx%" 模糊查询
queryWrapper.like("name","flx");
List<User> userList = userMapper.selectList(queryWrapper);

//  "%flx" 模糊查询
queryWrapper.likeLeft("name","flx");
List<User> userList = userMapper.selectList(queryWrapper);

//  "flx%" 模糊查询
queryWrapper.likeRight("name","flx");
List<User> userList = userMapper.selectList(queryWrapper);


//  "%flx%" 模糊查询不成立的值
queryWrapper.notLike("name","flx");
List<User> userList = userMapper.selectList(queryWrapper);

isNull空值查询 与 isNotNull 非空值查询

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// name 是 空值的数据
queryWrapper.isNull("name");
List<User> userList = userMapper.selectList(queryWrapper);

// name 不是 空值的数据
queryWrapper.isNotNull("name");
List<User> userList = userMapper.selectList(queryWrapper);

or、and

查询条件直接连写即可，拼装后的sql就是用and连接的
如果想用() 来区分sql的执行的代码块，可以用and
麻烦的其实可以直接在xml文件中写，没必要按照他的套路来

WHERE id = '1' 
	AND (name LIKE '%flx%'
	OR email LIKE '%163%')

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

queryWrapper.eq("id",1);
queryWrapper.and(wrapper->wrapper.like("name","flx")
                                .or()
                                .like("email","163")
                             

总结一下：

一 运算符
eq等值查询 与 ne不等值查询
gt 大于 与 ge 大于等于、lt小于与le小于等于
其实就是where后面的 = 、!= 、>、>=、<、<=

二 区间内查询
between 在某个区间内 与 notBetween不在某个区间内
in在范围内的值 与 notIn不在范围内的值
inSql 范围内查询，后面跟的是自定义sql语句
其实就是在固定区间 与 自定义区间的查询

三 排序
orderBy、orderByAsc(升序)、orderByDesc(降序)

四 模糊查询 与 非空查询
like 、likeLeft、likeRight、notLike
isNotNull

业务中常用的QueryWrapper总结

QueryWrapper<User> queryWrapper = new QueryWrapper<>();

// WHERE name LIKE '%雨%' AND age BETWEEN 20 AND 40 AND email IS NOT NULL
queryWrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");

//  WHERE manager_id IN (select id from user where name like '王%')
queryWrapper.inSql("manager_id", "select id from user where name like '王%'");

// WHERE age IN (?,?,?)
queryWrapper.in("age", Arrays.asList(30, 31, 32));

// select * from user where deleted=0 and name is not null and age>12
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("deleted",0).isNotNull("name").gt("age",12)

QueryWrapper 与 LambdaQueryWrapper的区别

唯一区别就是 LambdaQueryWrapper可以通过获取Lambda数据库列名