LambdaQueryWrapper——apply支持IN操作

背景

在做Rou-Yi项目中，我遇到了一个问题，在只有用LambdaQueryWrapper的项目中，其写了如下代码

// TrainRequestMapper.java
// 权限校验
@DataPermission({
    // 按部门查询需要限制部门
    @DataColumn(key = "deptName", value = "d.dept_id"),
    // 查询用户的自己的数据
    @DataColumn(key = "userName", value = "t.user_id")
})
Page<TrainRequestVo> selectPageTrainRequestList(@Param("page") Page<TrainRequest> page,
                                                @Param(Constants.WRAPPER) Wrapper<TrainRequest> queryWrapper);
// TrainRequestMapper.xml
<select id="selectPageTrainRequestList" resultMap="BaseResultMapVO">
        select t.request_id,
               t.dept_id,
               t.user_id,
               t.title,
               t.description,
               t.status,
               t.reason,
               t.create_by,
               t.create_time,
               t.update_by,
               t.update_time,
               t.remark
        from wfhb_train_request t
        left join sys_dept d on t.dept_id = d.dept_id
        ${ew.getCustomSqlSegment}
</select>

    // 	TrainRequestServiceImpl.java
    private LambdaQueryWrapper<TrainRequest> buildQueryWrapper(TrainRequestBo bo) {
        Long deptId = bo.getDeptId();
        LambdaQueryWrapper<TrainRequest> lqw = Wrappers.lambdaQuery();
        lqw.and(bo.getDeptId() != null, w -> {
            List<SysDept> deptList = deptMapper.selectList(new LambdaQueryWrapper<SysDept>()
                .select(SysDept::getDeptId)
                .apply(DataBaseHelper.findInSet(deptId, "ancestors")));

            List<Long> ids = StreamUtils.toList(deptList, SysDept::getDeptId);
            ids.add(deptId);
            w.in(TrainRequest::getDeptId, ids);
        });
        lqw.apply(status != null, "t.status = {0}", status);
        lqw.orderByDesc(TrainRequest::getUpdateTime);
        // lqw.last(status != null, "WHERE t.status = " + status + " ORDER BY t.update_time DESC");
        return lqw;
    }

在上述代码中我们使用了第41行代码进行动态的sql拼接

w.in(TrainRequest::getDeptId, ids);

但这样会出现问题，由于在后续的权限校验时也会拼接上Sys_dept表，Sys_dept表中存在dept_id，同时在LambdaQueryWrapper中in方法中（也就是如上代码）最终拼接的SQL为

WHERE dpet_id IN (106) // 假设最终拼接的数据为106

这会导致字段冲突，无法正确执行SQL

最终的解决方案

使用apply手动拼接SQL

private LambdaQueryWrapper<TrainRequest> buildQueryWrapper(TrainRequestBo bo) {
        Long deptId = bo.getDeptId();
        LambdaQueryWrapper<TrainRequest> lqw = Wrappers.lambdaQuery();
        lqw.and(bo.getDeptId() != null, w -> {
            List<SysDept> deptList = deptMapper.selectList(new LambdaQueryWrapper<SysDept>()
                .select(SysDept::getDeptId)
                .apply(DataBaseHelper.findInSet(deptId, "ancestors")));

            List<Long> ids = StreamUtils.toList(deptList, SysDept::getDeptId);
            ids.add(deptId);
            // 将 List 转换为逗号分隔的字符串
            String idStr = ids.stream()
                .map(String::valueOf)
                .collect(Collectors.joining(", "));

            // 手动拼接 SQL
            w.apply("t.dept_id IN (" + idStr + ")");
        });
        // 字段存在歧义，手动指定字段
        lqw.apply(status != null, "t.status = {0}", status);
        lqw.orderByDesc(TrainRequest::getUpdateTime);
        // lqw.last(status != null, "WHERE t.status = " + status + " ORDER BY t.update_time DESC");
        log.debug("Final QueryWrapper: {}", lqw.getSqlSegment());
        return lqw;
    }

总结

apply用法

1. 直接拼接，使用{0},{1},表示要拼接的参数

// 本文使用的代码
lqw.apply(status != null, "t.status = {0}", status);
// 多参数代码
lqw.apply(status != null, "t.status = {0} AND t.user_id = {1}", status, userId);

2. 自己手动拼接SQL，可以实现 IN 操作

// 将 List 转换为逗号分隔的字符串
String idStr = ids.stream()
.map(String::valueOf)
.collect(Collectors.joining(", "));

// 手动拼接 SQL
w.apply("t.dept_id IN (" + idStr + ")");