I have this sql query I am running in my database:
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 = 'EXP-raoan-000011774';
Which produces the above results. Everything following the first two fields are compounds that I would like to map to my model, so when I run this query from my repository:
public interface ExperimentDetailsRepository extends JpaRepository<ExperimentDetails, 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<ExperimentDetails> findByExternalId(@Param("externalId") String externalId);
}
I first tried this model:
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class ExperimentDetails {
@Id
private String externalId;
private String isid;
private String name;
private String controlledStatus;
private String jurisdictionName;
private String resultComments;
private String codeName;
private String corporateId;
}
Calling findByExternalId(externalId) in my service class produces the incorrect results, the method returns 3 objects which is fine, but only maps the first compound Cathine, and duplicates the result for the next two objects, which makes sense because my structure here is flat, when it actually should be an array for anything after name attribute.
So I tried using @Embeddable annotation to modify the model to the correct structure as below:
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class ExperimentDetails {
@Id
private String externalId;
private String isid;
@Embedded
@AttributeOverrides({
@AttributeOverride(name = "name", column = @Column(name = "name")),
@AttributeOverride(name = "controlledStatus", column = @Column(name = "controlled_status")),
@AttributeOverride(name = "jurisdictionName", column = @Column(name = "jurisdiction_name")),
@AttributeOverride(name = "resultComments", column = @Column(name = "result_comments")),
@AttributeOverride(name = "codeName", column = @Column(name = "code_name")),
@AttributeOverride(name = "corporateId", column = @Column(name = "corporate_id")),
})
private Compound compounds;
}
and I created the Embeddable class:
@Embeddable
@Data
public class Compound {
private String name;
private String controlledStatus;
private String jurisdictionName;
private String resultComments;
private String codeName;
private String corporateId;
}
This duplicates the same error in results as the previous model, which I then realized still doesn't produce the array of compounds, so I modified the model again:
@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
public class ExperimentDetails {
@Id
private String externalId;
private String isid;
@Embedded
@AttributeOverrides({
@AttributeOverride(name = "name", column = @Column(name = "name")),
@AttributeOverride(name = "controlledStatus", column = @Column(name = "controlled_status")),
@AttributeOverride(name = "jurisdictionName", column = @Column(name = "jurisdiction_name")),
@AttributeOverride(name = "resultComments", column = @Column(name = "result_comments")),
@AttributeOverride(name = "codeName", column = @Column(name = "code_name")),
@AttributeOverride(name = "corporateId", column = @Column(name = "corporate_id")),
})
private List<Compound> compounds;
}
This time making Compound a list. However, running findByExternalId(externalId) now produces null for the embedded class, so no compounds are actually mapped.
How can I map my model correctly to produce the correct results for my query?
