Spring Boot JPA实现复杂内连接：自定义查询返回类型咨询

针对你需要实现的自定义多列查询，以下是具体的Repository代码实现，同时说明几种可行的返回类型方案：

1. 推荐方案：自定义DTO类封装结果 #

先创建一个DTO类来对应查询返回的字段，确保字段和查询列一一对应：

import lombok.Data;
import lombok.AllArgsConstructor;

@Data
@AllArgsConstructor
public class QuestionWorkflowDTO {
    private String businessId;
    private String parentId;
    private String parentDescription;
    private String childId;
    private String childDescription;
}

注：如果不使用Lombok，需要手动编写全参构造方法、getter和setter方法。

2. Repository 核心代码 #

创建继承自JpaRepository的接口，通过@Query注解执行原生SQL：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface QuestionsWorkflowRepository extends JpaRepository<QuestionsWorkflow, String> {

    @Query(value = "select a.business_id, a.parent_id, b.description, a.child_id, c.description " +
            "from questions_workflow a " +
            "inner join questions b on a.parent_id=b.id " +
            "inner join questions c on a.child_id=c.id " +
            "where a.business_id=:businessId",
            nativeQuery = true)
    List<QuestionWorkflowDTO> findByBusinessId(@Param("businessId") String businessId);
}

其他可选返回类型（无需创建DTO） #

如果不想额外创建DTO类，也可以使用以下两种方式：

• 返回List<Object[]>：每个数组对应一行结果，按查询列的顺序取值

@Query(value = "select a.business_id, a.parent_id, b.description, a.child_id, c.description " +
        "from questions_workflow a " +
        "inner join questions b on a.parent_id=b.id " +
        "inner join questions c on a.child_id=c.id " +
        "where a.business_id=:businessId",
        nativeQuery = true)
List<Object[]> findByBusinessIdAsArray(@Param("businessId") String businessId);

• 返回List<Map<String, Object>>：键为SQL查询的列名（或别名），值为对应字段值

@Query(value = "select a.business_id as businessId, a.parent_id as parentId, b.description as parentDescription, a.child_id as childId, c.description as childDescription " +
        "from questions_workflow a " +
        "inner join questions b on a.parent_id=b.id " +
        "inner join questions c on a.child_id=c.id " +
        "where a.business_id=:businessId",
        nativeQuery = true)
List<Map<String, Object>> findByBusinessIdAsMap(@Param("businessId") String businessId);

内容的提问来源于stack exchange，提问作者SRI HARSHA S V S