MySQL/JPQL中如何关联四张表并复用code.id作为查询过滤条件

1. 修复基础关联查询（解决重复别名问题） #

两次关联project_code表必须使用不同别名，分别对应费用分配、项目分配关联的项目编码。基础JPQL语句分两种场景：

基于实体关联的JPQL（推荐，依赖实体映射关系） #

假设实体已配置好关联：Bill一对多关联ExpenseDistribution和ItemDistribution，后两者多对一关联ProjectCode

SELECT b.id, expCode.name, itemCode.name 
FROM Bill b
LEFT JOIN b.expenseDistributions exp
LEFT JOIN exp.projectCode expCode
LEFT JOIN b.itemDistributions itm
LEFT JOIN itm.projectCode itemCode

原生SQL风格JPQL（不依赖实体关联） #

SELECT b.id, expCode.name, itemCode.name
FROM Bill b
LEFT JOIN ExpenseDistribution exp ON b.id = exp.billId
LEFT JOIN ProjectCode expCode ON exp.projectCodeId = expCode.id
LEFT JOIN ItemDistribution itm ON b.id = itm.billId
LEFT JOIN ProjectCode itemCode ON itm.projectCodeId = itemCode.id

2. 动态过滤最优实现：Criteria API #

动态查询用JPA Criteria API更安全（避免SQL注入）、易维护，核心实现步骤如下：

// 示例：动态传入过滤参数
Long targetProjectCodeId = ...; // 需匹配的项目编码ID
String billNameKeyword = ...; // 账单名称模糊查询关键词

EntityManager em = ...; // 获取EntityManager实例
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);

// 根表关联
Root<Bill> root = query.from(Bill.class);

// 关联费用分配及对应项目编码
Join<Bill, ExpenseDistribution> expJoin = root.join("expenseDistributions", JoinType.LEFT);
Join<ExpenseDistribution, ProjectCode> expCodeJoin = expJoin.join("projectCode", JoinType.LEFT);

// 关联项目分配及对应项目编码
Join<Bill, ItemDistribution> itmJoin = root.join("itemDistributions", JoinType.LEFT);
Join<ItemDistribution, ProjectCode> itemCodeJoin = itmJoin.join("projectCode", JoinType.LEFT);

// 动态构建过滤条件
List<Predicate> predicates = new ArrayList<>();
if (targetProjectCodeId != null) {
    // 匹配费用分配或项目分配中的项目编码
    Predicate expMatch = cb.equal(expCodeJoin.get("id"), targetProjectCodeId);
    Predicate itemMatch = cb.equal(itemCodeJoin.get("id"), targetProjectCodeId);
    predicates.add(cb.or(expMatch, itemMatch));
}
if (billNameKeyword != null && !billNameKeyword.isEmpty()) {
    // 账单名称模糊查询
    predicates.add(cb.like(root.get("name"), "%" + billNameKeyword + "%"));
}

// 组装查询条件并指定返回字段
query.where(cb.and(predicates.toArray(new Predicate[0])));
query.multiselect(root.get("id"), expCodeJoin.get("name"), itemCodeJoin.get("name"));
// 可选：去重（一对多关联可能产生重复结果）
query.distinct(true);

// 执行查询
List<Object[]> results = em.createQuery(query).getResultList();

核心优势 #

• 完全避免手动拼接JPQL字符串的风险，防止SQL注入
• 动态条件添加灵活，可根据业务需求自由组合
• 代码可读性、维护性更高，关联关系清晰

3. 注意事项 #

• 确保实体类关联关系配置正确（如@OneToMany、@ManyToOne注解），否则Criteria API无法识别关联字段
• 若使用Spring Data JPA，可结合Specification接口封装动态查询逻辑，进一步简化代码

内容的提问来源于stack exchange，提问作者Hashila