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

【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<PmProject> 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）的数据
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' );

---

错误写法二：

//  错误写法2:
//          LambdaQueryWrapper<PmProject> 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 = 1
SELECT
	COUNT( * ) 
FROM
	pm_project 
WHERE
	id = 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）的数据
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找不到状态为1或2或3或4的项目，故可以删除
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' ));

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

    @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());
            });