mysql sequencegenerator,使用MySQL和Oracle进行Hibernate自动密钥生成

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.

解决方案

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:

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

Another way is to use a custom table generator.

First you define an Identifiable interface:

public interface Identifiable {

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 as explained in this article.

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;