JPA mapping 2 entity table with a relationship table between them

Question

I'm trying to map these two entitiy tables to one model class, game and platform. A game can have multiple platforms. So in my db I have the following tables:

CREATE TABLE IF NOT EXISTS games (
id                          SERIAL  NOT NULL PRIMARY KEY,
name                        VARCHAR,
summary                     TEXT,
avg_score                   REAL,
);

CREATE TABLE IF NOT EXISTS platforms (
id   SERIAL NOT NULL PRIMARY KEY,
name VARCHAR
);

And a relationship table called Game_Platforms

CREATE TABLE IF NOT EXISTS game_platforms (
id          serial not null primary key,
game_id      INTEGER NOT NULL,
platform_id  INTEGER NOT NULL,
release_date date not null,

FOREIGN KEY (game_id) REFERENCES games (id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (platform_id) REFERENCES platforms (id) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(game_id,platform_id, release_date)   --The same game can be released for the same platform multiple times (i.e. remaster)
);

Note that the tables have more columns but I'm just showing the ones that are relevant to this problem.

So on my model I have the following class which I want to map to the db using JPA

@Entity
@Table(name = "games")
public class Game {

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "games_gameid_seq")
private long id;

@Column(length = 100, nullable = false, unique = true)
private String name;

//Map with platforms
private Map<String, LocalDate> platforms;

And platform which doesn't have a class because I didn't find it necessary. I don't think a @OneToMany annotation will be enough to map this. Adding a class "Platform" to my model would be of last resort as I would have to change most of my interfaces, needing to change the whole app. Any idea on how to aproach this? Thanks in advance!

Technically you could add your GamePlatform entity (because it really is part of the model and you're not going to get around creating it) and then add a method that simply produces your Map object so the existing code keeps working. You could deprecate it and in time phase it out rather than in one big bang. — Gimby, Oct 26 '16 at 06:22
Remove ID column from platforms table and use `@ElementCollection`. Platform does not then need to be mapped as an Entity. Solution with a Map is detailed here. http://stackoverflow.com/questions/3393649/storing-a-mapstring-string-using-jpa — Alan Hay, Oct 26 '16 at 08:07
@Gimby ended up using your solution. Thanks a lot! Great idea — J. Nicastro, Oct 26 '16 at 21:21

score 1 · Accepted Answer · edited May 23 '17 at 12:33

When using an Object Relational Mapping tool such as Hibernate / the JPA API, you should be thinking in an object relational model - keyword: object. Thus not wanting to map a table as an entity is very counterproductive to your needs as you won't be able to use the technology as intended; you'd have to resort to using native queries.

Instead, do map all tables involved (GamPlatform in this case) and actually put JPA to work by adding the relational mappings.

@Entity
public class Game {

  ...

  @OneToMany(mappedBy="game")
  private Set<GamePlatform> gamePlatforms;

  ...
}

The real problem to be solved is the fact that there is a large pile of existing code which will break because of this change. To temporarily deal with that you could maintain the existing getter for the platforms property as it is and under water build up the returned HashMap from the newly defined model. Assuming the getter is named getPlatforms():

@Deprecated
public Map<String, LocalDate> getPlatforms(){
  Map<String,LocalDate> ret = new HashMap<>();

  gamePlatforms.stream().forEach(gamePlatform -> {
    ret.put(gamePlatform.getPlatform().getName(), gamePlatform.getReleaseDate());
  });

  return ret;
}

You could mark that method as deprecated and over time migrate code to use the proper entity classes instead.

Footnote: I'm sure there is an even compacter Java 8 way to do the above using Collectors.toMap, that is left as an exercise to the reader.

I realised, by migrating to hibernate from jdbc, that my db was inconsistent with my model. I will have to add platform and other entities to the model as you said. I wanted to avoid the interfaces refactoring but you made me realise that I have no choice but to do it. Thanks for time and your temporary solution proposal. — J. Nicastro, Oct 27 '16 at 10:59

score 0 · Answer 2 · answered Oct 26 '16 at 05:58

if I understand your meaning correctly you have one game and each game have several platform! you need onToMany relation and need to creat platform model. look at this example Game.java

@Entity
@Table(name = "games")
public class Game {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "games_gameid_seq")
    private long id;

    @Column(name = "name", nullable = false)
    private String name;

    @OneToMany(mappedBy="game_name")
    @Column(name = "PlatFormId", nullable = false)
    private Set<PlatForm>   PlatFormId= new HashSet<PlatForm>(0);

}

PlatForm.java

@Entity
@Table(name = "platForm")
public class PlatForm {

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "games_gameid_seq")
private long id;

@Column(name = "name", nullable = false)
private String name;

    @Column(name = "game_name", nullable = false)
    private Game  game;

Thanks for answering me. But as I said , adding a platform class to my model would a last resort solution. If no one's know a best way I will stick to this. Thanks again! — J. Nicastro, Oct 26 '16 at 06:03

JPA mapping 2 entity table with a relationship table between them

2 Answers2