JPA 2: multiple column usage in foreign keys

Question

I am using Hibernate as persistence provider and modelling my entities with JPA 2.

Now a question came up and i hope you can help me.

In my application you can open up a Game, create groups of players in it and walk around on the map (tiles (2d)).

First my entity definitions: Game:

@Entity
public class Game implements Serializable {
@Id
@SequenceGenerator(name = "gen_gameid", sequenceName = "seq_gameid")
@GeneratedValue(generator="gen_gameid")
private long gameid;

/**
 * Playing Characters.
 */
@OneToMany(mappedBy = "game")
private List<Character> characters;
private int round = 0;

@OneToMany(mappedBy="game")
private List<Tile> tiles;

@OneToMany(mappedBy="game")
private List<Group> group;

Tile (a tile will be created from a template and belongs to just one game):

@Entity @IdClass(TileId.class)
public class Tile implements Serializable{
    private static final long serialVersionUID = 2039974286110729270L;

    @Id
    private int x;

    @Id
    private int y;

    @Id @ManyToOne @JoinColumn(name="gameid")
    private Game game;

    @OneToOne(mappedBy="tile")
    private Character character;
}

Character:

@ManyToOne
@JoinColumn(name="gameid", referencedColumnName = "gameid")
private Game game;

@ManyToOne
@JoinColumns({
    @JoinColumn(name="groupgameid", referencedColumnName = "gameid"),
    @JoinColumn(name="groupTag", referencedColumnName = "grouptag")
})
private Group group;

    @OneToOne
    @JoinColumns({    
      @JoinColumn(name = "x", referencedColumnName = "x"),
      @JoinColumn(name = "y", referencedColumnName = "y"),
      @JoinColumn(name = "tilegameid", referencedColumnName = "gameid")
    })
    private Tile tile;

As you can see i had to rename the gameid column to groupgameid and tilegameid. This is not very pretty because i would need the gameid in character just once. To mark the fk columns in character from tile and group with insertable=false, updateable=false will allow the sql generation, but i can't change/set this values.

Sure i could introduce an artificial pk in group and tile, but i would need more joins.

Is JPA just limited that i have to allow the gameid column more than once with other names? Or is my design not optimal?

Looking forward to your feedback and thanks in advance. greetings Markus

PS (edit): At Moment i let the shema generate by hibernate at startup till my model is complete. But here is the generated shema (bit simplified and cutted out some unimportant fields):

CREATE TABLE Character
(
  charid bigint NOT NULL,
  gameid bigint,
  grouptag character varying(255),
  x integer,
  y integer,
  CONSTRAINT hero_pkey PRIMARY KEY (charid),
  CONSTRAINT fkd4addb09308bc3b822441a FOREIGN KEY (gameid)
  REFERENCES game (gameid) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkd4addb093091cb6522441a FOREIGN KEY (gameid, x, y)
  REFERENCES tile (gameid, x, y) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fkd4addb09c018f3ae22441a FOREIGN KEY (gameid, grouptag)
  REFERENCES gamegroup (gameid, grouptag) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
) 

CREATE TABLE tile (
  x integer NOT NULL,
  y integer NOT NULL,
  gameid bigint NOT NULL,
  CONSTRAINT tile_pkey PRIMARY KEY (gameid, x, y),
  CONSTRAINT fk27c6ce308bc3b8 FOREIGN KEY (gameid)
  REFERENCES game (gameid) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION)

CREATE TABLE gamegroup
(
  grouptag character varying(255) NOT NULL,
  gameid bigint NOT NULL,
     CONSTRAINT gamegroup_pkey PRIMARY KEY (gameid, grouptag),
  CONSTRAINT fk3c1c51cd308bc3b8 FOREIGN KEY (gameid)
  REFERENCES game (gameid) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)

PS 2: I already played around with , insertable = false, updatable = false. For example when i change the group JoinColumns to:

@ManyToOne
@JoinColumns({
    @JoinColumn(name="gameid", referencedColumnName = "gameid", insertable = false, updatable = false ),
    @JoinColumn(name="groupTag", referencedColumnName = "grouptag")
})
private Group group;

I get an Error that mixing is not allowed: Caused by: org.hibernate.AnnotationException: Mixing insertable and non insertable columns in a property is not allowed: net.hq.model.Charactergroup

When i make both insertable=false i am not able to set the group tag anymore. It groupTag stays empty after insertion and gameid is set. :-/

The way i add a Character to a group:

// Create game
Game game = new Game();
game.addCharacter(max);
em.persist(game);

// Group
Group heroGroup = new Group(game, "HEROES");
heroGroup.addCharacter(max);
em.persist(game);

Method in Group Class:

public void addCharacter(Character character){
    if(this.characters == null)
        this.characters = new ArrayList<Character>();

    this.characters.add(character);
    character.setGroup(this);
}

I'd be much easier to help if you could provide related DB schema and specify @Table annotation of your classes. — andbi, Jan 16 '11 at 11:48

score 13 · Answer 1 · edited Feb 10 '20 at 11:35

13

You need to do this:

@ManyToOne
@JoinColumns({
    @JoinColumn(name="gameid", referencedColumnName = "gameid", insertable = false, updatable = false ),
    @JoinColumn(name="groupTag", referencedColumnName = "grouptag", insertable = false, updatable = false)
})
private Group group;

EDIT: as mentioned in the comments, @JoinColumn is a repeatable annotation (since Java 8) that doesn't need wrapping. This simplifies the solution to:

@ManyToOne
@JoinColumn(name="gameid", referencedColumnName = "gameid", insertable = false, updatable = false ),
@JoinColumn(name="groupTag", referencedColumnName = "grouptag", insertable = false, updatable = false)
private Group group;

edited Feb 10 '20 at 11:35

neXus

2,005
3
29
53

answered Jun 28 '16 at 03:19

Sanama Na

143
1
6

Thank you Sanama Na who got me really close. I has to change to use square brackets since JoinColumns expected an array. – bigMC28 Jun 01 '17 at 21:11
1

In Java 8 you don't need the outer `@JoinColumns` anymore... From sonar-java rule [squid:S1710](https://github.com/joansmith/sonar-java/blob/master/java-checks/src/main/resources/org/sonar/l10n/java/rules/squid/S1710.html): _Before Java 8 if you needed to use multiple instances of the same annotation, they had to be wrapped in a container annotation. With Java 8, that's no longer necessary, allowing for cleaner, more readable code._ – neXus Feb 07 '19 at 16:09
@neXus Just a side note, they don't have to be wrapped, using container for repeatable annotations have disadvantages and advantages.For more Effective Java related section. – thread-game Feb 09 '20 at 14:34

score 4 · Answer 2 · answered Jan 16 '11 at 14:20

4

Are you sure you can't use insertable = false, updateable = false for these @JoinColumns?

As far as I understand, you can initialize gameid once by setting game property, and after that you don't need to change it since Tiles and Groups belong to the same Game.

answered Jan 16 '11 at 14:20

axtavt

239,438
41
511
482

Yes thats true. But it looks like i am not allowed to mix the JoinColums attributes. Explaination in PS 2. Greetings – mkuff Jan 16 '11 at 15:08

JPA 2: multiple column usage in foreign keys

2 Answers2

Linked