本系列博客其他文章请点击下方链接查看
【Mybatis-Plus学习笔记】目录
目录
BaseMapper
在mybatis-plus的BaseMapper中有很多基础查询方法
- selectById（int id）
User user = userMapper.selectById(1087982257332887553L);
等同于sql
select * from xxx where id = ?
- selectBatchIds(List ids)
List<User> users = userMapper.selectBatchIds(ids);
等同于sql
select * from xxx where id in (? ... ?)
- selectByMap(HashMap hashMap)
List<User> user = userMapper.selectByMap(hashMap);
等同于sql
select* from xxx where name = ? , value = ?
abstractWrapper
为了满足更复杂的需求，mybatis-plis提供了abstractWrapper来实现条件构造器查询。
条件构造器有两种声明方式
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
QueryWrapper<User> query = Wrappers.<User>query();
复杂查询
1、名字中包含雨并且年龄小于40
name like '%雨%' and age<40
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").lt("age", 40);
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age < ?
''DEBUG==> Parameters: %雨%(String), 40(Integer)
2、名字中包含雨年并且龄大于等于20且小于等于40并且email不为空
name like '%雨%' and age between 20 and 40 and email is not null
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").between("age", 20,40).isNotNull("email");
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL
''DEBUG==> Parameters: %雨%(String), 20(Integer), 40(Integer)
3、名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列
name like '王%' or age>=25 order by age desc,id asc
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").ge("age", 25)
.orderByDesc("age").orderByAsc("id");
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age >= ? ORDER BY age DESC , id ASC
''DEBUG==> Parameters: 王%(String), 25(Integer)
4、创建日期为2019年2月14日并且直属上级为名字为王姓
date_format(create_time,'%Y-%m-%d')='2019-02-14' and manager_id in (select id from user where name like '王%')
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-02-14")
.inSql("manager_id", "select id from user where name like '王%'");
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE date_format(create_time,'%Y-%m-%d') = ? AND manager_id IN (select id from user where name like '王%')
''DEBUG==> Parameters: 2019-02-14(String)
5、名字为王姓并且（年龄小于40或邮箱不为空）
name like '王%' and (age<40 or email is not null)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王")
.nested(i -> i.lt("age", 40).or().isNotNull("email"));
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND ( age < ? OR email IS NOT NULL )
''DEBUG==> Parameters: 王%(String), 40(Integer)
6、名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）
name like '王%' or (age<40 and age>20 and email is not null)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").or()
.nested(i -> i.between("age", 20,40).isNotNull("email"));
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL )
''DEBUG==> Parameters: 王%(String), 20(Integer), 40(Integer)
7、（年龄小于40或邮箱不为空）并且名字为王姓
(age<40 or email is not null) and name like '王%'
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(i -> i.lt("age", 40).or().isNotNull("email"))
.likeRight("name", "王");
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE ( age < ? OR email IS NOT NULL ) AND name LIKE ?
''DEBUG==> Parameters: 40(Integer), 王%(String)
8、年龄为30、31、34、35
age in (30、31、34、35)
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
List<Integer> list = Arrays.asList(30, 31, 34, 35);
queryWrapper.in("age", list);
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age IN (?,?,?,?)
''DEBUG==> Parameters: 30(Integer), 31(Integer), 34(Integer), 35(Integer)
9、只返回满足条件的其中一条语句即可
limit 1
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
List<Integer> list = Arrays.asList(30, 31, 34, 35);
queryWrapper.in("age", list).last("limit 1");
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age IN (?,?,?,?) limit 1
''DEBUG==> Parameters: 30(Integer), 31(Integer), 34(Integer), 35(Integer)
select中字段不全部出现的查询
10、名字中包含雨并且年龄小于40(需求1加强版)
select id,name
from user
where name like '%雨%' and age<40
方法一
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").lt("age", 40)
.select("id","name");
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name FROM user WHERE name LIKE ? AND age < ?
''DEBUG==> Parameters: %雨%(String), 40(Integer)
方法二
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨").lt("age", 40)
.select(User.class, info -> !info.getColumn().equals("create_time")
&& !info.getColumn().equals("manager_id"));
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email FROM user WHERE name LIKE ? AND age < ?
''DEBUG==> Parameters: %雨%(String), 40(Integer)
Condition
需求：根据姓名和邮箱查询，需要判断参数是否为空。如果没有Condition，需要如下编码。
private void withoutCondition(String name, String email) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
if (StringUtils.isNotEmpty(name)) {
queryWrapper.like("name", name);
}
if (StringUtils.isNotEmpty(email)) {
queryWrapper.like("email", email);
}
List<User> users = userMapper.selectList(queryWrapper);
}
使用Condition的编码如下。
private void withCondition(String name, String email) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like(StringUtils.isNotEmpty(name), "name", name)
.like(StringUtils.isNotEmpty(email),"email", email);
List<User> users = userMapper.selectList(queryWrapper);
}
不难看出，condition就是在原有的like参数前再加入一个判别式，作为Boolean类型的参数，如果判别式的结果为false，后边的判断将不起作用。
用实体作为条件构造器的参数传入
User userWhere = new User();
userWhere.setName("王天风");
userWhere.setAge(25);
QueryWrapper<User> queryWrapper = new QueryWrapper<>(userWhere);
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name=? AND age=?
''DEBUG==> Parameters: 王天风(String), 25(Integer)
简单来说就是把实体类中不为空的参数作为查询的对象来继续查询。
值得注意的是这种生成生存的where条件语句和链式编程生成的where条件语句，没有关联，也就是不冲突。
User userWhere = new User();
userWhere.setName("王天风");
userWhere.setAge(25);
QueryWrapper<User> queryWrapper = new QueryWrapper<>(userWhere);
queryWrapper.like("name", "王天风")
.like("age", 25);
List<User> users = userMapper.selectList(queryWrapper);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name=? AND age=? AND name LIKE ? AND age LIKE ?
''DEBUG==> Parameters: 王天风(String), 25(Integer), %王天风%(String), %25%(String)
所以请慎重使用。
同时如果使用实体类作为参数，为了达到更丰富的效果，还可以对参数进行注解修饰。如下
@TableField(condition = SqlCondition.LIKE)
private String name;
因为queryWrapper带有实体的构造器默认使用等值的方法进行查询，而加上上式中的注解，就可以变成like。
除此以外，还可以使用一些自定义condition。如下
@TableField(condition = "%s<#{%s}")
private int age;
执行后SQL如下：
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE CONCAT('%',?,'%') AND age<?
''DEBUG==> Parameters: 王天风(String), 25(Integer)
allEq
HashMap<String,Object> hashMap = new HashMap();
hashMap.put("name", "王天风");
hashMap.put("age", 25);
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq(hashMap);
List<User> users = userMapper.selectList(queryWrapper);
可以使用hashmap作为参数进行查询，查询方式就是map的key和value进行等值匹配。如果有值为空，会拼接is null。如果想忽略空值，就调用allEq的重载方法，多传入一个为false的Boolean常量。
官方文档中的例子非常形象的解释了这一现象
allEq还有一种用法
allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
其中BiPredicate是一个接口参数，可以起到过滤作用。
HashMap<String,Object> hashMap = new HashMap();
hashMap.put("name", "王天风");
hashMap.put("age", 25);
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.allEq((k,v)->!k.equals("name"),hashMap);
List<User> users = userMapper.selectList(queryWrapper);
作用就是按照不等于name的规则进行过滤。sql等同于
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age = ?
''DEBUG==> Parameters: 25(Integer)
其中：filter是过滤函数——是否允许字段传入比对条件中，params 与 null2IsNull同上，condition就和之前说的是完全一样的。
SelectMaps
应用场景
- 只需要查询其中几个字段，如果使用selectList返回实体，这样实体中就会有很多null
queryWrapper.select("id","name").like("name", "天");
List<Map<String, Object>> list = userMapper.selectMaps(queryWrapper);
list.forEach(System.out::println);
等同于sql
''DEBUG==> Preparing: SELECT id,name FROM user WHERE name LIKE ?
''DEBUG==> Parameters: %天%(String)
返回值，就只有name和id，如下：
{name=王天风, id=1088248166370832385}
- 统计查询
11、按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。
并且只取年龄总和小于500的组。
select avg(age) avg_age,min(age) min_age,max(age) max_age
from user
group by manager_id
having sum(age) <500
queryWrapper.select("avg(age) avg_age","min(age) min_age","max(age) max_age")
.groupBy("manager_id")
.having("sum(age) <{0}",500);
List<Map<String, Object>> list = userMapper.selectMaps(queryWrapper);
list.forEach(System.out::println);
等同于sql
''DEBUG==> Preparing: SELECT avg(age) avg_age,min(age) min_age,max(age) max_age FROM user GROUP BY manager_id HAVING sum(age) <?
''DEBUG==> Parameters: 500(Integer)
返回值如下：
{max_age=15, avg_age=15.0000, min_age=15}
{max_age=25, avg_age=25.0000, min_age=25}
{max_age=32, avg_age=30.3333, min_age=28}
selectObjs
selectObjs只返回第一个字段的值
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "天");
List<Object> list = userMapper.selectObjs(queryWrapper);
list.forEach(System.out::println);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ?
''DEBUG==> Parameters: %天%(String)
返回值如下：
1088248166370832385
虽然查询的是所有字段，但是只返回第一个字段，也就是id
selectCount
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "雨");
Integer count = userMapper.selectCount(queryWrapper);
System.out.println(count);
等同于sql
''DEBUG==> Preparing: SELECT COUNT( 1 ) FROM user WHERE name LIKE ?
''DEBUG==> Parameters: %雨%(String)
返回值如下：
2
就是数量，非常简单。
selectOne
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "天");
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ?
''DEBUG==> Parameters: %天%(String)
只能查询一个数据，如果多了，会报错org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2。谨慎使用
Lambda构造器
以下是Lambda构造器的三种实例方式
LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();
LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
接下来来简单使用一下
LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery();
lambdaQuery.like(User::getName, "雨").lt(User::getAge,40);
List<User> users = userMapper.selectList(lambdaQuery);
等同于sql
''DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age < ?
''DEBUG==> Parameters: %雨%(String), 40(Integer)
优点就是防止列名打错。
还有一个特殊的Lambda构造器
new LambdaQueryChainWrapper<User>(userMapper).like(User::getName, "雨").lt(User::getAge,40).list();
集成了更多的方法，可以直接实例来使用查询语句，最后可以使用list来返回一个集合。
自定义Sql
在UserMapper中使用注解来创建一个方法，如下：
@Select("select * from user ${ew.customSqlSegment}")
List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> wrapper);
参数是一个Wrapper，而select语句中ew就是Constants.WRAPPER，而customSqlSegment就是Wrapper中对应的语句，我们把之前的例子中的selectList替换成我们自己写的selectAll。结果是一样的。
但是我们不建议使用@Select注解，这样会失去Mybatis 的意义，我们还是把Sql语句写在了逻辑层。所以我们使用Xml来存放Sql语句。
如果使用过mybatis的朋友，应该早已信手拈来。首先，在配置文件中添加以下配置
mybatis-plus.mapper-locations= classpath:mapper/UserMapper.xml
这个根据自己的配置会不一样，总之就是让程序知道xml文件的位置。
然后编写XML文件即可
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.x.x.UserMapper">
<select id="selectAll" resultType="com.fang.entity.User">
select * from user ${ew.customSqlSegment};
</select>
</mapper>
记得取消@Select注解