一、创建添加构造器的两种方式
//1
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//2
QueryWrapper<User> query = Wrappers.<User>query();
二、注意点
- 条件构造器的方法中，参数条件大多使用的是数据库的字段名，而不是变量名；
- apply、inSql方法的使用（见下方selectByWrapper4方法）；
- and、or、nested、in方法的使用（见下方selectByWrapper5、6、7、8方法），sql语句中and的优先级>or的优先级，写多个queryWraper等同于中间and连接；
- last方法：无视优化规则直接拼接到sql的最后，只能调用一次，多次调用以最后一次调用为准，有sql注入风险，谨慎使用；（见下方selectByWrapper9）
三、案例
/**
* 测试BaseMapper中的List<T> selectList(@Param("ew") Wrapper<T> queryWrapper);方法
*/
@Test
public void selectByWrapper(){
//创建添加构造器有两种方式
//1
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//2
//QueryWrapper<User> query = Wrappers.<User>query();
queryWrapper.like("name", "Tom").lt("age",40);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
@Test
public void selectByWrapper2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "o").between("age", 18, 30).isNotNull("email");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
@Test
public void selectByWrapper3(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "J").or().ge("age", 18).orderByDesc("age").orderByAsc("id");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
// date_format(create_time,'%Y-%m-%d')='2020-10-03' and id in (select id from user where name like 'J%');
@Test
public void selectByWrapper4(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2020-10-03")
// sql注入风险or true or true
// queryWrapper.apply("date_format(create_time,'%Y-%m-%d')= '2020-10-03' or true or true")
.inSql("id", "select id from user where name like 'J%'");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
@Test
public void selectByWrapper5(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "J").and(wq->wq.lt("age",20).or().isNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
// name LIKE ? OR (age BETWEEN ? AND ? AND email IS NOT NULL)
// name LIKE ? OR email IS NOT NULL AND age <= ?
@Test
public void selectByWrapper6(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "J").or(wq->wq.between(true, "age", 18, 22).isNotNull("email"));
// queryWrapper.likeRight("name", "J").or().isNotNull("email").le("age",18);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
// (age < ? OR email IS NOT NULL) AND name LIKE ?
@Test
public void selectByWrapper7(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(wq->wq.lt("age", 20).or().isNotNull("email")).likeRight("name", "J");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
// age IN (?,?,?) AND age IN (?,?)
@Test
public void selectByWrapper8(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", 20,28,21);
queryWrapper.in("age", Arrays.asList(24,20));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
// last,仅保留最后n条，谨慎使用，有sql注入风险
// (age IN (?,?,?,?)) ORDER BY id DESC limit 3
@Test
public void selectByWrapper9(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(21,18,24,20)).orderByDesc("id").last("limit 3");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
