December 30, 2015

Composite Primary Key with Generated and Unique Value with JPA 2.0

Introduction

Sometime you need several column for you primary keys. The background for that can very. There are two solution for that in JPA:

  • @IdClass as inline columns
  • @EmbeddedId with extract column in separate class

Databas Design

  _____________
 |             |
 |   Student   |
 |_____________|
 |             |
 | *studentId  |
 | *groupId    |
 |  firstName  |
 |             |
 |_____________|

@IdClass

@EmbeddedId

The StudentPK class remains the same.

Conclusion

The two above option is very much the same and I cannot see any clear advantage for one solution over the other, so I would recommend use the solution that resembles the real world, i.e. SQL and use IdClass.

Automatic Generated Values

When having composite primary key you cannot use @GeneratedValue, but you can use @TableGenerator. Remember there are three strategies for automatic primary key generation values and the only database vendor neutral solution is TABLE.

  • @GeneratedValue(strategy = IDENTITY), with automatic generated value on column. Works only for MySQL and MS SQL.
  • @GeneratedValue(strategy = SEQUENCE), separate sequence. Works only for PostgreSQL and Oracle.
  • @GeneratedValue(strategy = TABLE), the ONLY database vendor neutral solution. ORM specific implementation, does not rely on any database specific technique. Uses separate table to persist increment value and handles increment by it self.

The StudentPK class remains the same.

Which will generate the following database.

    _____________        _____________
   |             |      |             |
   |   Student   |      |   SEQ_TABLE |
   |_____________|      |_____________|
   |             |      |             |
   | *studentId  |      |  SEQ_NAME   |
   | *groupId    |      |  SEQ_COUNT  |
   |  firstName  |      |             |
   |_____________|      |_____________|

NOTE You do not need to have automatic generated values for both columns.

NOTE You do not need to have unique constraint for id column, I just added it to show that you can, because sometimes you want to have it.

No comments: