Inserting Data in Multiple Tables in Hibernate

Question

I have been stuck on this all day. I have a form making a POST to the API and I want the data to be saved into 3 tables.

1. Records are saved in one table (Squad) that has auto generated Id. At insert in this table I want to read the auto generated Id of records and insert those in a different table (SquadPlayers) plus also insert an extra field that was submitted by the form in this 2nd table (SquadPlayers: GenericPlayerId).
2. Also a bit about what I want to submit from the front end form. I want all info about the squad plus Ids for upto 11 players submitted (these ids are what I will like to save in the field GenericPlayerId filed for SquadPlayers table).

I am new to backend coding especially databases and this new stack I picked up for learning purposes so if you are seeing anything silly here, now you know why :-) So if you think I am totally off or wrong with the my database design let me know.

So far I have this in my 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;
    }

}

Answer 1

Assuming this is how you want to have your data flow execution

• You have predefined list of player. And a player could belong to multiple squad uniquely
• You will create a squad on a form submission by assigning players to it. And a assigned player will have a unique identification number.
• Every squadPlayer will have playerStat

Note:

• Relation between Player and Squad is ManyToMany
• Your SquadPlayer is the joining table between Player and Squad
• So, Relation between Player and SquadPlayer is OneToMany
• And Relation between Squad and SquadPlayer is 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 entity has OneToMany association with squadSet field that depicts the fact a player can be included in multiple squad.

Squad

@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 Squad entity has OneToMany association with playerSet field depicts a Squad could have multiple players. Note unlike the Player entity here OneToMany annotation defines the cascade type of Merge and Persist. This tells hibernate to persist this relation too when persisting 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;

    ...
}

Simply here having the other end of mapping for both Player and Squad with respective joining column

Now your PlayerStat entity has OneToOne relation with SquadPlayer. Having orphanRemoval=true will remove the entry from PlayerStat when a SquadPlayer is removed (though its optional). We also defined the cascading rules for this relation.

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;
    ...
}

Persist Squad and all this Relations

Remember JPA or Hibernate determines your database relationships with the object graph. And the entities persist along with cascading relations from defining their assciations. So this is the flow that you can follow to make proper assocaiton for Squad object

• Create a new Squad() and set all field with provided fields.
• As you got the list of player id from the form, pull those player objects within same transaction
• iterate all those player and create new SquadPlayer() for each. Set all associated fields along with PlayerStat field. Then add each SquadPlayer to the Squad object's playerSet field.
• Finally Persist the Squad object

If you follow all in this flow your database should populate all tables with proper relations.

For Further Reading:

• Understanding the Relationship Mapping see this OneToMany, ManyToMany, ManyToOne and OneToOne quick guides
• A good article to start with JPA and Hibernate Cascade Types
• Another good article about Cascading Best Practices

Answer 2

I would change the structure in the following way:

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

2 - for recording result and performance purpose i would change the pk of PlayerStats table to a composite Pk (playerId-SquadId) with both column being Fk too referencing player and squad => you can have multiple performance stats about a player in different squads.

Does it makes sense or i missed some busisness logic that you needed?

P.s. jpa-wise you should be using @OneToMany and @ManyToOne annotations on your entity for this kind 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 ;

Squad

@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;