一、查询不显示全部列——select的使用
第一种情况——直接选定要显示的列
// SELECT id,name FROM user WHERE (name LIKE ? AND age < ?)
@Test
public void selectByWrapperSupper(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id","name").like("name","J").lt("age",20);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
第二种情况——反向设定不要显示的列（适用于要显示的列有很多的情况）
// SELECT id,email FROM user WHERE (name LIKE ? AND age < ?)
@Test
public void selectByWrapperSupper2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name","J").lt("age",20).select(User.class,
info->!info.getColumn().equals("age")&&!info.getColumn().equals("name"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
二、方法的第三个参数——condition执行条件的使用
// SELECT id,name,age,email FROM user WHERE (name LIKE ?)
@Test
public void testCondition(){
String name = "J";
String email = "";
condition(name, email);
}
private void condition(String name,String email){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// if(StringUtils.isNotBlank(name)){
// queryWrapper.like("name", name);
// }
// if(StringUtils.isNotBlank(email)){
// queryWrapper.like("email", email);
// }
// 使用第三个参数condition参数来控制条件后
queryWrapper.like(StringUtils.isNotBlank(name),"name",name)
.like(StringUtils.isNotBlank(email), "email", email);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
三、当使用实体作为条件构造器的构造参数时
注1：实体作为构造参数和条件构造器单独设置的条件同时存在，所以注意不要设置重复了。
// SELECT id,name,age,email FROM user WHERE name=? AND age=? AND (name LIKE ? AND age < ?)
@Test
public void selectByWrapperEntity(){
User user = new User();
user.setName("Jack");
user.setAge(20);
QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
queryWrapper.like("name","J").lt("age",20);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
注2：实体作为构造参数传入后如果想做模糊查询，只需要在实体上对应字段添加注解
@TableField(condition = SqlCondition.LIKE)
private String name;
注3：%s表示字段，#{%s}表示字段对应的值，自定义一个条件
// SELECT id,name,age,email FROM user WHERE name LIKE CONCAT('%',?,'%') AND age<? AND (name LIKE ? AND age < ?)
@TableField(condition = "%s<#{%s}")
private int age;
四、allEq用法
第一种情况：某个条件的值为null时，查出来的sql就是is null
// SELECT id,name,age,email FROM user WHERE (name = ? AND age IS NULL)
@Test
public void selectByWrapperAllEq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> params = new HashMap<>();
params.put("name","Jack");
params.put("age",null);
queryWrapper.allEq(params);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
第二种情况：某个条件的值为null，allEq方法添加第二个参数为false时，查出来的sql忽略值为null的条件
// SELECT id,name,age,email FROM user WHERE (name = ?)
@Test
public void selectByWrapperAllEq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> params = new HashMap<>();
params.put("name","Jack");
params.put("age",null);
queryWrapper.allEq(params,false);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
第三种情况：allEq方法中添加过滤条件参数，只保留符合条件的参数作为条件
// SELECT id,name,age,email FROM user WHERE (age IS NULL)
@Test
public void selectByWrapperAllEq2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> params = new HashMap<>();
params.put("name","Jack");
params.put("age",null);
queryWrapper.allEq((k,v)->!k.equals("name"),params);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
第四种情况：在上面第三种情况基础上再添加第三个布尔条件为false，忽略值为null的条件
// SELECT id,name,age,email FROM user
@Test
public void selectByWrapperAllEq2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> params = new HashMap<>();
params.put("name","Jack");
params.put("age",null);
queryWrapper.allEq((k,v)->!k.equals("name"),params,false);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
第五种情况：在上面基础上添加condition执行条件
// SELECT id,name,age,email FROM user WHERE (name = ?)
@Test
public void selectByWrapperAllEq2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> params = new HashMap<>();
params.put("name","Jack");
params.put("age",null);
queryWrapper.allEq(StringUtils.isNotBlank((String)params.get("name")),(k,v)->!k.equals("age"),params,false);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
