Spring Boot使用@Query联表查询时出现依赖注入失败错误

问题描述 #

在Spring Boot项目中，通过Repository的@Query编写联表查询语句时，启动出现UnsatisfiedDependencyException错误，提示无法创建employeeRepository Bean，根源是查询方法getJoinInformation()验证失败。

错误日志：

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'empcontroller': Unsatisfied dependency expressed through field 'emprepo'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'employeeRepository' defined in com.emp.employeeMangement.api.Repository.EmployeeRepository defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.emp.employeeMangement.api.Repository.EmployeeRepository.getJoinInformation(); Reason: Validation failed for query for method public abstract java.util.List com.emp.employeeMangement.api.Repository.EmployeeRepository.getJoinInformation()!; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.emp.employeeMangement.api.Repository.EmployeeRepository.getJoinInformation()!

错误原因分析 #

1. 实体关联属性名大小写错误：@Query中的e.Attendence是实体类的关联属性名，Java属性名通常为小驼峰格式（比如attendence），大写开头会导致JPA找不到对应的关联关系。
2. ResponseDTO构造参数不匹配：ResponseDTO的全参构造函数包含7个参数，但查询语句仅传递了6个参数（缺少salAmount），JPA无法找到匹配的构造函数实例化DTO。
3. 错误的@Bean注解：EmployeeRepository接口上添加了@Bean注解，Spring Data JPA会自动生成Repository代理Bean，无需手动声明@Bean。

修复步骤 #

1. 修正实体关联属性名 #

检查Employee实体中与Attendence的关联属性，假设属性名为attendence，修改@Query中的关联名称：

@Query(value = "SELECT new com.emp.employeeMangement.api.DTO.ResponseDTO(e.empName,e.gender,e.email,e.empCode,a.noOfPresent,a.noOfAbsent) from Employee e JOIN e.attendence a")

2. 匹配ResponseDTO构造参数 #

有两种可选方案：

方案一：补充salAmount的查询（若存在Salary关联）

如果Employee与Salary存在关联，修改查询语句加入salAmount字段：

@Query(value = "SELECT new com.emp.employeeMangement.api.DTO.ResponseDTO(e.empName,e.gender,e.email,e.empCode,a.noOfPresent,a.noOfAbsent,s.salAmount) from Employee e JOIN e.attendence a JOIN e.salary s")

方案二：添加对应参数的构造函数

在ResponseDTO中新增一个接收6个参数的构造函数（保留原有全参构造）：

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ResponseDTO {
    private String empName;
    private String email;
    private String gender;
    private int empCode;
    private int noOfPresent;
    private int noOfAbsent;
    private int salAmount;

    // 新增匹配查询参数的构造函数
    public ResponseDTO(String empName, String gender, String email, int empCode, int noOfPresent, int noOfAbsent) {
        this.empName = empName;
        this.gender = gender;
        this.email = email;
        this.empCode = empCode;
        this.noOfPresent = noOfPresent;
        this.noOfAbsent = noOfAbsent;
        this.salAmount = 0; // 给salAmount设置默认值
    }
}

3. 移除错误的@Bean注解 #

删除EmployeeRepository接口上的@Bean注解：

package com.emp.employeeMangement.api.Repository;

import com.emp.employeeMangement.api.DTO.ResponseDTO;
import com.emp.employeeMangement.api.Model.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee,Long> {

    @Query(value = "SELECT new com.emp.employeeMangement.api.DTO.ResponseDTO(e.empName,e.gender,e.email,e.empCode,a.noOfPresent,a.noOfAbsent) from Employee e JOIN e.attendence a")
    List<ResponseDTO> getJoinInformation();

}

验证修复 #

启动项目后，访问/getInfo接口，即可正常获取联表查询结果。

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