Spring Data JPA原生SQL联查返回EmpDeptDto报错求助

背景 #

我有Employee和Department两个实体，希望通过联查返回EmpDeptDto结果，但执行代码时出现SQL语法错误。

Employee实体类 #

@Entity
@Table(name = "employee")
@Data
public class Employee {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "email")
    private String email;

    @Column(name = "address")
    private String address;

    @Column(name = "dept_id")
    private Long deptId;
}

Department实体类 #

@Entity
@Table(name = "department")
public class Department {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "description")
    private String description;
}

EmpDeptDto类 #

@Data
@NoArgsConstructor
@AllArgsConstructor
public class EmpDeptDto {
    private String empDept;
    private String empName;
    private String empEmail;
    private String empAddress;
}

Repository代码 #

@Query(value = "SELECT new com.example.join.dto.EmpDeptDto (d.name as empDept, e.name as empName, e.email as empEmail, "
            + "e.address as empAddress) "
            + "FROM department d LEFT JOIN employee e ON d.id = e.dept_id", nativeQuery = true)
    List<EmpDeptDto> fetchEmpDeptDataJoin();

错误信息 #

执行时返回以下错误：

SQL Error: 1064, SQLState: 42000
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.example.join.dto.DeptEmpDto(d.name as empDept, e.name as empName, e.email as em' at line 1

解决方案 #

问题核心是**nativeQuery = true的误用**：
当设置nativeQuery = true时，JPA会直接把语句传给MySQL执行，但new com.example.join.dto.EmpDeptDto(...)是JPQL专属的语法，MySQL无法识别这种Java类实例化写法，因此触发语法错误。

提供两种解决方式：

方式1：改用JPQL查询（推荐） #

删除nativeQuery = true，让JPA用JPQL解析语句，注意JPQL使用实体类名和属性名，而非数据库表名和字段名：

@Query("SELECT new com.example.join.dto.EmpDeptDto(d.name, e.name, e.email, e.address) "
        + "FROM Department d LEFT JOIN Employee e ON d.id = e.deptId")
List<EmpDeptDto> fetchEmpDeptDataJoin();

• JPQL中引用的是实体类Department、Employee，不是数据库表名department、employee
• 引用实体属性d.id、e.deptId，不是数据库字段d.id、e.dept_id
• DTO构造参数顺序需与全参构造器一致，无需加as别名，JPQL会按顺序匹配构造器参数

方式2：保留原生SQL，通过投影映射DTO #

如果必须使用原生SQL，需保证查询结果列名与DTO属性名一致，让Spring Data自动映射：

@Query(value = "SELECT d.name as empDept, e.name as empName, e.email as empEmail, e.address as empAddress "
        + "FROM department d LEFT JOIN employee e ON d.id = e.dept_id", nativeQuery = true)
List<EmpDeptDto> fetchEmpDeptDataJoin();

• 移除new ...的JPQL语法，直接返回列名与DTO属性完全匹配的结果集
• 需确保DTO有无参构造器，Spring Data会自动完成映射

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