SpringBoot JPA Repository自定义返回类型的优化方案咨询

我希望从JPA Repository仅返回实体类的部分字段，但所需数据中不包含主键，无法直接使用实体类作为返回类型。尝试创建视图类，但仅实体包下的类能被仓库识别；使用List<?>仅能得到值，而我需要键值对。目前采用嵌套Map的方式可得到正确结果，想咨询是否有更优实现方案。

当前代码示例：

public interface JobRepository extends Repository<Job, Integer> {

    @Query(value = "SELECT j.employee_id,e.employee_name,e.employee_email, COUNT(*) as count FROM job j INNER join employee e on j.employee_id=e.employee_id GROUP BY j.employee_id", nativeQuery = true)
    List<Map<Integer, Map<String, Map<String, Integer>>>> getEmployeeCount();
}

输出结果：

[{
    "employee_name": "Employee 1",
    "employee_id": 1,
    "employee_email": "employee1@gmail.com",
    "count": 2
}]

数据库结构：

1. 简化Map返回类型 #

你当前使用的嵌套Map完全冗余，实际返回的是单层键值对结构，直接改用List<Map<String, Object>>即可，代码更简洁：

public interface JobRepository extends Repository<Job, Integer> {

    @Query(value = "SELECT j.employee_id,e.employee_name,e.employee_email, COUNT(*) as count FROM job j INNER join employee e on j.employee_id=e.employee_id GROUP BY j.employee_id", nativeQuery = true)
    List<Map<String, Object>> getEmployeeCount();
}

返回的Map会自动以查询列名（含别名）作为key，字段值作为value，和你当前的输出结果完全一致。

2. 接口投影（Spring Data JPA 推荐方案） #

定义一个投影接口，包含所需字段对应的get方法，Spring Data会自动生成实现类并映射查询结果，类型安全且无需手动处理键值对：

// 可放在任意包下，无需局限于实体包
public interface EmployeeCountProjection {
    Integer getEmployeeId();
    String getEmployeeName();
    String getEmployeeEmail();
    Long getCount(); // COUNT(*)返回数值类型，用Long更适配
}

修改Repository方法，注意原生SQL的列别名要和接口get方法名的驼峰格式对应（如employee_id对应employeeId）：

public interface JobRepository extends Repository<Job, Integer> {

    @Query(value = "SELECT j.employee_id as employeeId, e.employee_name as employeeName, e.employee_email as employeeEmail, COUNT(*) as count FROM job j INNER join employee e on j.employee_id=e.employee_id GROUP BY j.employee_id", nativeQuery = true)
    List<EmployeeCountProjection> getEmployeeCount();
}

3. DTO类+构造函数投影 #

如果需要不可变的返回对象，可定义DTO类，通过构造函数映射查询结果：

// 可放在任意包下
public class EmployeeCountDTO {
    private Integer employeeId;
    private String employeeName;
    private String employeeEmail;
    private Long count;

    // 构造函数参数顺序需与查询结果列顺序完全一致
    public EmployeeCountDTO(Integer employeeId, String employeeName, String employeeEmail, Long count) {
        this.employeeId = employeeId;
        this.employeeName = employeeName;
        this.employeeEmail = employeeEmail;
        this.count = count;
    }

    // 按需添加get方法
    public Integer getEmployeeId() { return employeeId; }
    public String getEmployeeName() { return employeeName; }
    public String getEmployeeEmail() { return employeeEmail; }
    public Long getCount() { return count; }
}

Repository方法实现分两种方式：

public interface JobRepository extends Repository<Job, Integer> {

    // JPQL方式（需指定DTO全限定类名）
    @Query("SELECT new com.yourpackage.EmployeeCountDTO(j.employee.id, e.employeeName, e.employeeEmail, COUNT(j)) FROM Job j JOIN j.employee e GROUP BY j.employee.id")
    List<EmployeeCountDTO> getEmployeeCount();

    // 原生SQL方式（自动匹配构造函数参数顺序）
    @Query(value = "SELECT j.employee_id, e.employee_name, e.employee_email, COUNT(*) FROM job j INNER join employee e on j.employee_id=e.employee_id GROUP BY j.employee_id", nativeQuery = true)
    List<EmployeeCountDTO> getEmployeeCount();
}

4. 解决视图类识别问题 #

你提到的“仅实体包下的类能被仓库识别”，通常是@EntityScan注解的扫描范围配置问题。检查项目中@EntityScan的包路径，确保视图类（DTO/投影）所在包被包含；或者直接采用上述投影/DTO方案，根本不需要将类放在实体包下。

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