【mybatis-plus的LambdaQueryWrapper中and、or的用法】在 for循环中使用 or

2023-08-18 23:00

短信预约 -IT技能 免费直播动态提醒

【LambdaQueryWrapper的or用法】

错误写法一：

//【 错误写法1: 打印的sql中or拼接没有加括号:SELECT COUNT( * ) FROM pm_project WHERE `del_flag` = 0 AND// ( id = 287 AND `project_status` = '1' OR `project_status` = '2' OR `project_status` = '3' OR `project_status` = '4' AND `del_flag` = '0' );//          LambdaQueryWrapper lqw = Wrappers.lambdaQuery();//          lqw.eq(PmProject::getId,id)//             .eq(PmProject::getProjectStatus, '1').or()//             .eq(PmProject::getProjectStatus, '2').or()//             .eq(PmProject::getProjectStatus, '3').or()//             .eq(PmProject::getProjectStatus, '4')//             .eq(PmProject::getDelFlag, '0');

【控制台sql打印：】

在这里插入图片描述

-- 错误写法一：1、通过id能找到一条未被删除（`del_flag`=0）的数据SELECTid,`project_code`,`project_name`,`project_status`,`project_type_code`,`start_time`,`end_time`,`del_flag`,`creator_id`,`modifier_id`,create_time,update_time,`delay_end_time` FROMpm_project WHEREid = 287 AND `del_flag` = 0;-- 2、COUNT = 19SELECTCOUNT( * ) FROMpm_project WHERE`del_flag` = 0 AND ( id = 287 AND `project_status` = '1' OR `project_status` = '2' OR `project_status` = '3' OR `project_status` = '4' AND `del_flag` = '0' );

错误写法二：

//  错误写法2://          LambdaQueryWrapper lqw = Wrappers.lambdaQuery();//          lqw.and(wq -> wq//             .eq(PmProject::getId,id)//             .eq(PmProject::getProjectStatus, '1').or()//             .eq(PmProject::getProjectStatus, '2').or()//             .eq(PmProject::getProjectStatus, '3').or()//             .eq(PmProject::getProjectStatus, '4')//             .eq(PmProject::getDelFlag, '0'));

在这里插入图片描述

-- 错误写法二：1、通过id能找到一条未被删除（`del_flag`=0）的数据SELECT id,`project_code`,`project_name`,`project_status`,`project_type_code`,`start_time`,`end_time`,`del_flag`,`creator_id`,`modifier_id`,create_time,update_time,`delay_end_time` FROM pm_project WHERE id=287 AND `del_flag`=0; -- 2、COUNT = 19 SELECT COUNT( * ) FROM pm_project WHERE `del_flag`=0 AND ((id = 287 AND `project_status` = '1' OR  `project_status` = '2' OR `project_status` = '3' OR `project_status` = '4' AND `del_flag` = '0')); -- 上面的sql里写了id = 287 竟然还能查出19条数据，说明where id = 287 没有生效。原因： AND ((id = 287 AND `project_status` = '1' OR `project_status` = '2'-- 拼接短路需要把id放外面，把里面所有的or用括号括起来-- ；下面的sql可以使之生效，查出一条数据-- COUNT = 1SELECTCOUNT( * ) FROMpm_project WHEREid = 287

正确写法：

         LambdaQueryWrapper<PmProject> lqw = Wrappers.lambdaQuery();            lqw.eq(PmProject::getId,id);            lqw.and(wq -> wq               .eq(PmProject::getProjectStatus, '1').or()               .eq(PmProject::getProjectStatus, '2').or()               .eq(PmProject::getProjectStatus, '3').or()               .eq(PmProject::getProjectStatus, '4')               .eq(PmProject::getDelFlag, '0'));if (baseMapper.selectCount(lqw) > 0)   throw new CustomException("项目不是未开始状态,无法删除！");

在这里插入图片描述

 -- 正确写法 1、通过id能找到一条未被删除（`del_flag`=0）的数据SELECTid,`project_code`,`project_name`,`project_status`,`project_type_code`,`start_time`,`end_time`,`del_flag`,`creator_id`,`modifier_id`,create_time,update_time,`delay_end_time` FROMpm_project WHEREid = 287 AND `del_flag` = 0;-- 2找不到状态为1或2或3或4的项目，故可以删除SELECTCOUNT( * ) FROMpm_project WHERE`del_flag` = 0 AND (id = 287 AND ( `project_status` = '1' OR `project_status` = '2' OR `project_status` = '3' OR `project_status` = '4' AND `del_flag` = '0' ));

删除项目时校验的最终正确写法：

    @Override   @Transactional   public Boolean deleteWithValidByIds(Collection<Long> ids, Boolean isValid) {      if(isValid){         //TODO 做一些业务上的校验,判断是否需要校验         ids.stream().forEach(id->{            if (baseMapper.selectById(id)==null) {               throw new CustomException("项目基础信息不存在");            }            LambdaQueryWrapper<PmProject> lqw = Wrappers.lambdaQuery();            lqw.eq(PmProject::getId,id);            lqw.and(wq -> wq               .eq(PmProject::getProjectStatus, '1').or()               .eq(PmProject::getProjectStatus, '2').or()               .eq(PmProject::getProjectStatus, '3').or()               .eq(PmProject::getProjectStatus, '4')               .eq(PmProject::getDelFlag, '0'));            if (baseMapper.selectCount(lqw) > 0)               throw new CustomException("项目不是未开始状态,无法删除！");         });      }      return removeByIds(ids);   }

and、or的用法

// WHERE xxxx!=id And ( xxxx=ANo or xxxx=BNo)            LambdaQueryWrapper<Project> queryWrapper = new LambdaQueryWrapper<>();            queryWrapper.ne(Project::getId,project.getId());            queryWrapper.and((wrapper)->{                wrapper.eq(Project::getANo,project.getBillNo())                        .or().eq(Project::getBNo,project.getBillNo());            });