如何在Repository中正确映射原生查询到实体模型

你的核心问题是SQL返回的是一个实验对应多个化合物的一对多结果，但之前的模型设计没有正确处理这种关系，导致要么重复数据要么映射失败。以下是两种可行的解决方式：

方式一：用DTO手动组装结果（快速适配现有原生查询） #

因为你用的是原生SQL查询，直接用DTO（数据传输对象，不需要@Entity注解）接收聚合结果是最直接的方案，能避开JPA实体映射的限制。

1. 定义DTO类 #

// 实验详情DTO，聚合多个化合物
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExperimentDetailsDTO {
    private String externalId;
    private String isid;
    private List<Compound> compounds;
}

// 化合物DTO
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Compound {
    private String name;
    private String controlledStatus;
    private String jurisdictionName;
    private String resultComments;
    private String codeName;
    private String corporateId;
}

2. 修改Repository返回原始结果 #

将Repository方法改为返回Tuple类型，获取每一行的原始查询数据：

public interface ExperimentDetailsRepository extends JpaRepository<ControlledEvent, String> {

    @Query(value = "Select ce.external_id, ce.isid, ji.name, ji.controlled_status, " +
        "ji.jurisdiction_name, ji.result_comments," +
        "ji.code_name, s.corporate_id " +
        "from cs_jurisdiction_information ji " +
        "Join substance s on ji.substance_id = s.id " +
        "Join controlled_event ce on ce.id = s.controlled_event_id " +
        "and ce.calling_system_id = 402 " +
        "and ce.external_id = :externalId", nativeQuery = true)
    List<Tuple> findRawDataByExternalId(@Param("externalId") String externalId);
}

3. 在Service层组装DTO #

提取实验的公共信息，再把每一行的化合物数据组装成集合：

@Service
public class ExperimentDetailsService {

    @Autowired
    private ExperimentDetailsRepository repository;

    public ExperimentDetailsDTO getExperimentDetails(String externalId) {
        List<Tuple> rawData = repository.findRawDataByExternalId(externalId);
        if (rawData.isEmpty()) {
            return null;
        }

        // 提取实验公共字段（所有行的external_id和isid一致）
        String expExternalId = (String) rawData.get(0).get("external_id");
        String isid = (String) rawData.get(0).get("isid");

        // 组装化合物列表
        List<Compound> compounds = new ArrayList<>();
        for (Tuple row : rawData) {
            Compound compound = new Compound();
            compound.setName((String) row.get("name"));
            compound.setControlledStatus((String) row.get("controlled_status"));
            compound.setJurisdictionName((String) row.get("jurisdiction_name"));
            compound.setResultComments((String) row.get("result_comments"));
            compound.setCodeName((String) row.get("code_name"));
            compound.setCorporateId((String) row.get("corporate_id"));
            compounds.add(compound);
        }

        return new ExperimentDetailsDTO(expExternalId, isid, compounds);
    }
}

方式二：建立JPA实体关联（符合ORM规范） #

如果数据库表本身是一对多关系（一个controlled_event对应多个substance，每个substance对应一个jurisdiction_information），可以通过实体关联实现自动映射，无需原生SQL。

1. 定义关联实体 #

// ControlledEvent实体（对应controlled_event表）
@Entity
@Table(name = "controlled_event")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ControlledEvent {
    @Id
    private Long id;
    private String externalId;
    private String isid;
    private Integer callingSystemId;

    // 一对多关联Substance
    @OneToMany(mappedBy = "controlledEvent", fetch = FetchType.LAZY)
    private List<Substance> substances;
}

// Substance实体（对应substance表）
@Entity
@Table(name = "substance")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Substance {
    @Id
    private Long id;
    private String corporateId;

    // 多对一关联ControlledEvent
    @ManyToOne
    @JoinColumn(name = "controlled_event_id")
    private ControlledEvent controlledEvent;

    // 一对一关联JurisdictionInformation
    @OneToOne(mappedBy = "substance", fetch = FetchType.EAGER)
    private JurisdictionInformation jurisdictionInfo;
}

// JurisdictionInformation实体（对应cs_jurisdiction_information表）
@Entity
@Table(name = "cs_jurisdiction_information")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class JurisdictionInformation {
    @Id
    private Long id;
    private String name;
    private String controlledStatus;
    private String jurisdictionName;
    private String resultComments;
    private String codeName;

    // 一对一关联Substance
    @OneToOne
    @JoinColumn(name = "substance_id")
    private Substance substance;
}

2. 简化Repository查询 #

public interface ControlledEventRepository extends JpaRepository<ControlledEvent, Long> {
    Optional<ControlledEvent> findByExternalIdAndCallingSystemId(String externalId, Integer callingSystemId);
}

3. Service层组装DTO #

从关联实体中提取数据，转换为目标DTO格式：

@Service
public class ExperimentDetailsService {

    @Autowired
    private ControlledEventRepository eventRepository;

    public ExperimentDetailsDTO getExperimentDetails(String externalId) {
        Optional<ControlledEvent> eventOpt = eventRepository.findByExternalIdAndCallingSystemId(externalId, 402);
        if (eventOpt.isEmpty()) {
            return null;
        }

        ControlledEvent event = eventOpt.get();
        List<Compound> compounds = event.getSubstances().stream()
                .map(substance -> {
                    JurisdictionInformation ji = substance.getJurisdictionInfo();
                    Compound compound = new Compound();
                    compound.setName(ji.getName());
                    compound.setControlledStatus(ji.getControlledStatus());
                    compound.setJurisdictionName(ji.getJurisdictionName());
                    compound.setResultComments(ji.getResultComments());
                    compound.setCodeName(ji.getCodeName());
                    compound.setCorporateId(substance.getCorporateId());
                    return compound;
                })
                .collect(Collectors.toList());

        return new ExperimentDetailsDTO(event.getExternalId(), event.getIsid(), compounds);
    }
}

为什么之前的方案失败？ #

• 扁平实体模型：JPA会把查询的每一行映射为一个实体对象，而externalId是@Id，JPA会认为所有行都是同一个实体，后续行的字段会覆盖前面的，导致重复第一个化合物的数据。
• @Embedded单Compound：@Embedded只能映射单个嵌入对象，无法处理多行的化合物数据，所以只能得到第一个化合物。
• @Embedded List：JPA的@Embedded不支持集合类型，无法将多行结果映射为一个实体中的集合字段，因此compounds字段为空。

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