2.MyBatis-Plus条件构造器
1.QueryWrapper
1.查找薪水大于3000 且名字里有“小”的 员工
对应sql语句:select * from t_employee where salary>3000 and name like ‘%小%’
@Test
public void selectByQueryWrapper1(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
// QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
queryWrapper.gt("salary",3000).like("name","小");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
2.查找薪水范围3000-5000,名字里有“小”以及email不为空的员工
对应sql语句:select * from t_employee where between 3000 and 5000 and name like ‘%小%’ and email is not null
@Test
public void selectByQueryWrapper2(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
// QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
queryWrapper.like("name","小").between("salary",3000,5000).isNotNull("email");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
3.查询姓李的,并且出生日期范围是1993-02-09到1994-04-09的员工
对应sql语句:SELECT * FROM t_employee WHERE DATE_FORMAT(birthday,‘%Y-%m-%d’)>=‘1993-02-09’ AND DATE_FORMAT(birthday,‘%Y-%m-%d’)<=‘1994-04-09’ AND NAME LIKE ‘李%’
@Test
public void selectByQueryWrapper3(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.apply("DATE_FORMAT(birthday,'%Y-%m-%d')>={0} and DATE_FORMAT(birthday,'%Y-%m-%d')<={1}","1993-02-09","1994-04-09").likeRight("name","李");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
4.查询姓李的或者邮箱不为空并且是女性的员工
对应sql语句:SELECT * FROM t_employee WHERE NAME LIKE ‘李%’ OR (email IS NOT NULL AND gender =‘女’)
@Test
public void selectByQueryWrapper4(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.likeRight("name","李").or(wq->wq.isNotNull("email").eq("gender","女"));
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
5.查询姓李的并且邮箱不为空或者是女性的员工
对应sql语句:SELECT * FROM t_employee WHERE NAME LIKE ‘李%’ AND (email IS NOT NULL OR gender =‘女’)
@Test
public void selectByQueryWrapper5(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
// QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
queryWrapper.likeRight("name","李").and(wq->wq.isNotNull("email").or().eq("gender","女"));
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
6.查询属于编号1,2,3部门的并且薪水小于等于5500的员工 根据年龄从大到小排序显示
对应sql语句:SELECT * FROM t_employee WHERE salary<=5500 AND department_id IN (1,2,3) ORDER BY birthday ASC
@Test
public void selectByQueryWrapper6(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper<>();
List<Integer> idsList = new ArrayList<>();
idsList.add(1);
idsList.add(2);
idsList.add(3);
queryWrapper.le("salary",5500).in("departmentId",idsList).orderByAsc("birthday");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
7.查找薪水大于3000 名字里有“小”的 员工(只显示编号和姓名)
对用sql语句:select * from t_employee where salary>3000 and name like ‘%小%’
@Test
public void selectByQueryWrapper7(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.select("id","name").gt("salary",3000).like("name","小");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
8.查找薪水大于3000 名字里有“小”的 员工(排除出生日期和性别)
对应sql语句:select * from t_employee where salary>3000 and name like ‘%小%’
@Test
public void selectByQueryWrapper8(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.select(Employee.class,info->!info.getColumn().equals("birthday")&&!info.getColumn().equals("gender"))
.gt("salary",3000).like("name","小");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
9.查询每个部门的平均薪资
对应sql语句:select departmentId,avg(salary) as avg_salary from t_employee group by departmentId
@Test
public void selectByQueryWrapper9(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.select("departmentId","avg(salary) as avg_salary")
.groupBy("departmentId");
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
}
10.返回map类型数据
在9.查询每个部门的平均薪资中,返回的数据类型为List<Employee>
这样就会导致返回的对象中含有很多空数据。
使用map可以避免返回这些空数据
@Test
public void selectByQueryWrapperReturnMap(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.
select("departmentId","avg(salary) as avg_salary")
.groupBy("departmentId");
List<Map<String, Object>> map = employeeMapper.selectMaps(queryWrapper);
System.out.println(map);
}
不使用map返回
使用map返回
11.返回查询总记录数
@Test
public void selectByQueryWrapper11(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
queryWrapper.gt("salary",3000).like("name","小");
Integer count = employeeMapper.selectCount(queryWrapper);
System.out.println(count);
}
附.QueryWrapper常用方法
2.UpdateWrapper
1.更新指定员工的邮箱和联系电话
对应sql语句:UPDATE t_employee SET email=“111111@qq.com”,phoneNumber = “65432112341” WHERE id = 6
@Test
public void updateByUpdateWrapper1(){
UpdateWrapper<Employee> updateWrapper=new UpdateWrapper<>();
Employee employee = new Employee();
employee.setEmail("ceshi@qq.com");
employee.setPhoneNumber("66662112341");
updateWrapper.eq("id",6);
int affectRow = employeeMapper.update(employee,updateWrapper);
if (affectRow>0){
System.out.println("插入成功");
}
else{
System.out.println("插入失败");
}
}
2.删除市场部老员工
对应sql语句:DELETE FROM t_employee WHERE DATE_FORMAT(birthday,‘%Y-%m-%d’)<=“1990-01-01” AND departmentId = 2
@Test
public void deleteByUpdateWrapper1(){
UpdateWrapper updateWrapper=new UpdateWrapper<>();
updateWrapper.apply("DATE_FORMAT(birthday,'%Y-%m-%d')<={0}","1990-01-01").eq("departmentId",2);
int affectRow = employeeMapper.delete(updateWrapper);
if (affectRow>0){
System.out.println("删除成功");
}
else{
System.out.println("删除失败");
}
}
3.condition
condition主要是为了简化组装条件
未使用condition:
@Test
public void selectByQueryWrapperWithoutCondition(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
// QueryWrapper<Employee> queryWrapper2=Wrappers.<Employee>query();
String s_gender="";
String s_name="小";
if(StringUtils.isNotEmpty(s_gender)){
queryWrapper.eq("gender",s_gender);
}
if(StringUtils.isNotEmpty(s_name)){
queryWrapper.like("name",s_name);
}
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
使用condition
@Test
public void selectByQueryWrapperWithCondition(){
QueryWrapper<Employee> queryWrapper=new QueryWrapper();
String s_gender="";
String s_name="小";
queryWrapper.eq(StringUtils.isNotEmpty(s_gender),"gender",s_gender).like(StringUtils.isNotEmpty(s_name),"name",s_name);
List<Employee> employeeList = employeeMapper.selectList(queryWrapper);
System.out.println(employeeList);
}
4.lambda条件构造器
QueryWrapper
@Test
public void selectByQueryWrapperWithLambda(){
LambdaQueryWrapper<Employee> lambdaQueryWrapper=new QueryWrapper<Employee>().lambda();
// LambdaQueryWrapper<Employee> lambdaQueryWrapper=new LambdaQueryWrapper<>();
// LambdaQueryWrapper<Employee> lambdaQueryWrapper = Wrappers.lambdaQuery();
lambdaQueryWrapper.gt(Employee::getSalary,"3500")
.like(Employee::getName,"小");
List<Employee> employeeList = employeeMapper.selectList(lambdaQueryWrapper);
System.out.println(employeeList);
}
@Test
public void selectByQueryWrapperWithLambdaChain(){
List<Employee> employeeList = new LambdaQueryChainWrapper<>(employeeMapper)
.gt(Employee::getSalary,"3500")
.like(Employee::getName,"小")
.list();
System.out.println(employeeList);
}
UpdateWrapper
@Test
public void updateByUpdateWrapperLambda(){
LambdaUpdateWrapper<Employee> updateWrapper=new LambdaUpdateWrapper<>();
updateWrapper.eq(Employee::getId,6);
updateWrapper.set(Employee::getEmail,"hahahha@qq.com").set(Employee::getPhoneNumber,"15915915915");
int affectRow = employeeMapper.update(null,updateWrapper);//这里设置要修改的字段不再需要用到实体类对象,所以直接写null
if (affectRow>0){
System.out.println("更新成功");
}
else{
System.out.println("更新失败");
}
}
参考资料
1.https://www.bilibili.com/video/BV1KV411U7pH?p=1&spm_id_from=pageDriver
2.https://www.cnblogs.com/yuyueq/p/14639016.html
3.https://www.bilibili.com/video/BV12R4y157Be?p=1