How to make a @OneToMany with a @Subselect and a Composite PK

Viewed 16

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?

0 Answers
Related