Spring项目中JPA原生查询结果转为键值对的实现求助

我帮你梳理几个可行的方案，把原生SQL查询返回的Object数组转成键值对格式，你可以根据自己的需求选择：

方案1：自定义DTO接收结果（最推荐，类型安全易维护） #

这种方式适合长期维护的项目，通过创建一个数据传输对象（DTO）来映射查询结果，返回带字段名的结构化数据。

首先创建DTO类，注意要包含查询结果里的所有字段，并且提供与查询列顺序完全匹配的全参构造方法：

public class ProjectHierarchyDTO {
    private Long projectId;
    private Boolean projectStatus;
    private Long testSuiteId;
    private Boolean testSuiteStatus;
    private Long testScriptId;
    private Boolean testScriptStatus;
    private Long testStepId;
    private Boolean testStepStatus;

    // 构造方法参数顺序必须和SQL查询的列顺序完全一致
    public ProjectHierarchyDTO(Long projectId, Boolean projectStatus, Long testSuiteId, Boolean testSuiteStatus,
                               Long testScriptId, Boolean testScriptStatus, Long testStepId, Boolean testStepStatus) {
        this.projectId = projectId;
        this.projectStatus = projectStatus;
        this.testSuiteId = testSuiteId;
        this.testSuiteStatus = testSuiteStatus;
        this.testScriptId = testScriptId;
        this.testScriptStatus = testScriptStatus;
        this.testStepId = testStepId;
        this.testStepStatus = testStepStatus;
    }

    // 按需添加Getter方法（如果要序列化返回给前端，必须加）
    public Long getProjectId() { return projectId; }
    public Boolean getProjectStatus() { return projectStatus; }
    // 其他字段的Getter省略，自行补充
}

然后修改ProjectRepository.java的方法返回类型：

@Query(value=" SELECT p.id as project_id,p.project_status, t.id as test_suite_id, t.test_suite_status, ts.id as test_script_id,ts.test_script_status, tss.id as test_step_id, tss.test_step_status FROM project p LEFT OUTER JOIN test_suite t ON (p.id = t.project_id AND t.test_suite_status = 1) LEFT OUTER JOIN test_script ts ON (t.id = ts.test_suite_id AND ts.test_script_status=1) LEFT OUTER JOIN test_step tss ON (ts.id = tss.test_script_id AND tss.test_step_status=1) where p.team_id=:teamId and p.project_status=1 ",nativeQuery=true)
public List<ProjectHierarchyDTO> getActiveProjectsWithTeamId(@Param("teamId") Long teamId);

接着同步修改Service层的返回类型：
projectService.java：

List<ProjectHierarchyDTO> findActiveProjectsByTeamId(Long id) throws DAOException;

projectServiceImpl.java：

@Override
@Transactional(readOnly = true)
public List<ProjectHierarchyDTO> findActiveProjectsByTeamId(Long id) throws DAOException {
    log.info("entered into ProjectServiceImpl:findOneByTeamId");
    if (id != null) {
        try {
            List<ProjectHierarchyDTO> projects = projectRepository.getActiveProjectsWithTeamId(id);
            return projects;
        } catch (Exception e) {
            log.error("Exception raised while retrieving the project of the mentioned ID from database : " + e.getMessage());
            throw new DAOException("Exception occured while retrieving the required project");
        } finally {
            log.info("exit from ProjectServiceImpl:findOneByTeamId");
        }
    }
    return null;
}

这样返回的结果就是带有明确字段名的DTO列表，完全符合你想要的键值对格式。

方案2：返回Map<String, Object>（快速实现，无需额外类） #

如果只是临时需求或者快速测试，可以直接让Repository返回List<Map<String, Object>>，Spring Data会自动把SQL查询的列别名作为Map的key，对应的值作为value。

修改ProjectRepository.java：

@Query(value=" SELECT p.id as project_id,p.project_status, t.id as test_suite_id, t.test_suite_status, ts.id as test_script_id,ts.test_script_status, tss.id as test_step_id, tss.test_step_status FROM project p LEFT OUTER JOIN test_suite t ON (p.id = t.project_id AND t.test_suite_status = 1) LEFT OUTER JOIN test_script ts ON (t.id = ts.test_suite_id AND ts.test_script_status=1) LEFT OUTER JOIN test_step tss ON (ts.id = tss.test_script_id AND tss.test_step_status=1) where p.team_id=:teamId and p.project_status=1 ",nativeQuery=true)
public List<Map<String, Object>> getActiveProjectsWithTeamId(@Param("teamId") Long teamId);

同步修改Service层：
projectService.java：

List<Map<String, Object>> findActiveProjectsByTeamId(Long id) throws DAOException;

projectServiceImpl.java：

@Override
@Transactional(readOnly = true)
public List<Map<String, Object>> findActiveProjectsByTeamId(Long id) throws DAOException {
    log.info("entered into ProjectServiceImpl:findOneByTeamId");
    if (id != null) {
        try {
            List<Map<String, Object>> projects = projectRepository.getActiveProjectsWithTeamId(id);
            return projects;
        } catch (Exception e) {
            log.error("Exception raised while retrieving the project of the mentioned ID from database : " + e.getMessage());
            throw new DAOException("Exception occured while retrieving the required project");
        } finally {
            log.info("exit from ProjectServiceImpl:findOneByTeamId");
        }
    }
    return null;
}

这种方式不需要创建额外类，但缺点是没有类型安全，后续维护时容易出错。

方案3：使用Spring Data接口投影（简洁且类型安全） #

还有一种更简洁的方式是用接口投影，定义一个包含对应Getter方法的接口，Spring Data会自动生成代理类来映射结果：

先定义投影接口：

public interface ProjectHierarchyProjection {
    // Getter方法名要和SQL别名对应（驼峰转下划线：project_id → getProjectId）
    Long getProjectId();
    Boolean getProjectStatus();
    Long getTestSuiteId();
    Boolean getTestSuiteStatus();
    Long getTestScriptId();
    Boolean getTestScriptStatus();
    Long getTestStepId();
    Boolean getTestStepStatus();
}

修改ProjectRepository.java的返回类型：

@Query(value=" SELECT p.id as project_id,p.project_status, t.id as test_suite_id, t.test_suite_status, ts.id as test_script_id,ts.test_script_status, tss.id as test_step_id, tss.test_step_status FROM project p LEFT OUTER JOIN test_suite t ON (p.id = t.project_id AND t.test_suite_status = 1) LEFT OUTER JOIN test_script ts ON (t.id = ts.test_suite_id AND ts.test_script_status=1) LEFT OUTER JOIN test_step tss ON (ts.id = tss.test_script_id AND tss.test_step_status=1) where p.team_id=:teamId and p.project_status=1 ",nativeQuery=true)
public List<ProjectHierarchyProjection> getActiveProjectsWithTeamId(@Param("teamId") Long teamId);

然后同步修改Service层的返回类型即可，这种方式兼顾了简洁和类型安全，不需要写构造方法和实现类。

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