例如,前面示意图中的 nickname 和 description 字段,假设我们是一个婚恋网站,用户在筛选其他用户的时候,主要是用 age 和 sex 两个字段进行查询,而 nickname 和 description 两个字段主要用于展示,一般不会在业务查询中用到。description 本身又比较长,因此我们可以将这两个字段独立到另外一张表中,这样在查询 age 和 sex 时,就能带来一定的性能提升。
UPDATE t_user SET is_deleted=1 WHERE id=? AND is_deleted=0
测试查询功能,被逻辑删除的数据默认不会被查询
SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0
五、条件构造器和常用接口
1. wrapper介绍
Wrapper : 条件构造抽象类,最顶端父类
AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
QueryWrapper : 查询条件封装
UpdateWrapper : Update 条件封装
AbstractLambdaWrapper : 使用Lambda 语法
LambdaQueryWrapper :用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper : Lambda 更新封装Wrapper
2. QueryWrapper
2.1 组装查询条件
@Testpublicvoidtest01(){//查询用户名包含a,年龄在20到30之间,并且邮箱不为null的用户信息 //SELECT id,username AS name,age,email,is_deleted FROM t_user //WHERE is_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
QueryWrapper<User> queryWrapper =newQueryWrapper<>();
queryWrapper.like("username","a").between("age",20,30).isNotNull("email");
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);}
2.2 组装排序条件
@Testpublicvoidtest02(){//按年龄降序查询用户,如果年龄相同则按id升序排列 //SELECT id,username AS name,age,email,is_deleted FROM t_user //WHERE is_deleted=0 ORDER BY age DESC,id ASC QueryWrapper<User>
queryWrapper =newQueryWrapper<>();
queryWrapper
.orderByDesc("age").orderByAsc("id");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);}
2.3 组装删除条件
@Testpublicvoidtest03(){//删除email为空的用户 //DELETE FROM t_user WHERE (email IS NULL)
QueryWrapper<User> queryWrapper =newQueryWrapper<>();
queryWrapper.isNull("email");//条件构造器也可以构建删除语句的条件 int result = userMapper.delete(queryWrapper);
System.out.println("受影响的行数:"+ result);}
2.4 条件的优先级
@Testpublicvoidtest04(){
QueryWrapper<User> queryWrapper =newQueryWrapper<>();//将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改 //UPDATE t_user SET age=?, email=? //WHERE (username LIKE ? AND age > ? OR email IS NULL)
queryWrapper
.like("username","a").gt("age",20).or().isNull("email");
User user =newUser();
user.setAge(18);
user.setEmail("user@gmail.com");int result = userMapper.update(user, queryWrapper);
System.out.println("受影响的行数:"+ result);}
@Testpublicvoidtest04(){
QueryWrapper<User> queryWrapper =newQueryWrapper<>();//将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息修改 //UPDATE t_user SET age=?, email=? //WHERE (username LIKE ? AND (age > ? OR email IS NULL)) //lambda表达式内的逻辑优先运算
queryWrapper
.like("username","a").and(i -> i.gt("age",20).or().isNull("email"));
User user =newUser();
user.setAge(18);
user.setEmail("user@gmail.com");int result = userMapper.update(user, queryWrapper);
System.out.println("受影响的行数:"+ result);}
@Testpublicvoidtest06(){//查询id小于等于3的用户信息 //SELECT id,username AS name,age,email,is_deleted FROM t_user //WHERE (id IN (select id from t_user where id <= 3))
QueryWrapper<User> queryWrapper =newQueryWrapper<>();
queryWrapper.inSql("id","select id from t_user where id <= 3");
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);}
3. UpdateWrapper
@Testpublicvoidtest07(){//将(年龄大于20或邮箱为null)并且用户名中包含有a的用户信息修改 //组装set子句以及修改条件
UpdateWrapper<User> updateWrapper =newUpdateWrapper<>();//lambda表达式内的逻辑优先运算
updateWrapper
.set("age",18).set("email","user@alex.com").like("username","a").and(i -> i.gt("age",20).or().isNull("email"));//这里必须要创建User对象,否则无法应用自动填充。如果没有自动填充,可以设置为null //UPDATE t_user SET username=?, age=?,email=? //WHERE (username LIKE ? AND (age > ? OR email IS NULL)) //User user = new User(); //user.setName("张三");//int result = userMapper.update(user, updateWrapper); //UPDATE t_user SET age=?,email=? WHERE (username LIKE ? AND (age > ? OR email IS NULL)) int result = userMapper.update(null, updateWrapper);
System.out.println(result);}