Spring-Boot-摸索之路-5：MyBatis-Plus 强大的条件构造器Wrapper

Posted on

MyBatis-Plus提供了简单的CRUD方法，这次来研究MyBatis-Plus的条件构造器。

构造器关系

Wrapper

  • 蓝色线连起来的是Wrapper的类，其他都是继承
  • 最下边四个是正常类，可以new对象
  • 加了Abstract的是抽象类
  • 蓝色实线是继承关系

wapper介绍 ：

  • Wrapper ： 条件构造抽象类，最顶端父类，抽象类中提供4个方法西面贴源码展示
  • AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件
  • AbstractLambdaWrapper ： Lambda 语法使用 Wrapper统一处理解析 lambda 获取 column。
  • LambdaQueryWrapper ：看名称也能明白就是用于Lambda语法使用的查询Wrapper
  • LambdaUpdateWrapper ： Lambda 更新封装Wrapper
  • QueryWrapper ： Entity 对象封装操作类，不是用lambda语法
  • UpdateWrapper ： Update 条件封装，用于Entity对象更新操作

构造器方法

FunctionName Desc Example
eq( ); 等于 = eq(“name”,”Allwayz”) -> name = ‘Allwayz’
ne( ); 不等于 != ne(“name”,”Allwayz”) -> name<>’Allwayz’
gt( ); 大于 > gt(“age”,18) -> age > 18
ge( ); 大于等于 >= ge(“age”,18) -> age >= 18
lt( ); 小于 < lt(“age”,18) -> age < 18
le( ); 小于等于 <= le(“age”,18) -> age <= 18
between( ); BETWEEN between(“age”,18,30) -> age between 18 and 30
notBetween( ); NOT BETWEEN notBetween(“age”,18,30) -> age not between 18 and 30
like( ); LIKE ‘%value%’ like(“name”,”All”) -> name like ‘%All%’
notLike( ); NOT LIKE ‘%value%’ notLike(“name”,”All”) -> name not like ‘%All%’
likeLeft( ); LIKE ‘%value’ likeLeft(“name”,”All”) -> name like ‘%All’
likeRight( ); LIKE ‘value%’ likeRight(“name”,”All”) -> name like ‘All%’
isNull( ); Value IS NULL isNull(“name”) ->name is null
isNotNull( ); Value IS NOT NULL isNotNull(“name”) ->name is not null
in( ); Value IN(v0,v1…) in(“age”,{1,2,3}) -> age in (1,2,3)
notIn( ); Value NOT IN(v0,v1…) notIn(“age”,{1,2,3}) -> age not in (1,2,3)
inSql( ); Value IN(sql) inSql(“id”,”Select id from table where id < 3”) -> id in (Select id from table where id < 3)
notInSql( ); Value NOT IN(sql) notInSql(“id”,”Select id from table where id < 3”) -> id not in (Select id from table where id < 3)
groupBy( ); 分组 groupBy(“id”,”name”) -> group by id, name
orderByAsc( ); 升序排列 orderByAsc(“id”,”name”) -> order by id ASC, name ASC
orderByDesc( ); 降序排列 orderByDesc(“id”,”name”) -> order by id DESC, nameDESC
orderBy( ); 排列 orderBy(true, true,”id”,”name”) -> order by id ASC, name ASC
having( ); HAVING(sql) having(“sum(age) > {0}”,11) -> having sum (age) >11
or( ); 拼接 OR
and( ); AND 嵌套
apply( ); 拼接 sql
last( ) 无视优化规则直接拼接到sql最后
exists( ); 拼接 EXISTS（sql）
notExists( ); 拼接 NOT EXIXSTS （sql）
nested( ); 正常嵌套 不带AND OR

使用过程

1
2
3
4
5
6
7
8
9
/**
 * Fuzzy query
 */
@Test
public void testSelectMajor(){
    List<Major> majorList = majorMapper
        .selectList(new QueryWrapper<Major>().like("major_code","MC_506"));
    majorList.forEach(System.out::println);
}

控制台

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
JDBC Connection [HikariProxyConnection@2005675644 wrapping com.mysql.cj.jdbc.ConnectionImpl@b70fa38] will not be managed by Spring
// SQL语句
==>  Preparing: SELECT major_id,major_code,major_name,major_status,create_time,update_time,is_delete FROM major WHERE is_delete=0 AND (major_code LIKE ?) 
//变量
==> Parameters: %MC_506%(String)
//结果
<==    Columns: major_id, major_code, major_name, major_status, create_time, update_time, is_delete
<==        Row: 1, MC_50601, History of Architecture, null, 2020-02-14 15:00:21, 2020-02-14 15:00:21, 0
<==        Row: 8, MC_50602, International System in the 20th Century, null, 2020-02-14 15:30:00, 2020-02-14 15:30:00, 0
<==        Row: 9, MC_50603, Ancient Greece:Myth,Art,War, null, 2020-02-14 15:32:37, 2020-02-14 15:32:37, 0
<==      Total: 3
Closing non transactional SqlSession 
[org.apache.ibatis.session.defaults.DefaultSqlSession@38c460e8]
Major(majorId=1, majorCode=MC_50601, majorName=History of Architecture, majorStatus=null, createTime=2020-02-14T15:00:21, updateTime=2020-02-14T15:00:21, isDelete=0)
Major(majorId=8, majorCode=MC_50602, majorName=International System in the 20th Century, majorStatus=null, createTime=2020-02-14T15:30, updateTime=2020-02-14T15:30, isDelete=0)
Major(majorId=9, majorCode=MC_50603, majorName=Ancient Greece:Myth,Art,War, majorStatus=null, createTime=2020-02-14T15:32:37, updateTime=2020-02-14T15:32:37, isDelete=0)