执行@Query删除操作时遇异常：D1_0.COMPANY_BRANCH_ID列未找到

开发者阿泽

2026-7-5

问题分析与解决方案

问题原因

你的JPQL删除语句通过多级关联e.department.companyBranch.id过滤待删除的Employee，但Hibernate生成SQL时错误地尝试直接在EMPLOYEE表（别名d1_0）中访问COMPANY_BRANCH_ID字段——而该字段实际属于DEPARTMENT表，根本不存在于EMPLOYEE表中。这是因为JPQL的DELETE语句默认不会为多级关联自动生成JOIN逻辑，而是错误地将关联路径映射为当前表的字段。

解决方案

方案一：JPQL子查询（ORM规范兼容）

通过子查询先定位关联目标CompanyBranch的所有Department的ID，再匹配Employee的关联Department，避免多级关联路径解析错误：

@Modifying
@Query(value = "DELETE FROM Employee e WHERE e.department.id IN " +
               "(SELECT d.id FROM Department d WHERE d.companyBranch.id = :companyBranchId)")
void deleteAllByCompanyBranchId(@Param("companyBranchId") Long companyBranchId);

方案二：原生SQL删除（性能更优）

直接编写数据库层面的关联删除逻辑，明确关联EMPLOYEE和DEPARTMENT表，适合大数据量场景：

子查询版本（跨数据库兼容性好）

@Modifying
@Query(value = "DELETE FROM EMPLOYEE e " +
               "WHERE e.DEPARTMENT_ID IN " +
               "(SELECT d.ID FROM DEPARTMENT d WHERE d.COMPANY_BRANCH_ID = :companyBranchId)",
       nativeQuery = true)
void deleteAllByCompanyBranchId(@Param("companyBranchId") Long companyBranchId);

JOIN版本（H2数据库支持，性能更高）

@Modifying
@Query(value = "DELETE e FROM EMPLOYEE e " +
               "JOIN DEPARTMENT d ON e.DEPARTMENT_ID = d.ID " +
               "WHERE d.COMPANY_BRANCH_ID = :companyBranchId",
       nativeQuery = true)
void deleteAllByCompanyBranchId(@Param("companyBranchId") Long companyBranchId);