MybatisPlus：实现复杂SQL语句拼接

(m or n) and x and y

1. SQL

   SELECT id,org_code,service_name FROM pay_account WHERE ((org_code = '123456' OR org_code = 'CLEAR') AND pay_channel = 'WxPay' AND account_type = 2)
   

2. Mybatis Plus

   queryWrapper.select(PayAccount::getId, PayAccount::getOrgCode, PayAccount::getServiceName)
                   .and(e -> e.eq(PayAccount::getOrgCode, orgCode).or()
                           .eq(PayAccount::getOrgCode, BsConstant.JD_PAY_SERVICE_NAME))
                   .eq(PayAccount::getPayChannel, ChannelCodeEnum.WxPay.getCode())
                   .eq(PayAccount::getAccountType, PayAccountTypeEnum.SERVICE.getCode());
   

(m or n) and (x and y)

1. SQL

   SELECT id,org_code,service_name FROM pay_account WHERE ((org_code = '123456' OR org_code = 'CLEAR') AND (pay_channel = 'WxPay' AND account_type = 2))
   

2. Mybatis Plus

   queryWrapper.select(PayAccount::getId, PayAccount::getOrgCode, PayAccount::getServiceName)
                   .and(e -> e.eq(PayAccount::getOrgCode, orgCode).or()
                           .eq(PayAccount::getOrgCode, BsConstant.JD_PAY_SERVICE_NAME))
                   .and(e -> e.eq(PayAccount::getPayChannel, ChannelCodeEnum.WxPay.getCode())
                           .eq(PayAccount::getAccountType, PayAccountTypeEnum.SERVICE.getCode()));
   

(m and n) or ( x and y)

1. SQL

   SELECT * FROM api_info 
   WHERE ((org_id = 1 AND api_category_id = 1) OR (api_category_id = 1 AND is_standard = 1)) ;
   
   -- 示例二
   SELECT * FROM sys_dict d WHERE (d.type_code = 'PATIENT_SRC' AND d.org_code = '12328') 
   OR (d.type_code = 'PATIENT_SRC' AND d.org_code IS NULL);
   

2. Mybatis Plus

   QueryWrapper<ApiInfo> wrapper = new QueryWrapper<>();
   wrapper.lambda().select(ApiInfo::getId, ApiInfo::getAlias, ApiInfo::getName, ApiInfo::getApiDesc, ApiInfo::getUpdatedTime)
   .and(e -> e.eq(ApiInfo::getOrgId, apiInfo.getOrgId()).eq(ApiInfo::getApiCategoryId, apiInfo.getApiCategoryId()))
   .or(e -> e.eq(ApiInfo::getApiCategoryId, apiInfo.getApiCategoryId()).eq(ApiInfo::getIsStandard, true));
   
   # 示例二
   queryWrapper.and(e -> e.eq(SysDict::getTypeCode, typeCode).isNull(SysDict::getOrgCode))
                .or(e -> e.eq(SysDict::getTypeCode, typeCode).eq(SysDict::getOrgCode, orgCode))
                .orderByAsc(SysDict::getOrderNo);
   

(m and n ) or (m and x)

(m and n ) or (m and x) 等同于 m and (n or x)

1. SQL

    SELECT * FROM sys_dict WHERE (`status` = 1 AND (type_code = 'SURVEY_CHANNEL' AND org_code IS NULL) OR (type_code = 'SURVEY_CHANNEL' AND org_code = '12328')) ORDER BY order_no ASC;
   
    SELECT * FROM sys_dict WHERE (`status` = 1 AND type_code = 'SURVEY_CHANNEL' AND (org_code = '12328' OR org_code IS NULL)) ORDER BY order_no ASC;
   

2. Mybatis Plus

    LambdaQueryWrapper<SysDict> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(SysDict::getStatus, SysConstants.ENABLE).and(e -> e.eq(SysDict::getTypeCode, typeCode)
                .isNull(SysDict::getOrgCode)).or(e -> e.eq(SysDict::getTypeCode, typeCode)
                .eq(SysDict::getOrgCode, orgCode)).orderByAsc(SysDict::getOrderNo);
   
        queryWrapper.eq(SysDict::getStatus, SysConstants.ENABLE).eq(SysDict::getTypeCode, typeCode)
                .and(e -> e.eq(SysDict::getOrgCode, orgCode).or().isNull(SysDict::getOrgCode))
                .orderByAsc(SysDict::getOrderNo);
   

m and ( x or y) and (n)

1. SQL

   SELECT * FROM api_category 
   WHERE del_flag = 0 and  ((org_id = 1 OR org_id IS NULL) AND (belong_type = 1))
   

2. Mybatis Plus

   queryWrapper.and(wrapper -> wrapper.eq(ApiCategory::getOrgId, apiCategory.getOrgId()).or().isNull(ApiCategory::getOrgId))
                       .and(wrapper -> wrapper.eq(ApiCategory::getBelongType, ApiBelongTypeEnum.COMMON.getType()));