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