各种条件查询
@RunWith(SpringRunner.class)
@EnableAutoConfiguration(exclude = WebMvcAutoConfiguration.class)
@SpringBootTest(classes = SdkApiApplication.class)
public class MybatisPlusDemoApplicationTests {
@Resource
private UserMapper userMapper;
@Test
public void select(){
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
@Test
public void insert(){
User user = new User();
user.setAge(31);
user.setManagerId(1088250446457389058L);
user.setCreateTime(LocalDateTime.now());
int insert = userMapper.insert(user);
System.out.println("影像记录数:"+insert);
}
/**
* 查询名字中包含'雨'并且年龄小于40
* where name like '%雨%' and age < 40
*/
@Test
public void selectByWrapper(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name","雨").lt("age",40);
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 创建日期为2019年2月14日并且直属上级姓名为王姓
* date_format(create_time,'%Y-%m-%d') and manager_id in (select id from user where name like '王%')
*/
@Test
public void selectByWrapper2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-02-14")
.inSql("manager_id","select id from user where name like '王%'");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* and & or 名字为王姓,(年龄小于40或者邮箱不为空)
*/
@Test
public void selectByWrapper3(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name","王").and(e ->e.lt("age",40).or().isNotNull("email"));
// queryWrapper.likeRight("name","王").and(wq-> wq.lt("age",40).or().isNotNull("email"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* between & and 名字为王姓,(年龄小于40,并且年龄大于20,并且邮箱不为空)
*/
@Test
public void selectWrapper4(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.likeRight("name", "王").and(wq -> wq.between("age", 20, 40).and(wqq -> wqq.isNotNull("email")));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* nested (年龄小于40或者邮箱不为空)并且名字为王姓
* (age<40 or email is not null)and name like '王%'
*/
@Test
public void selectWrapper5(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email")).likeRight("name","王");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* in 年龄为30,31,35,34的员工
*/
@Test
public void selectWrapper6(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30,31,34,35));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* * 无视优化规则直接拼接到 sql 的最后(有sql注入的风险,请谨慎使用)只返回满足条件的一条语句即可
* limit 1
*/
@Test
public void selectWrapper7(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("age", Arrays.asList(30,31,34,35)).last("limit 1");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 查找为王姓的员工的姓名和年龄
*/
@Test
public void selectWrapper8(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("name","age").likeRight("name","王");
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
/**
* 查询所有员工信息除了创建时间和员工ID列
*/
@Test
public void selectWrapper9(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select(User.class,info->!info.getColumn().equals("create_time")
&&!info.getColumn().equals("manager_id"));
List<User> userList = userMapper.selectList(queryWrapper);
userList.forEach(System.out::println);
}
}