Insert data into multiple tables in sleep mode

I've been stuck with this all day. I have a form creating a POST for the API and I want the data to be stored in 3 tables.

  • Records are stored in a single table ( Squad ), which has an auto-generated identifier. When pasting into this table, I want to read the automatically generated identifier of the records and paste them into another table ( SquadPlayers ), and also add an additional field that was sent by the form in this 2nd table ( SquadPlayers : GenericPlayerId).
  • Also a little about what I want to submit from the foreground form. I need all the squad information plus identifiers for up to 11 players (these identifiers are what I would like to save in the GenericPlayerId field for the SquadPlayers table).

I am new to backend coding, especially databases, and this new stack that I chose for training, so if you see something stupid here, now you know why :-) So if you think that I completely wrong with my database design let me know. enter image description here

So far I have it in two classes for Squad and SquadPlayers.

Squad.java

package com.FUT.track.web.FUTtrackapplication.squads; import javax.persistence.*; @Entity @Table(name="Squad") public class Squad { @Id @GeneratedValue( strategy = GenerationType.IDENTITY ) private int squadId; private String squadName; private String squadDescription; private String primaryFormation; @OneToOne(cascade = CascadeType.ALL) @JoinColumn(name = "playerId") private SquadPlayers squadPlayers; public Squad() { } public Squad(String squadName, String squadDescription, String primaryFormation, SquadPlayers squadPlayers) { super(); this.squadName = squadName; this.squadDescription = squadDescription; this.primaryFormation = primaryFormation; this.squadPlayers = squadPlayers; } public int getSquadId() { return squadId; } public void setSquadId(int squadId) { this.squadId = squadId; } public String getSquadName() { return squadName; } public void setSquadName(String squadName) { this.squadName = squadName; } public String getSquadDescription() { return squadDescription; } public void setSquadDescription(String squadDescription) { this.squadDescription = squadDescription; } public String getPrimaryFormation() { return primaryFormation; } public void setPrimaryFormation(String primaryFormation) { this.primaryFormation = primaryFormation; } public SquadPlayers getSquadPlayers() { return squadPlayers; } public void setSquadPlayers(SquadPlayers squadPlayers) { this.squadPlayers = squadPlayers; } } 

Squadplayers.java

  package com.FUT.track.web.FUTtrackapplication.squads; import javax.persistence.*; @Entity @Table(name="SquadPlayers") public class SquadPlayers { @Id private Integer playerId; private Integer squadId; private Integer genericPlayerId; @OneToOne(mappedBy = "squadPlayers") private Squad squad; public Integer getPlayerId() { return playerId; } public void setPlayerId(Integer playerId) { this.playerId = playerId; } public Integer getSquadId() { return squadId; } public void setSquadId(Integer squadId) { this.squadId = squadId; } public Squad getSquad() { return squad; } public void setSquad(Squad squad) { this.squad = squad; } public Integer getGenericPlayerId() { return genericPlayerId; } public void setGenericPlayerId(Integer genericPlayerId) { this.genericPlayerId = genericPlayerId; } } 
+5
source share
2 answers

Assuming you want your data stream to execute

  • You have a predefined list of player . And player can belong to several units uniquely
  • You will create squad when submitting the form by assigning player . And the designated player will have a unique identification number.
  • Each squadPlayer will have a playerStat

Note:

  • The relationship between player and squad ManyToMany
  • Your squadPlayer is the connection table between player and squad
  • So the relationship between player and squadPlayer OneToMany
  • And the connection between squad and squadPlayer OneToMany

Player

 @Entity @Table(name = "player") public class Player { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private String name; private String position; private String country; private String club; @OneToMany(mappedBy = "player") private Set<SquadPlayer> squadSet = new HashSet<>(); .... } 

Here, your player object has a OneToMany connection to the squadSet field, which displays the fact that the player can be included in several squads.

Structure

 @Entity @Table(name="squad") public class Squad { @Id @GeneratedValue( strategy = GenerationType.IDENTITY ) private int id; private String name; private String description; private String primaryFormation; @OneToMany(mappedBy = "squad", cascade = {CascadeType.MERGE, CascadeType.PERSIST}) private Set<SquadPlayer> playerSet = new HashSet<>(); ... } 

And here, the squad entity has OneToMany associated with the playerSet field, in which Squad can have multiple players. Note that unlike the player object, the OneToMany annotation here defines the cascading type of Merge and Persist . This indicates hibernation while maintaining this relationship while maintaining Squad.

Squadplayer

 @Entity @Table(name = "squad_player") public class SquadPlayer { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; @Column(name = "generic_player_id") private int genericPlayerId; @ManyToOne @JoinColumn(name = "squad_id") private Squad squad; @ManyToOne @JoinColumn(name = "player_id") private Player player; @OneToOne(mappedBy = "squadPlayer", orphanRemoval = true, cascade = CascadeType.ALL) private PlayerStat playerStat; ... } 

Just here, having the other end of the display for player and squad with the corresponding join column

Your playerStat has a OneToOne relationship with SquadPlayer . If orphanRemoval=true will remove the entry from playerStat when squadPlayer is removed (although not necessary). We also defined cascading rules for this relationship.

Playerstat

 @Entity @Table(name = "player_stat") public class PlayerStat { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; private int performance; @OneToOne @JoinColumn(name = "squad_player_id") private SquadPlayer squadPlayer; ... } 

Persian squad and all these relationships

Remember that JPA or Hibernate defines your relationship with a database with an object graph. And entities are preserved along with cascading relationships from the definition of their rites. So this is a stream that you can use to create the appropriate attribute for the squad object

  • Create new Squad() and set all the fields with the fields provided.
  • When you get the player id list from the form, pull those player objects within one transaction
  • repeat all these player and create new SquadPlayer() for each. Define all related fields along with the playerStat field. Then add each squadPlayer to the squad object playerSet .
  • Finally save the squad object

If you follow all this flow, your database should populate all the tables with the appropriate relationships.

For further reading:

+5
source

I would change the structure as follows:

1 - completely delete the SquadPlayers table and go for a simple oneToMany relationship between the player and squad => add fk for the player referencing squadId so that the player can be part of only one unit at a time (make it null if there are players without a unit)

2 - to record the result and performance, I would change the pk table from the PlayerStats table to a composite Pk (playerId-SquadId), both columns being Fk, too attached to the player and the team => you can have several characteristics of the player’s performance, different squads.

Does it make sense, or have I missed the logic of logic that you need?

Ps jpa-wise you should use @OneToMany and @ManyToOne annotations for your entity for this type of table structure.

Player

 @Entity @Table(name="Player") public class Player { @Id private Integer playerId; @ManyToOne @JoinColumn(name = "playerSquadId", referencedColumnName = "squadId") private Squad squad; @OneToMany(mappedBy = "player") private List<PlayerStats> playerStats ; 

Structure

 @Entity @Table(name="Squad") public class Squad { @Id @GeneratedValue( strategy = GenerationType.IDENTITY ) private Integer squadId; @OneToMany(mappedBy = "squad") private List<Player> players; @OneToMany(mappedBy = "squad") private List<PlayerStats> playerStats ; 

Playerstats

 @Entity @Table(name="PlayerStats") public class PlayerStats { @Id private Integer playerId; @Id private Integer squadId; @ManyToOne @JoinColumn(name = "playerStatsPlayerId", referencedColumnName = "playerId", insertable = false, updatable = false) private Player player; @ManyToOne @JoinColumn(name = "playerStatsSquadId", referencedColumnName = "squadId", insertable = false, updatable = false) private Squad squad; 
+1
source

Source: https://habr.com/ru/post/1273290/


All Articles