Possibly I'm understanding the concept wrong, but here is my problem:
I want to store two types of data in my database:
- game positions (uniquely represented by some lengthy string, containing two lists of moves - one for moves possible in this position and one for moves leading to this position)
- game moves (represented by a smaller string, linking two positions)
In classes:
@Entity
public class Move {
@Id
@GeneratedValue
private long id;
private String representation;
private Position positionBeforeMove;
private Position positionAfterMove;
...
}
@Entity
public class Position {
@Id
private String representation;
private List<Move> movesLeadingToPosition;
private List<Move> movesLeadingFromPosition;
...
}
I want to store a set of games. In this context, the positions are unique, and the moves may occur multiple times in the database.
My naieve implementation uses two tables:
MOVE
number id (PK)
varchar2 representation
varchar2 positionBeforeMove (FK)
varchar2 positionAfterMove (FK)
...
POSITION
varchar2 representation (PK)
...
This works fine; using EntityManager.merge(position) I can grow my POSITION table as I store more and more games, while keeping the positions unique.
However, the solution is not optimal: I'd much prefer to have a generated id like I have in the MOVE table!
@Entity
public class Position {
@Id
@GeneratedValue
private long id;
private String representation;
private List<Move> movesLeadingToPosition;
private List<Move> movesLeadingFromPosition;
...
}
MOVE
number id (PK)
varchar2 representation
number positionBeforeMove_id (FK)
number positionAfterMove_id (FK)
...
POSITION
number id; (PK)
varchar2 representation (Unique)
...
But when I implement this, I end up with duplicate representations, because the EntityManager.merge(...) merges based on the specified Id, not the natural key.
Is there any way I can let java (JPA / Hibernate / ...) merge the positions based on the natural key, while still allowing me to use an ID as the key to the table? Or, would I need to merge the positions manually? (for example: Whenever I want to store a move, would I need to retrieve the positionAfterMove and positionBeforeMove from the database, add the move to movesLeadingToPosition and movesLeadingFromPosition respectively, and then update the positions in the database?)
(context: I'm using Java 1.7, JPA, and a MySQL database)
