03.条件构造器
条件构造器
public void test(){
EntityWrapper ew=new EntityWrapper();
ew.setEntity(new User());
String name="wang";
Integer age=16;
ew.where("name = {0}",name).andNew("age > {0}",age).orderBy("age");
List<User> list = userService.selectList(ew);
Page page2 = userService.selectPage(page, ew);
}
}
这里使用了一个条件包装类 EntityWrapper，来进行对 SQL 语句的拼装，原理也很好理解：上面的代码中，第一个 list 查询的结果就是查询数据库中 name=wang 并且 age > 16 岁的所有记录并按照 age 排序，而第二个查询就是再多加一个分页的功能。基本上来说,使用 EntityWrapper 可以简单地完成一些条件查询，但如果查询方法使用频率很高的话还是建议自己写在 UserMapper 里。
对于 EntityMapper 的条件拼接，基本可以实现 SQL 中常用的 where,and,or,groupby,orderby 等语法，具体构建方法可以灵活组合。
@Test
public void testTSQL11() {
/*
* 实体带查询使用方法 输出看结果
*/
ew.setEntity(new User(1));
ew.where("name={0}", "zhangsan").and("id=1")
.orNew("status={0}", "0").or("status=1")
.notLike("nlike", "notvalue")
.andNew("new=xx").like("hhh", "ddd")
.andNew("pwd=11").isNotNull("n1,n2").isNull("n3")
.groupBy("x1").groupBy("x2,x3")
.having("x1=11").having("x3=433")
.orderBy("dd").orderBy("d1,d2");
System.out.println(ew.getSqlSegment());
}
AbstractWrapper
QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类，用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件。注意: entity 生成的 where 条件与 使用各个 api 生成的 where 条件没有任何关联行为。
QueryWrapper
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取。
select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)
UpdateWrapper
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件 及 LambdaUpdateWrapper, 可以通过 new UpdateWrapper().lambda() 方法获取。
使用 Wrapper 自定义 SQL
- 使用注解
@Select("select * from mysql_data ${ew.customSqlSegment}")
List<MysqlData> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);
- 使用 XML
List<MysqlData> getAll(Wrapper ew);
<select id="getAll" resultType="MysqlData">
SELECT * FROM mysql_data ${ew.customSqlSegment}
</select>
案例：使用 Wrapper
@RunWith(SpringRunner.class)
@SpringBootTest
public class WrapperTest {
@Resource
private UserMapper userMapper;
@Resource
private RoleMapper roleMapper;
@Test
public void tests() {
System.out.println("----- 普通查询 ------");
List<User> plainUsers = userMapper.selectList(new QueryWrapper<User>().eq("role_id", 2L));
List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
Assert.assertEquals(plainUsers.size(), lambdaUsers.size());
print(plainUsers);
System.out.println("----- 带子查询(sql注入) ------");
List<User> plainUsers2 = userMapper.selectList(new QueryWrapper<User>()
.inSql("role_id", "select id from role where id = 2"));
List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
.inSql(User::getRoleId, "select id from role where id = 2"));
Assert.assertEquals(plainUsers2.size(), lambdaUsers2.size());
print(plainUsers2);
System.out.println("----- 带嵌套查询 ------");
List<User> plainUsers3 = userMapper.selectList(new QueryWrapper<User>()
.nested(i -> i.eq("role_id", 2L).or().eq("role_id", 3L))
.and(i -> i.ge("age", 20)));
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
Assert.assertEquals(plainUsers3.size(), lambdaUsers3.size());
print(plainUsers3);
System.out.println("----- 自定义(sql注入) ------");
// 方式一
List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
.apply("role_id = 2"));
/* List<User> lambdaUsers4 = userMapper.selectList(new QueryWrapper<User>().lambda()
.apply("role_id = 2"));*/
// 方式二
List<User> plainUsers5 = userMapper.selectList(new QueryWrapper<User>()
.apply("role_id = {0}",2));
/* List<User> lambdaUsers5 = userMapper.selectList(new QueryWrapper<User>().lambda()
.apply("role_id = {0}",2));*/
print(plainUsers4);
Assert.assertEquals(plainUsers4.size(), plainUsers5.size());
UpdateWrapper<User> uw = new UpdateWrapper<>();
uw.set("email", null);
uw.eq("id", 4);
userMapper.update(new User(), uw);
User u4 = userMapper.selectById(4);
Assert.assertNull(u4.getEmail());
}
@Test
public void lambdaQueryWrapper() {
System.out.println("----- 普通查询 ------");
List<User> plainUsers = userMapper.selectList(new LambdaQueryWrapper<User>().eq(User::getRoleId, 2L));
List<User> lambdaUsers = userMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getRoleId, 2L));
Assert.assertEquals(plainUsers.size(), lambdaUsers.size());
print(plainUsers);
System.out.println("----- 带子查询(sql注入) ------");
List<User> plainUsers2 = userMapper.selectList(new LambdaQueryWrapper<User>()
.inSql(User::getRoleId, "select id from role where id = 2"));
List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
.inSql(User::getRoleId, "select id from role where id = 2"));
Assert.assertEquals(plainUsers2.size(), lambdaUsers2.size());
print(plainUsers2);
System.out.println("----- 带嵌套查询 ------");
List<User> plainUsers3 = userMapper.selectList(new LambdaQueryWrapper<User>()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
Assert.assertEquals(plainUsers3.size(), lambdaUsers3.size());
print(plainUsers3);
System.out.println("----- 自定义(sql注入) ------");
List<User> plainUsers4 = userMapper.selectList(new QueryWrapper<User>()
.apply("role_id = 2"));
print(plainUsers4);
UpdateWrapper<User> uw = new UpdateWrapper<>();
uw.set("email", null);
uw.eq("id", 4);
userMapper.update(new User(), uw);
User u4 = userMapper.selectById(4);
Assert.assertNull(u4.getEmail());
}
private <T> void print(List<T> list) {
if (!CollectionUtils.isEmpty(list)) {
list.forEach(System.out::println);
}
}
/**
* SELECT id,name,age,email,role_id FROM user
* WHERE ( 1 = 1 ) AND ( ( name = ? AND age = ? ) OR ( name = ? AND age = ? ) )
*/
@Test
public void testSql() {
QueryWrapper<User> w = new QueryWrapper<>();
w.and(i -> i.eq("1", 1))
.nested(i ->
i.and(j -> j.eq("name", "a").eq("age", 2))
.or(j -> j.eq("name", "b").eq("age", 2)));
userMapper.selectList(w);
}
/**
* SELECT id,name FROM user
* WHERE (age BETWEEN ? AND ?) ORDER BY role_id ASC,id ASC
*/
@Test
public void testSelect() {
QueryWrapper<User> qw = new QueryWrapper<>();
qw.select("id","name").between("age",20,25)
.orderByAsc("role_id","id");
List<User> plainUsers = userMapper.selectList(qw);
LambdaQueryWrapper<User> lwq = new LambdaQueryWrapper<>();
lwq.select(User::getId,User::getName).between(User::getAge,20,25)
.orderByAsc(User::getRoleId,User::getId);
List<User> lambdaUsers = userMapper.selectList(lwq);
print(plainUsers);
Assert.assertEquals(plainUsers.size(), lambdaUsers.size());
}
}
- Update 操作
@RunWith(SpringRunner.class)
@SpringBootTest
public class UpdateWrapperTest {
@Autowired
private UserMapper userMapper;
/**
* UPDATE user SET age=?, email=? WHERE (name = ?)
*/
@Test
public void tests() {
//方式一：
User user = new User();
user.setAge(29);
user.setEmail("test3update@baomidou.com");
userMapper.update(user,new UpdateWrapper<User>().eq("name","Tom"));
//方式二：
//不创建User对象
userMapper.update(null,new UpdateWrapper<User>()
.set("age",29).set("email","test3update@baomidou.com").eq("name","Tom"));
}
/**
* 使用lambda条件构造器
* UPDATE user SET age=?, email=? WHERE (name = ?)
*/
@Test
public void testLambda() {
//方式一：
User user = new User();
user.setAge(29);
user.setEmail("test3update@baomidou.com");
userMapper.update(user,new LambdaUpdateWrapper<User>().eq(User::getName,"Tom"));
//方式二：
//不创建User对象
userMapper.update(null,new LambdaUpdateWrapper<User>()
.set(User::getAge,29).set(User::getEmail,"test3update@baomidou.com").eq(User::getName,"Tom"));
}
}