MyBatisPlus：多条件复杂SQL的写法实现

使用MyBatisPlus实现多条件复杂SQL查询

Mybatis Plus 实现下面 SQL 语句的写法：

(m or n) and x and y

1. SQL

   SELECT * FROM pay_account WHERE ((org_code = '123456' OR org_code = 'companyA') AND pay_channel = 'WxPay' AND type = 2)

2. Mybatis Plus

   queryWrapper.and(wrapper -> wrapper.eq(PayAccount::getOrgCode, "123456")
                   .or()
                   .eq(PayAccount::getOrgCode, "companyA"))
           .eq(PayAccount::getPayChannel, "WxPay")
           .eq(PayAccount::getType, 2);

(m or n) and (x and y)

1. SQL

   SELECT * FROM article WHERE ((org_code = '123456' OR org_code = 'companyA') AND (belong = 'WxPay' AND 
   top = 2))

2. Mybatis Plus

   queryWrapper.and(wrapper -> wrapper.eq(Article::getOrgCode, "123456")
                   .or()
                   .eq(Article::getOrgCode, "companyA"))
           .and(wrapper -> wrapper.eq(Article::getBelong, "WxPay")
                   .eq(Article::getTop, 2));

(m and n) or ( x and y)

1. SQL

   SELECT * FROM article WHERE ((org_code = '123456' AND org_code = 'companyA') OR (belong = 'WxPay' AND top = 2))

2. Mybatis Plus

   queryWrapper.and(wrapper -> wrapper.eq(Article::getOrgCode, "123456")
                   .eq(Article::getOrgCode, "companyA"))
           .or(wrapper -> wrapper.eq(Article::getBelong, "WxPay")
                   .eq(Article::getTop, 2));

m and ( x or y) and (n)

1. SQL

   SELECT * FROM article WHERE (org_code = '123456' AND (org_code = 'companyA' OR id IS NULL) AND (belong = 'WxPay'))

2. Mybatis Plus

   queryWrapper.eq(Article::getOrgCode, "123456")
           .and(wrapper -> wrapper.eq(Article::getOrgCode, "companyA")
                   .or()
                   .isNull(Article::getId))
           .and(wrapper -> wrapper.eq(Article::getBelong, "WxPay"));

((m and n) or (x and y)) and z

1. SQL

   SELECT id,status FROM article WHERE (((require_audit = ? AND status = ?) OR (status = ? AND audit_status = ?)) AND published_time <= ?) limit 100

2. Mybatis Plus

   queryWrapper.and(e -> e.and(g -> g.eq(Article::getRequireAudit, false)
                           .eq(Article::getStatus, "wait"))
                   .or(f -> f.eq(Article::getStatus, "published")
                           .eq(Article::getAuditStatus, "pass"))
           ).le(Article::getPublishedTime, current);

(m and ((n) or (x and y in()))) OR()

1. SQL

   SELECT * FROM org_emp 
   WHERE emp_duty = 1 AND ( org_code = '1234' AND (( dept_code = '1118' ) 
   		OR (
   			dept_code = '53003' 
   		AND emp_code IN ( '54001', '54002' ))))
   
   OR (org_code = '5678' AND (( dept_code = '870001' ) 
   		OR (
   			dept_code = '870002' 
   		AND emp_code IN ( '20111', '20112' ))));

2. MyBatis-Plus

   private void addPermission(LambdaQueryWrapper<OrgEmp> queryWrapper, List<BaseDataPermissionTreeVo> permissionList) {
       List<String> orgCodes = permissionList.stream().map(BaseDataPermissionTreeVo::getOrgCode).collect(Collectors.toList());
       queryWrapper.and(orgWrapper -> {
           // 遍历机构
           orgCodes.forEach(orgCode -> {
               // 机构之间是or
               orgWrapper.or(owr -> {
                   owr.eq(OrgEmp::getOrgCode, orgCode);
                   List<BaseDataPermissionTreeVo> deptPermission = permissionList.stream().filter(p -> StringUtil.equals(p.getOrgCode(), orgCode)).collect(Collectors.toList());
                   BaseDataPermissionTreeVo baseDataPermissionTreeVo = deptPermission.get(0);
                   List<DeptEmpPermissionTree> deptEmpList = baseDataPermissionTreeVo.getDeptEmpList();
                   if (CollectionUtils.isNotEmpty(deptEmpList)) {
                       // 机构与科室 and
                       owr.and(dwr -> {
                           // 遍历科室
                           deptEmpList.forEach(deptEmp -> {
                               // 科室之间or
                               dwr.or(dwr1 -> {
                                   // 科室条件 and
                                   dwr1.eq(OrgEmp::getDeptCode, deptEmp.getDeptCode());
                                   List<DeptEmpPermissionTree> deptEmps = deptEmpList.stream().filter(m -> StringUtil.equals(deptEmp.getDeptCode(), m.getDeptCode()))
                                       .collect(Collectors.toList());
                                   DeptEmpPermissionTree deptEmpPermissionTree = deptEmps.get(0);
                                   List<String> empCodeList = deptEmpPermissionTree.getEmpCodeList();
                                   if (CollectionUtils.isNotEmpty(empCodeList)) {
                                       // 科室雇员 in
                                       dwr1.in(OrgEmp::getEmpCode, empCodeList);
                                   }
                               });
                           });
                       });
   
                   }
               });
           });
       });
   }