JPA 2: multiple column usage in foreign keys

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


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

-------------Problems Reply------------

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.

Category:java Views:0 Time:2011-01-16

Related post

  • JPA : many-to-many - only one foreign key in the association table 2010-04-14

    I mapped two classes in a ManyToMany association with these annotations : @Entity @Inheritance(strategy=InheritanceType.TABLE_PER_CLASS) public abstract class TechnicalItem extends GenericBusinessObject implements Resumable{ @SequenceGenerator(name="

  • Database column naming for foreign key 2011-04-13

    should I signal the foreign key in a database column name? FKOrder vs. FK_Order vs. Order --------------Solutions------------- The short answer is no - don't put "FK" in column names of foreign key columns. You can still signal the intent of the colu

  • If a Column is a Foreign Key does it need an Index? 2009-09-24

    This question already has an answer here: Does specifying a foreign key make it an index? 3 answers I have created indexes on many of my columns in my tables. Many of these columns are foreign keys as well. Can I safely delete the indexes on the colu

  • In MySQL, how do I create a column with a foreign key? 2010-03-14

    A column with a foreign key to another table's column. How do I do that? --------------Solutions------------- This is not possbile with table engine MyISAM but with InnoDB, e.g.: CREATE TABLE parent (id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB;

  • Show data column instead of foreign key in dynamic data site 2011-10-25

    I am using dynamic data site. I have enabled scaffolding for all tables. It also displays foreign key. I want to display a data column instead of foreign key from the table. Global.System.Data.Linq.Mapping.TableAttribute(Name:="dbo.ItemInProc

  • Should a database table that contains two columns that are foreign keys have a third column which is the primary key? 2010-06-21

    I am guessing no, since the foreign keys are the primary keys in their own tables, so they will be unique. More info I am using MySQL and the following three tables are using the InnoDB engine. ======================= ======================= | galler

  • Java EE 6 JPA 2 ManyToOne Relation Creates Invalid Foreign Key 2010-12-16

    I am trying to create two entities where both entities have embeddedIds. One of the entities have 2 references to the other entity, where both of those references are related as ManyToOne. Example codes are written below; @Embeddable public class Ite

  • JPA map non-id field as foreign key 2011-04-07

    I have two entities as follows: @Entity public class Entity1 { @Id Long id; @Basic @OneToOne @Column(unique=true,nullable=false) String awesome; ... } and @Entity public class Entity2 { @Id Long id; @OneToOne(mappedBy="awesome",targetEntity=Entity1.c

  • Changing a table's primary key column referenced by foreign key in other tables 2011-05-30

    In our DB (on SQL Server 2005) we have a "Customers" table, whose primary key is Client Code, a surrogate, bigint IDENTITY(1,1) key; the table is referenced by a number of other tables in our DB thru a foreign key. A new CR implementation we are esti

  • JPA: Give a name to a Foreign Key on DB? 2011-07-07

    I have a simple questions. How can I give a name to the Foreign Key relations that arise from the @ ManyToOne annotation ? --------------Solutions------------- If you are interested in naming the column used in the foreign key, one may specify the na

  • Joining NHibernate Classes that share a common column but no foreign key 2008-11-20

    I have a couple of tables that I want to map to classes. The tables look like this: Asset --------- AssetId AssetName Product --------- ProductId ProductName AssetId Disposal --------- DisposalId AssetId DisposalDate Basically what I want to do is jo

  • Multiple yet mutually exclusive foreign keys - is this the way to go? 2009-09-29

    I have three tables: Users, Companies and Websites. Users and companies have websites, and thus each user record has a foreign key into the Websites table. Also, each company record has a foreign key into the Websites table. Now I want to include for

  • MYSQL Insert .. Select - Multiple rows & tables with foreign keys? 2009-10-02

    I'm using an INSERT .. SELECT to 'duplicate' rows from a table (with a different foreign key). This works fine, however it's inserting multiple rows and I now need to do another INSERT to insert multiple rows that are linked to each individual row in

  • Can table columns with a foreign key be null? 2010-03-02

    For example, I have a table which has several ID columns to other tables. I want a foreign key to force integrity only if I do put data in there. If I do an update at a later time to populate that column then it will still check the constraint (this

  • SQL Server problems reading columns with a foreign key 2010-05-05

    I have a weird situation, where simple queries seem to never finish for instance SELECT top 100 ArticleID FROM Article WHERE ProductGroupID=379114 returns immediately SELECT top 1000 ArticleID FROM Article WHERE ProductGroupID=379114 never returns SE

  • Fluent Nhibernate mapping multiple classes to single foreign key 2010-11-10

    I have a database structure where one table extends another table, and then a third table has a foreign key that is valid in the first two tables. For example: TABLE Person PersonId int Name varchar ShirtId int TABLE Shirt ShirtId int Color string TA

  • SQL Query to get the table names which use a particular column as a foreign key 2011-01-07

    What sql query would you write to get the table names which have a particular foreign key. Eg. Person_Table(person_id,name,address,...) Purchases_Table(purchaseid, purchased_by_id(fkey)....) AccountDetails_Table(AccountId,PersonID,....) Roles_Table(P

  • Dropping a column with a foreign key 2011-04-08

    On my mysql DB with inno_db engine, I have a table with a foreign key. I want to drop the column (along with the foreign key and the associated index of course - i don't need the whole column!) Now, simply dropping it yields an error: General error:

  • Apply left join to extract data from multiple tables using thier foreign key 2011-06-22

    I have a table TblOrders which have two fields like FldOrderStatusId and FldInstrumentID as a foreign key from the tables called TblOrderStatus and TblInstrumentMasters that possible to left join for thie table. the code is given belo

Copyright (C), All Rights Reserved.

processed in 0.330 (s). 11 q(s)