05-Wrapper

Wrapper

创建Wrapper

Mapper构造方法和Wrappers工具类创建方法中都可以接受实体对象来做等值查询，如果想改变这种等值查询的默认行为，则需要在实体类上添加@TableField注解，修改condition属性的值，为SqlCondition类中定义的常量，如果SqlCondition类中定义的这些常量还不满足需求就需要自己手动模仿SqlCondition类中的常量写了

QueryWrapper<Student> queryWrapper = Wrappers.<User>query();  //工具类创建
QueryWrapper<Student> queryWrapper = new QueryWrapper<>();  //new对象创建方式

Wrapper常用方法

这些方法可以使用链式调用，使用的是and链接

方法名	描述	等价SQL
eq(R column, Object val)	等于	eq("name", "张三") ---> name = '张三'
ne(R column, Object val)	不等于	ne("name", "张三") ---> name <> '张三'
gt(R column, Object val)	大于	gt("age", 18) ---> age > 18
ge(R column, Object val)	大于等于	gt("age", 18) ---> age >= 18
lt(R column, Object val)	小于	lt("age", 18) ---> age < 18
le(R column, Object val)	小于等于	le("age", 18) ---> age <= 18
between(R column, Object val1, Object val2)	between and	between("age", 18, 30) ---> age between 18 and 30
notBetween(R column, Object val1, Object val2)	not between and	notBetween("age", 18, 30 ---> age not between 18 and 30
like(R column, Object val)	like '%值%'	like("name", "张") ---> name like '%张%'
notLike(R column, Object val)	not like '%值%'	notLike("name", "张") ---> name not like '%张%'
likeLeft(R column, Object val)	like '%值'	likeLeft("name", "张") ---> name like '%张'
likeRight(R column, Object val)	like '值%'	likeRight("name", "张") ---> name like '张%'
isNull(R column)	字段 is null	isNull("name") ---> name is null
isNotNull(R column)	字段 is not null	isNotNull("name") ---> name is not null
in(R column, Collection<?> value)	字段 in （value[0], value[1]...）	in("age",Arrays.asList(1, 2, 3)) ---> age in (1, 2, 3)
notIn(R column, Collection<?> value)	字段 not in （value[0], value[1]...）	notIn("age",Arrays.asList(1, 2, 3)) ---> age not in (1, 2, 3)
inSql(R column, String inValue)	字段 in (sql语句)	inSql("id", "select id from table where id < 3") ---> where id in (select id from table id < 3)
notInSql(R column, String inValue)	字段 not in (sql语句)	notInSql("id", "select id from table where id < 3")--->where id not in (select id from table id < 3)
groupBy(R... columns)	group by 字段, ...	groupBy("id", "name") ---> group by id,name
having(String sqlHaving, Object... params)	having(sql语句)	having("sum(age) > {0}", 11) ---> having sum(age) > 11
exists(String existsSql)	exists(sql语句)	exists("select id from table where age = 1") ---> exists (select id from table where age = 1)
notExists(String notExistsSql)	not exists (sql语句)	notExists("select id from table where age = 1") ---> not exists (select id from table where age = 1)
orderByAsc(R... columns)	group by 字段, ... asc	orderByAsc("id", "name") ---> order by id ASC,name asc
orderByDesc(R... columns)	group by 字段, ... desc	orderByAsc("id", "name") ---> order by id ASC,name desc
or()	or 拼接	eq("id",1).or().eq("name","张三") ---> id = 1 or name = '张三'
or(Consumer<Param> consumer)	or 嵌套	or(i -> i.eq("name", "张三").ne("id", 1)) ---> or (name = '张三' and id <> 1)
and(Consumer<Param> consumer)	and 嵌套	and(i -> i.eq("name", "张三").ne("id", 1)) ---> and (name = '张三' and id <> 1)
nested(Consumer<Param> consumer)	嵌套	nested(i -> i.eq("name", "张三").ne("id", 1)) ---> (name = '张三' and id <> 1)
apply(String applySql, Object... params)	拼接sql	apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "1970-01-01") ---> date_format(dateColumn,'%Y-%m-%d') = '1970-01-01'")

QueryWrapper常用方法

方法名	描述	等价SQL
select(String... sqlSelect)	设置要查询字段	select("id", "name") ---> select id, name
select(Predicate<TableFieldInfo> predicate)	根据条件设置要查询的字段	select(i -> !i.getColumn().equals("id") && !i.getColumn().equals("name") ---> select age

UpdateWrapper常用方法

方法名	描述	等价SQL
set(String column, Object val)	set 字段	set("name", "张三") ---> set name = '张三'
setSql(String sql)	set （sql语句）	set("name = zhangsan") ---> set name = '张三'

condition机制

Wrapper的每个方法都有一个，第一个参数是布尔类型的condition的重载方法，该机制用法如下：通常需要对参数进行空判断，可将该操作写入到condition参数中

@Test
public void testSelect() {
  String inputParameterName = "";
  String inputParameterEmail = null;

  QueryWrapper<User> queryWrapper = new QueryWrapper<>();
  //if (inputParameterName != null && !inputParameterName.equals("")) {
  //queryWrapper.like("name", inputParameterName);
  //}
  queryWrapper.like(inputParameterName != null && !inputParameterName.equals(""), "name", inputParameterName);
  //if (inputParameterEmail != null && !inputParameterEmail.equals("")) {
  //queryWrapper.like("email", inputParameterName);
  //}
  queryWrapper.like(inputParameterEmail != null && !inputParameterEmail.equals(""), "email", inputParameterName);
  List<User> userList = userMapper.selectList(queryWrapper);
  Assertions.assertEquals(5, userList.size());
}

Lambda格式Wrapper

创建方式

LambdaQueryWrapper<Student> lambda = new QueryWrapper<User>().lambda();  //QueryWrapper转化方式
LambdaQueryWrapper<Student> lambda = new LambdaQueryWrapper<>();  //直接new方式
LambdaQueryWrapper<Student> lambda = Wrappers.<Student>lambdaQuery();  //Wrappers工具类方式

使用方式

LambdaQueryWrapper<Student> lambda = new LambdaQueryWrapper<>();
lambda.eq(Student::getName, "张三");  //方法引用防止误写
List<Student> studentList = userMapper.selectList(lambda);
Assertions.assertEquals(5, studentList.size());
// 3.0.7新增写法
List<Student> userList = new LambdaQueryChainWrapper<>(studentMapper)  //studentMapper是通过@Autowired注解注入的
  .like(User::getName, "张三")
  .list();  //纯函数式编程，少了查询前缀