LambdaQueryWrapper 实现 OR 条件查询

1. 基本 OR 条件实现

方式一：使用 or() 方法

java

public List<CapitalInfo> queryWithOrCondition() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1)
    wrapper.eq(CapitalInfo::getCapitalYear, 2025)
           .or()
           .eq(CapitalInfo::getCapitalState, 1);
    
    return capitalInfoMapper.selectList(wrapper);
}

方式二：使用 or(Consumer) 函数式编程（推荐）

java

public List<CapitalInfo> queryWithOrCondition2() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1)
    wrapper.or(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                     .or()
                     .eq(CapitalInfo::getCapitalState, 1));
    
    return capitalInfoMapper.selectList(wrapper);
}

2. 更清晰的 OR 条件写法

使用 apply() 直接写 SQL（不推荐，但有时方便）

java

public List<CapitalInfo> queryWithOrCondition3() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // 直接写 OR 条件
    wrapper.apply("capital_year = 2025 OR capital_state = 1");
    
    return capitalInfoMapper.selectList(wrapper);
}

3. 带括号的复杂 OR 条件

情况一：(A = X OR B = Y) AND C = Z

java

public List<CapitalInfo> queryComplexOr1() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1) AND capital_type = 'A'
    wrapper.and(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                      .or()
                      .eq(CapitalInfo::getCapitalState, 1))
           .eq(CapitalInfo::getCapitalType, "A");
    
    return capitalInfoMapper.selectList(wrapper);
}

情况二：A = X AND (B = Y OR C = Z)

java

public List<CapitalInfo> queryComplexOr2() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE capital_year = 2025 AND (capital_state = 1 OR capital_type = 'A')
    wrapper.eq(CapitalInfo::getCapitalYear, 2025)
           .and(w -> w.eq(CapitalInfo::getCapitalState, 1)
                      .or()
                      .eq(CapitalInfo::getCapitalType, "A"));
    
    return capitalInfoMapper.selectList(wrapper);
}

情况三：多个 OR 条件组合

java

public List<CapitalInfo> queryMultipleOr() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1) 
    //   AND (capital_type = 'A' OR capital_source = '政府')
    wrapper.and(w1 -> w1.eq(CapitalInfo::getCapitalYear, 2025)
                        .or()
                        .eq(CapitalInfo::getCapitalState, 1))
           .and(w2 -> w2.eq(CapitalInfo::getCapitalType, "A")
                        .or()
                        .eq(CapitalInfo::getCapitalSource, "政府"));
    
    return capitalInfoMapper.selectList(wrapper);
}

4. OR 条件与其他查询组合

示例 1：OR + LIKE 模糊查询

java

public List<CapitalInfo> queryOrWithLike() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1) 
    //   AND capital_name LIKE '%资金%'
    wrapper.and(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                      .or()
                      .eq(CapitalInfo::getCapitalState, 1))
           .like(CapitalInfo::getCapitalName, "资金");
    
    return capitalInfoMapper.selectList(wrapper);
}

示例 2：OR + BETWEEN 范围查询

java

public List<CapitalInfo> queryOrWithBetween() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1) 
    //   AND capital_year BETWEEN 2020 AND 2030
    wrapper.and(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                      .or()
                      .eq(CapitalInfo::getCapitalState, 1))
           .between(CapitalInfo::getCapitalYear, 2020, 2030);
    
    return capitalInfoMapper.selectList(wrapper);
}

示例 3：OR + IN 查询

java

public List<CapitalInfo> queryOrWithIn() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR capital_state = 1) 
    //   AND capital_type IN ('A', 'B', 'C')
    wrapper.and(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                      .or()
                      .eq(CapitalInfo::getCapitalState, 1))
           .in(CapitalInfo::getCapitalType, Arrays.asList("A", "B", "C"));
    
    return capitalInfoMapper.selectList(wrapper);
}

5. 动态 OR 条件构建

根据参数动态构建 OR 条件

java

public List<CapitalInfo> queryDynamicOr(CapitalInfoQueryDTO queryDTO) {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // 动态构建 OR 条件
    wrapper.or(w -> {
        if (queryDTO.getCapitalYear() != null) {
            w.eq(CapitalInfo::getCapitalYear, queryDTO.getCapitalYear());
        }
        if (queryDTO.getCapitalState() != null) {
            // 如果是 OR 关系的第二个条件，需要添加 or()
            if (queryDTO.getCapitalYear() != null) {
                w.or();
            }
            w.eq(CapitalInfo::getCapitalState, queryDTO.getCapitalState());
        }
    });
    
    // 添加其他 AND 条件
    if (StringUtils.isNotBlank(queryDTO.getCapitalName())) {
        wrapper.like(CapitalInfo::getCapitalName, queryDTO.getCapitalName());
    }
    
    return capitalInfoMapper.selectList(wrapper);
}

更通用的动态 OR 构建方法

java

public List<CapitalInfo> queryDynamicOr2(CapitalInfoQueryDTO queryDTO) {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // 使用 Consumer 构建动态 OR 条件
    buildOrConditions(wrapper, queryDTO);
    
    return capitalInfoMapper.selectList(wrapper);
}

