I am currently creating a POST endpoint that sends a request body containing an array of strings and other variables. I am trying to save this data into two different tables, both referencing the same ID that is stored in the first table. Each table schema has an ID column. However, when I try to insert data into the second table using the ID from the first table, it seems like the second table can’t have duplicate IDs, resulting in only one record being saved.
For example, in the service layer
public ReturnDto save(InputDto inputDto) {
FirstTableEntity a = a.builder(...somecode).build()
FirstTableEntity saveda = repo1.save(a); // Spring Data JPA repository interface for performing CRUD on FirstTableEntity
List<SecondTableEntity> List = inputDto.{ArrayofString}().stream().map(
e -> SecondTableEntity.builder()
.id(saveda.getId()) // This is where I am referencing the first table's Id.
...somecode
).collect(Collectors.toList());
repo2.saveAll(List); // Spring Data JPA repository interface for performing CRUD on SecondTableEntity
FirstTable DAO class
(I did not need to specify id for the builder petter due to this @GeneratedValue Annotation for first table and this is the id I am trying to referencing in second table.)
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "SALE_ID")
@JdbcTypeCode(SqlTypes.VARCHAR)
private UUID id;
SecondTable DAO class
@Id
@Column(name = "SALE_ID")
@JdbcTypeCode(SqlTypes.VARCHAR)
private UUID id;
Schema for first table
create or replace FIRSTTABLE (
SALE_ID VARCHAR(100) NOT NULL DEFAULT UUID_STRING() COMMENT 'System generated UUID',...etc
)
Schema for second table
create or replace SECONDTABLE (
SALE_ID VARCHAR(100) NOT NULL COMMENT 'ID of sale',...etc
)
Suppose I have a array of string [a,b] in the request body, and each a,b should have a same id value that referencing first table’s id but it only result that b is the only one get saved.
When I see the log, it seems like a is storing first but then b is overwrite the a since they have a same id. However what I want is below. ID should be uuid but just better understanding I am using simple number.
ID
3 C
3 D
3 E
Any advice would be appreciated. I am in work hour, so I will check for comments every 5-10 minutes. Any feedback would be greatly appreciated. If you need more info, please let me know as well!
1
What you want to use here is a foreign key and a parent-child relationship between FirstTable and SecondTable.
SecondTable should have a separate, its own id which would be unique within SecondTable.
A Primary Key (marked by @Id in java code) is a unique identifier of each entity, so it is always not null and unique within its table. That is why you got ‘a’ overwritten by ‘b’. Since both have the same id – thus represent the same entity.
A Foreign Key is a link to another entity in another table. Usually Foreign Key in the ChildTable refers to the Primary Key of the entity in the ParentTable (but may refer to any other unique field in the ParentTable). Foreign Key can be the same for several entites in the ChildTable (SecondTable in your case).
So what you need to do is to add a foreign key field to the SecondTable (both java and sql) and ‘link’ it to the primary key (@Id) of the FirstTable:
SecondTable entity class
@Id
@Column(name = "ID")
@JdbcTypeCode(SqlTypes.VARCHAR)
private UUID id;
@ManyToOne
@JoinColumn(name = "SALE_ID") // Foreign Key
private FirstTableEntity firstTableEntity;
FirstTableEntity class. You can leave it unchanged, or add a backward link to child entites:
@OneToMany(mappedBy = "firstTableEntity", cascade = CascadeType.ALL)
private List<SecondTableEntity> secondTableEntities;
Schema for SecondTable (syntaxis may differ)
create or replace SECONDTABLE (
ID VARCHAR(100) PRIMARY KEY DEFAULT UUID_STRING() COMMENT 'System generated UUID'
SALE_ID VARCHAR(100) FOREIGN KEY REFERENCES FirstTable(SALE_ID) COMMENT 'ID of sale',...etc )
Additional point – naming conventions. Usually, Primary Key fields are called simply “ID” and Foreign Key fields – “RefferedTableName_ID”. So I would recommend to rename FirstTable PK from “SALE_ID” to “ID” to avoid misunderstandings.