Hibernate auto key generation with MySQL and Oracle

Question

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

Java classes with JPA annotations were generated from the database tables with Hibernate Code Generation.

The problem is that we now have the need to use auto primary key generation and MySQL uses GenerationType.IDENTITY and Oracle uses GenerationType.SEQUENCE. Furthermore, we need the ability to manually set primary key by ourself in some rare cases.

The followig code in the annotated class works with auto key generation for both databases but fails, if a primary key is self set.

@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 the @GeneratedValue and @SequenceGenerator annotations it is possible to set the primary key manually, but auto generation doesn't work.

Answer 1

Even if you used GenerationType.AUTO without any SEQUENCE specific parameter, you wouldn't be able to save assigned identifiers.

There are some workarounds if you are willing to make some compromises:

1. One way would be to switch to the assigned identifiers. You can use UUID identifiers, which work for both MySQL and Oracle and you can also assign the values manually.

2. Another way is to use a custom table generator.

First you define an Identifiable interface:

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

Then you extend 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 is able to mix assigned identifiers with synthetic generated 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();
    });

generating 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 the SEQUENCE and the assigned generators. In short, considering 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 map it to your entities 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;

All the code is available on GitHub and works like a charm.

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 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)

This all works in Oracle, and MS Sql Server, and MySql using JBoss as the App Server.

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