Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » How can I share a column between two FKs to the same reference table?
How can I share a column between two FKs to the same reference table? [message #1804719] Sat, 30 March 2019 06:47 Go to next message
Amalia ytrety is currently offline Amalia ytretyFriend
Messages: 1
Registered: March 2019
Junior Member
I have posted this question on Stackoverflow and am yet to see a solution. Let me post the same question today.

How can I share a column between two FKs to the same reference table? I have four entities: Player,Team, TeamPlayer and PlayerScore.

Now here is the use case:

Every batsman in cricket (sorry for a non-global example) playing for a specific team will be scoring when he has a partner-batsman called the runner. Now, the PlayerScore entity needs to capture this information. So, I must ensure that both the batsman and his partner are playing for the same team. I can use this table to understand which pairs of batsman have been the performing the best. In exact terms, I need two references from PlayerScore Entity to the TeamPlayer entity. Both of them share exactly one column, team. dqfanfeedback
How can I achieve this?

Here are the four classes:
@Entity
@Table(name="team")
public class Team {

@Id
private int id;

@Column(name="name",length=50)
private String name;
}

@Entity
@Table(name="player")
public class Player {

@Id
private int id;

@Column(name="name",length=50)
private String name;
}

@Entity
@Table(name="team_player")
public class TeamPlayer {

@EmbeddedId
private TeamPlayerPK id;

@ManyToOne(targetEntity=Player.class)
@JoinColumn(name="player")
private Player player;

@ManyToOne(targetEntity=Team.class)
@JoinColumn(name="team")
private Team team;

@Column(name="name",length=50)
private String name;

@Embeddable
public static class TeamPlayerPK implements Serializable
{

private static final long serialVersionUID = 1L;

private int team;
private int player;
}
}

@Entity
@Table(name="player_score")
public class PlayerScore {

@Id
private int scoreId;

@ManyToOne(targetEntity=TeamPlayer.class)
@JoinColumns(value={@JoinColumn(name="team",referencedColumnName="team"),@JoinColumn(name="batsmen",referencedColumnName="player")})
private TeamPlayer batsman; 

@ManyToOne(targetEntity=TeamPlayer.class)
@JoinColumns(value={@JoinColumn(name="team",referencedColumnName="team"),@JoinColumn(name="runner",referencedColumnName="player")})
private TeamPlayer runner;

private int score;

@Temporal(TemporalType.DATE)
private Date matchDate;
}


Can you help me please?

Thank you,

[Updated on: Tue, 02 April 2019 07:52]

Report message to a moderator

Re: How can I share a column between two FKs to the same reference table? [message #1805156 is a reply to message #1804719] Mon, 08 April 2019 18:10 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1388
Registered: July 2009
Senior Member
Why do you have a link to "https://www.dqfanfeedback.xyz/"?

I'm not quite sure your issue as you've only shown a class with no description of the fields you mean to share. Looking over your TeamPlayer class, it seems you might be trying to map the Foreign key as a ManyToOne as well as the ID. JPA 2.0 makes it unnecessary to have a separate basic mapping; you can just mark the relationship as the @Id. See https://wiki.eclipse.org/EclipseLink/Examples/JPA/2.0/DerivedIdentifiers

Either that, or use the @MapsId annotation to indicate that the relationship uses the foreign key field you specify in the annotation (instead of defining a joinColumn). See https://www.eclipse.org/eclipselink/api/2.6/index.html?javax/persistence/MapsId.html


Regards,
Chris
Re: How can I share a column between two FKs to the same reference table? [message #1830262 is a reply to message #1804719] Wed, 22 July 2020 11:27 Go to previous messageGo to next message
John Vons is currently offline John VonsFriend
Messages: 1
Registered: July 2020
Junior Member
I have four entities: Player,Team, TeamPlayer and PlayerScore.

Now here is the use case:

Every batsman in cricket (sorry for a non-global example) playing for a specific team will be scoring when he has a partner-batsman called the runner. Now, the PlayerScore entity needs to capture this information. So, I must ensure that both the batsman and his partner are playing for the same team. I can use this table to understand which pairs of batsman have been the performing the best. In exact terms, I need two references from PlayerScore Entity to the TeamPlayer entity. Both of them share exactly one column, team. How can I achieve this?
Re: How can I share a column between two FKs to the same reference table? [message #1830275 is a reply to message #1830262] Wed, 22 July 2020 14:06 Go to previous message
Adam Chalkley is currently offline Adam ChalkleyFriend
Messages: 3
Registered: July 2020
Junior Member
To clarify you essentially want to know how to map:

create table player_score (
    ...
    player_1 references player (id),
    player_2 references player (id),
    team references team (id),
    primary key (player_1, player_2, team)
);


as you have a class/table already:
create table team_player (
    ...
    player references player (id),
    team references team (id),
    primary key(player, team)
);


You want to map the EmbeddedId you've created for the primary key in the TeamPlayer object to two in the PlayerScore object.
I think you'd achieve (not tested) with

@EmbeddedId
@AttributeOverride(name="player", @Column(name="player_1"))
TeamPlayerPK teamPlayer1;

@EmbeddedId
@AttributeOverride(name="player", @Column(name="player_2"))
TeamPlayerPK teamPlayer2;

e.g. see https://stackoverflow.com/questions/36571347/jpa-how-to-override-column-names-of-embedded-attributes/36571484

[Edit: re-read you were already suggesting using just 1 team pkey.]

[Updated on: Wed, 22 July 2020 21:57]

Report message to a moderator

Previous Topic:Error on calling StoredProcedure
Next Topic:Issue with Primary Key with Postgresql and Audit Trigger 91+
Goto Forum:
  


Current Time: Fri Jun 25 06:35:33 GMT 2021

Powered by FUDForum. Page generated in 0.02060 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top