I have a Native Query that makes the union of 2 versions of history:
(SELECT
tv1.ID AS ID,
'V1' AS VERSION,
tv1.MESSAGE AS MESSAGE,
tv1.REQUESTOR AS REQUESTOR
FROM
T_HISTORY_V1 tv1)
UNION ALL
(SELECT
tv2.ID AS ID,
'V2' AS VERSION,
tv2.MESSAGE_REQUEST AS MESSAGE,
tv2.REQUESTOR_USER AS REQUESTOR
FROM
T_HISTORY_V2 tv2)
I created a Entity with a composed PK and using @Subselect to represents this query:
Primary Key
@Getter
@Setter
@Embeddable
@NoArgsConstructor
@AllArgsConstructor
public class HistoryPK implements Serializable {
@Column(name = "ID")
private Long id;
@Column(name = "VERSION")
private String version;
}
Entity
@Getter
@Setter
@Entity
@Immutable
@NoArgsConstructor
@AllArgsConstructor
@Subselect("(SELECT " +
" tv1.ID AS ID, " +
" 'V1' AS VERSION, " +
" tv1.MESSAGE AS MESSAGE, " +
" tv1.REQUESTOR AS REQUESTOR "
" FROM " +
" T_HISTORY_V1 tv1) " +
" UNION ALL " +
" (SELECT " +
" tv2.ID AS ID, " +
" 'V2' AS VERSION, " +
" tv2.MESSAGE_REQUEST AS MESSAGE, " +
" tv2.REQUESTOR_USER AS REQUESTOR " +
" FROM " +
" T_HISTORY_V2 tv2) ")
public class HistoryReport {
@EmbeddedId
private HistoryPK id;
@Column(name = "MESSAGE")
private String message;
@Column(name = "REQUESTOR")
private String requestor;
}
So far everything works perfectly. I can use this entity with a repository and consult the information. But each history (V1 and V2) has a link with a list of changes. and I would like to make a OneToMany of my entity with a table of changes of V1 and also with table of changes of V2. Something like:
My Changes V1 table:
@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "T_CHANGES_V1")
@SequenceGenerator(name = "CHANGES_V1_SEQ", sequenceName = "CHANGES_V1_SEQ", initialValue = 1,
allocationSize = 1)
public class ChangesV1 {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CHANGES_V1_SEQ")
private Long id;
@Column(name = "ID_HISTORY")
private Long idHistory;
@Column(name = "FIELD_NAME")
private String fieldName;
}
And inside my HistoryReport I wanna do something like this:
@OneToMany
@JoinColumn(name = "idHistory", referencedColumnName = "id.id")
private List<ChangesV1> changes;
Link my composedID with the changes table. But isnt work. Someone can help me?