[FIXED] Hibernate join tables with maps

Issue

So I'm getting myself up to speed with Hibernate and I've run into an issue that I can't quite understand how to model in Hibernate that I could use some guidance/advice on.

So I'm building a database for recording the results of Mario Kart tournaments (could be any racing tournament). I have the following entities:

• Player - First and last name of the player
• Track - The name of the track
• Race - The Track the race was on, the date of the race, the tournament the race belongs to, and the players who were part of the race
• Tournament - The name of the tournament, the players in the tournament, the races that were part of the tournament

The issue I'm struggling with is the relationship between race and player. I think a join table should be used to resolve the many to many issue, but I think it would also need to have a field to show the position the given player finished in the given race (it could also be points scored, but that is easy to convert from one to the other). This would have a join table that had the player id, race id, and points.

What sort of annotations should I be using here? I feel like they should be on the Race entity, as the concept of points only makes sense in the context of the given race, however I don't know what I am doing at this point.

Any guidance/help/ideas are appreciated.

Solution

If I had to do this, I would base my Entities around a single Race. Each Race has a Track, and contains a List of Players with their individual Position.

A Tournament would then be a List of Races.

In the End, I would want the Database to look something like this:

Create TABLE race (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    raceName TEXT NOT NULL,
    dateOfRace timestamp default CURRENT_TIMESTAMP,
);

Create TABLE track (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    trackName TEXT NOT NULL,
);

Create TABLE race_tracks (
    race_id BIGINT UNIQUE,
    track_id BIGINT,
  CONSTRAINT `FK_RACE_TRACK_RACE_ID` FOREIGN KEY (`race_id`) REFERENCES `race` (`id`),
  CONSTRAINT `FK_RACE_TRACK_TRACK_ID` FOREIGN KEY (`track_id`) REFERENCES `track` (`id`)
);

Create TABLE player (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    firstName TEXT NOT NULL,
    lastName TEXT NOT NULL,
);

Create TABLE race_participation (
    race_id BIGINT,
    player_id BIGINT,
    position int NOT NULL,
  CONSTRAINT `FK_RACE_PART_RACE_ID` FOREIGN KEY (`race_id`) REFERENCES `race` (`id`),
  CONSTRAINT `FK_RACE_PART_PLAYER_ID` FOREIGN KEY (`player_id`) REFERENCES `player` (`id`)
);

Create TABLE tournament (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tournamentName TEXT NOT NULL,
);

Create TABLE tournament_races (
    race_id BIGINT,
    tournament_id BIGINT,
    position int NOT NULL,
  CONSTRAINT `FK_TOURNEMANT_RACES_RACE_ID` FOREIGN KEY (`race_id`) REFERENCES `race` (`id`),
  CONSTRAINT `FK_TOURNEMANT_RACES_TOURNAMENT_ID` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`id`)
);

This would result in the Following Entities

@Entity
@Table(name = "player")
public class Player {
    
    @Id
    @GeneratedValue
    private Long id;
    
    private String firstName;
    private String lastName;
    
    @OneToMany(mappedBy="player")
    private List<RaceParticipation> participation;

   // Getter and Settet
}

@Entity
@Table(name = "race")
public class Race {
    
    @Id
    @GeneratedValue
    private Long id;
    
    @Column(name = "raceName")
    private String name;
    
    @Column(name = "dateOfRace", columnDefinition = "timestamp default CURRENT_TIMESTAMP")
    private Timestamp dateOfRace;
    
    
    @OneToMany(mappedBy="race")
    private List<RaceParticipation> participation;

    @OneToOne
    @JoinTable(
            name="race_tracks",
            joinColumns=@JoinColumn(name = "race_id", referencedColumnName = "id", unique=true),
            inverseJoinColumns=@JoinColumn(name = "track_id")
    )
    private Track track;
   
    // Getter and Setter
    }

@Entity
@Table(name="track")
public class Track {
    
    @Id
    @GeneratedValue
    private Long id;
    
    private String trackName;

    // Getter and Setter
}

@Entity
@Table(name = "race_participation")
public class RaceParticipation {
    
    @EmbeddedId
    private RaceParticipationId id;
    
    @MapsId("player_id")
    @ManyToOne
    @JoinColumn(name = "player_id")
    private Player player;
    
    
    @MapsId("race_id")
    @ManyToOne
    @JoinColumn(name = "race_id")
    private Race race;
    
    private Integer position;

   // Getter and Setter
}

@Embeddable
public class RaceParticipationId implements Serializable {
    
    @Column(name = "player_id")
    private Long player_id;
    
    @Column(name = "race_id")
    private Long race_id;
   
   // Getter and Setter
}

@Entity
@Table(name="tournament")
public class Tournament {
    
    @Id
    @GeneratedValue
    private Long id;
    
    private String tournamentName;
    
    @OneToMany
    @JoinTable(
            name = "tournament_races",
            joinColumns=@JoinColumn(name = "tournament_id",referencedColumnName="id"),
            inverseJoinColumns=@JoinColumn(name="race_id", referencedColumnName="id")
    )
    private List<Race> races;

    // Getter and Setter
}

Answered By - Motscha
Answer Checked By - David Goodson (JavaFixing Volunteer)