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.

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

}

@O.Badr When I submit my form Squad table in database should have data for all properties you see above (submitted from the form )and right after it should take the auto generated Ids for the records from Squad table and all player Ids submitted from the form and save in Squad Players. I had the simple save working with one table. Now that I have broken down the structure into multiple tables I'm not sure how to achieve this. — sayayin
– sayayin, Commented Nov 12, 2017 at 3:19
you should do a fetch after first insert is successful if not throw an exception , use @transactional in service method, create separate service class methods and repository interfaces ... — Sam2016
– Sam2016, Commented Nov 28, 2017 at 6:50
could you define whats the relation between Player and Squad ? I mean in general for your business can a player be assigned in multiple squad ? I am trying to verify your entity mapping. Give your Player entity also. — Shafin Mahmud
– Shafin Mahmud, Commented Nov 28, 2017 at 6:51
One more thing. Its seems from you entity that One Squad will have only one player ! is that so ?? Clear your business first. — Shafin Mahmud
– Shafin Mahmud, Commented Nov 28, 2017 at 7:06

Shafin Mahmud · Accepted Answer · 2018-01-04 14:35:17Z

12

+50

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

edited Jan 4, 2018 at 14:35

answered Nov 28, 2017 at 10:52

Shafin Mahmud

4,0911 gold badge25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

sayayin Over a year ago

Thank you for such a detailed answer. I will go through this and let you know how it works out. Thanks again.

sayayin Over a year ago

It will take a little while as I'm learning all this stuff, I'll keep you posted. Lot of information to absorb and fully understand.

Shafin Mahmud Over a year ago

Updated answer with some preferred articles about entity mapping and cascading. Hope you will find it helpful

Nathan Over a year ago

I would also have added a unique constraint in the SquadPlayer entity. @Table(name = "squad_player", uniqueConstraints = {@UniqueConstraint(columnNames = { "squad_id", "player_id" })}) This will allow your DB to automatically enforce that a player cannot belong to the same squad multiple times.

Shafin Mahmud Over a year ago

yes, @NathanKummer . certainly this contraint could be enforced. But I was just intended to focus on the relationship mapping and cascading things that he was basically stuck in. But I think your notes will help too.

|

Zeromus · Accepted Answer · 2017-11-28 10:49:59Z

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;

Collectives™ on Stack Overflow

Inserting Data in Multiple Tables in Hibernate

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related