private void buildOrConditions(LambdaQueryWrapper<CapitalInfo> wrapper, 
                               CapitalInfoQueryDTO queryDTO) {
    // 收集需要 OR 连接的条件
    List<Consumer<LambdaQueryWrapper<CapitalInfo>>> orConditions = new ArrayList<>();
    
    if (queryDTO.getCapitalYear() != null) {
        orConditions.add(w -> w.eq(CapitalInfo::getCapitalYear, queryDTO.getCapitalYear()));
    }
    
    if (queryDTO.getCapitalState() != null) {
        orConditions.add(w -> w.eq(CapitalInfo::getCapitalState, queryDTO.getCapitalState()));
    }
    
    if (StringUtils.isNotBlank(queryDTO.getCapitalType())) {
        orConditions.add(w -> w.eq(CapitalInfo::getCapitalType, queryDTO.getCapitalType()));
    }
    
    // 如果有 OR 条件，则构建 OR 查询
    if (!orConditions.isEmpty()) {
        wrapper.and(w -> {
            for (int i = 0; i < orConditions.size(); i++) {
                Consumer<LambdaQueryWrapper<CapitalInfo>> condition = orConditions.get(i);
                if (i > 0) {
                    w.or();
                }
                condition.accept(w);
            }
        });
    }
}

6. OR 条件 + 排序 + 分页

java

public IPage<CapitalInfo> queryOrWithPage(CapitalInfoQueryDTO queryDTO, 
                                         Integer pageNum, 
                                         Integer pageSize) {
    Page<CapitalInfo> page = new Page<>(pageNum, pageSize);
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // OR 条件
    wrapper.or(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                     .or()
                     .eq(CapitalInfo::getCapitalState, 1));
    
    // 其他 AND 条件
    if (StringUtils.isNotBlank(queryDTO.getCapitalName())) {
        wrapper.like(CapitalInfo::getCapitalName, queryDTO.getCapitalName());
    }
    
    // 排序
    wrapper.orderByDesc(CapitalInfo::getCreateTime)
           .orderByAsc(CapitalInfo::getCapitalState);
    
    return capitalInfoMapper.selectPage(page, wrapper);
}

7. 嵌套 OR 条件（更复杂的场景）

java

public List<CapitalInfo> queryNestedOr() {
    LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
    
    // WHERE (capital_year = 2025 OR (capital_state = 1 AND capital_type = 'A'))
    wrapper.or(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                     .or()
                     .and(inner -> inner.eq(CapitalInfo::getCapitalState, 1)
                                        .eq(CapitalInfo::getCapitalType, "A")));
    
    return capitalInfoMapper.selectList(wrapper);
}

8. 常见错误和正确写法对比

❌ 错误写法：

java

// 错误：这样写会生成错误的 SQL
LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(CapitalInfo::getCapitalYear, 2025)
       .eq(CapitalInfo::getCapitalState, 1);  // 这是 AND，不是 OR
// 生成：WHERE capital_year = 2025 AND capital_state = 1

✅ 正确写法：

java

// 正确：使用 or() 连接
LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(CapitalInfo::getCapitalYear, 2025)
       .or()
       .eq(CapitalInfo::getCapitalState, 1);
// 生成：WHERE capital_year = 2025 OR capital_state = 1

9. 完整业务示例

java

@Service
public class CapitalInfoService {
    
    public List<CapitalInfo> queryCapitalWithOrCondition(CapitalInfoQueryDTO queryDTO) {
        LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
        
        // 基础 OR 条件：年份=2025 或 状态=1
        wrapper.or(w -> w.eq(CapitalInfo::getCapitalYear, 2025)
                         .or()
                         .eq(CapitalInfo::getCapitalState, 1));
        
        // 其他筛选条件（AND 关系）
        if (StringUtils.isNotBlank(queryDTO.getCapitalType())) {
            wrapper.eq(CapitalInfo::getCapitalType, queryDTO.getCapitalType());
        }
        
        if (StringUtils.isNotBlank(queryDTO.getCapitalSource())) {
            wrapper.eq(CapitalInfo::getCapitalSource, queryDTO.getCapitalSource());
        }
        
        if (StringUtils.isNotBlank(queryDTO.getCapitalName())) {
            wrapper.like(CapitalInfo::getCapitalName, queryDTO.getCapitalName());
        }
        
        // 排序
        wrapper.orderByDesc(CapitalInfo::getCapitalYear)
               .orderByAsc(CapitalInfo::getCapitalState)
               .orderByDesc(CapitalInfo::getCreateTime);
        
        return capitalInfoMapper.selectList(wrapper);
    }
    
    /**
     * 更灵活的 OR 条件查询
     * @param years 年份列表（OR 关系）
     * @param states 状态列表（OR 关系）
     */
    public List<CapitalInfo> queryWithMultiOr(List<Integer> years, List<Integer> states) {
        LambdaQueryWrapper<CapitalInfo> wrapper = new LambdaQueryWrapper<>();
        
        // WHERE (capital_year IN (2023,2024,2025) OR capital_state IN (1,2))
        wrapper.and(w -> {
            if (CollectionUtils.isNotEmpty(years)) {
                w.in(CapitalInfo::getCapitalYear, years);
            }
            if (CollectionUtils.isNotEmpty(states)) {
                if (CollectionUtils.isNotEmpty(years)) {
                    w.or();
                }
                w.in(CapitalInfo::getCapitalState, states);
            }
        });
        
        return capitalInfoMapper.selectList(wrapper);
    }
}

10. 生成的 SQL 示例

sql

-- 基础 OR 条件
SELECT * FROM capital_info 
WHERE capital_year = 2025 OR capital_state = 1;

-- 复杂 OR + AND 条件
SELECT * FROM capital_info 
WHERE (capital_year = 2025 OR capital_state = 1) 
  AND capital_type = 'A' 
  AND capital_name LIKE '%资金%'
ORDER BY capital_year DESC, capital_state ASC, create_time DESC;

总结：

• 使用 .or() 方法连接 OR 条件

• 使用 .and(Consumer) 处理带括号的复杂条件

• 注意 OR 条件的优先级，合理使用括号

• LambdaQueryWrapper 提供了类型安全的 OR 条件构建方式