Hibernate auto key creation with MySQL and Oracle

Question

I am working on a Java application that must perform CRUD operations (using Hibernate 4.3.8) in two different databases with the same database schema. There is MySQL (version 5.1.73) and Oracle (11g Express Edition version 11.2.0.2.0 - 64 bit).

Java classes with JPA comments were generated from database tables with Hibernate code generation.

The problem is that now we need to use automatic primary key generation, and MySQL uses GenerationType.IDENTITY, and Oracle uses GenerationType.SEQUENCE. In addition, we need the ability to manually manually set the primary key in some rare cases.

The serial code in the annotated class works with generating an automatic key for both databases, but it fails if the primary key itself is installed.

@GeneratedValue(strategy=GenerationType.AUTO, generator="sequence_generator") @SequenceGenerator(name="sequence_generator", sequenceName="SEQUENCE1") @Column(name = "id", unique = true, nullable = false) public Integer getId() { return this.id; } 

Without annotations @GeneratedValue and @SequenceGenerator, you can manually set the primary key, but automatic generation does not work.

Answer 1

Even if you used GenerationType.AUTO without any specific SEQUENCE parameter, you would not be able to save the assigned identifiers.

There are some workarounds if you are willing to compromise:

• One way is to switch to the assigned identifiers. You can use UUIDs that work for both MySQL and Oracle, and you can also assign values ​​manually.

• Another way is to use your own table generator.

  First you define the identity interface:

   public interface Identifiable<T extends Serializable> { T getId(); } 

  Then you expand the table generator:

   public class AssignedTableGenerator extends TableGenerator { @Override public Serializable generate(SessionImplementor session, Object obj) { if(obj instanceof Identifiable) { Identifiable identifiable = (Identifiable) obj; Serializable id = identifiable.getId(); if(id != null) { return id; } } return super.generate(session, obj); } } 

  This generator can mix assigned identifiers with synthetic ones:

   doInTransaction(session -> { for (int i = 0; i < 5; i++) { session.persist(new AssignTableSequenceIdentifier()); } AssignTableSequenceIdentifier tableSequenceIdentifier = new AssignTableSequenceIdentifier(); tableSequenceIdentifier.id = -1L; session.merge(tableSequenceIdentifier); session.flush(); }); 

  generates the following statements:

   select tbl.next_val from sequence_table tbl where tbl.sequence_name=default for update insert into sequence_table (sequence_name, next_val) values (default,1) update sequence_table set next_val=2 where next_val=1 and sequence_name=default select tbl.next_val from sequence_table tbl where tbl.sequence_name=default for update update sequence_table set next_val=3 where next_val=2 and sequence_name=default select tbl.next_val from sequence_table tbl where tbl.sequence_name=default for update update sequence_table set next_val=4 where next_val=3 and sequence_name=default select tbl.next_val from sequence_table tbl where tbl.sequence_name=default for update update sequence_table set next_val=5 where next_val=4 and sequence_name=default select tbl.next_val from sequence_table tbl where tbl.sequence_name=default for update update sequence_table set next_val=6 where next_val=5 and sequence_name=default select identityvs0_.id as id1_0_0_ from assigneTableIdentifier identityvs0_ where identityvs0_.id=-1 insert into assigneTableIdentifier (id) values (1, 2) insert into assigneTableIdentifier (id) values (2, 4) insert into assigneTableIdentifier (id) values (5, -1) 

For Oracle, you can combine SEQUENCE and assigned generators as described in this article .

In short, given the following generator:

 public class AssignedSequenceStyleGenerator extends SequenceStyleGenerator { @Override public Serializable generate(SessionImplementor session, Object obj) { if(obj instanceof Identifiable) { Identifiable identifiable = (Identifiable) obj; Serializable id = identifiable.getId(); if(id != null) { return id; } } return super.generate(session, obj); } } 

You can match it with your objects as follows:

 @Id @GenericGenerator( name = "assigned-sequence", strategy = "com.vladmihalcea.book.hpjp.hibernate.identifier.AssignedSequenceStyleGenerator", parameters = @org.hibernate.annotations.Parameter( name = "sequence_name", value = "post_sequence" ) ) @GeneratedValue( generator = "assigned-sequence", strategy = GenerationType.SEQUENCE ) private Long id; 

Answer 2

Try something like this:

 @Id @Column( name = "ID" ) @TableGenerator( name = "AppSeqStore", table = "APP_SEQ_STORE", pkColumnName = "APP_SEQ_NAME", pkColumnValue = "LISTENER_PK", valueColumnName = "APP_SEQ_VALUE", initialValue = 1, allocationSize = 1 ) @GeneratedValue( strategy = GenerationType.TABLE, generator = "AppSeqStore" ) 

And this table is in the database:

 CREATE TABLE APP_SEQ_STORE ( APP_SEQ_NAME VARCHAR(255) NOT NULL, APP_SEQ_VALUE NUMBER(10) NOT NULL, PRIMARY KEY(APP_SEQ_NAME) ) INSERT INTO APP_SEQ_STORE VALUES ('LISTENER_PK', 0) 

All this works in Oracle, MS Sql Server and MySql using JBoss as an application server.

Further information here: http://www.developerscrappad.com/408/java/java-ee/ejb3-jpa-3-ways-of-generating-primary-key-through-generatedvalue/