使用MySQL和Oracle进行Hibernate自动密钥生成 [英] Hibernate auto key generation with MySQL and Oracle
问题描述
我正在研究一个 Java 应用程序，它应该在具有相同数据库模式的两个不同数据库上执行CRUD操作（使用 Hibernate 4.3.8）。
有一个 MySQL （版本5.1.73）和一个 Oracle （11g Express版本11.2.0.2.0 - 64位）数据库。
具有 JPA批注的Java类是使用Hibernate Code Generation从数据库表中生成的。
问题是我们现在需要使用自动主键生成，MySQL使用 GenerationType.IDENTITY ，而Oracle使用 GenerationType.SEQUENCE 。此外，我们需要能够在极少数情况下自行手动设置主键。
注释类中的followig代码适用于两个数据库的自动密钥生成，但失败，如果一个主键是自设的。
$ $ p $ $ $ $ c $ @GeneratedValue（strategy = GenerationType.AUTO，generator =sequence_generator）
@SequenceGenerator（name =sequence_generator，sequenceName =SEQUENCE1）
@Column（name =id，unique = true，nullable = false）
public Integer getId（）{
返回this.id;
}
如果没有 @GeneratedValue 和 @ @ SequenceGenerator 注释可以手动设置主键，但自动生成不起作用。
如果您使用 GenerationType.AUTO 而没有任何SEQUENCE特定参数，则无法保存指定的标识符。
有一些解决方法，如果您愿意做出一些折衷：
-
一种方法是切换到指定的标识符。您可以使用 UUID 标识符，这两种标识符都适用于MySQL和Oracle，您也可以手动赋值。另一种方法是使用自定义表生成器。
首先你定义一个可识别的接口：
public interface可识别的< T extends Serializable> {
T getId（）;
}
然后扩展表生成器：
public class AssignedTableGenerator扩展TableGenerator {
$ b $ @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）;
该生成器能够将分配的标识符与合成生成的：
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（）;
}）;
生成以下语句：
<$ p $
从sequence_table tbl中选择tbl.next_val其中tbl.sequence_name =更新默认值
插入sequence_table（sequence_name，next_val）值（默认值为1）
update sequence_table set next_val = 2其中next_val = 1且sequence_name = default
从sequence_table tbl中选择tbl.next_val，其中tbl.sequence_name =缺省值用于更新
update sequence_table set next_val = 3 where next_val = 2 and sequence_name = default
从sequence_table tbl中选择tbl.next_val，其中tbl.sequence_name =缺省值用于更新
update sequence_table set next_val = 4 where next_val = 3 and sequence_name = default
从sequence_table tbl中选择tbl.next_val，其中tbl.sequence_name = default for update
update sequence_table set next_val = 5 where next_val = 4 and sequence_name = default
从sequence_table tbl中选择tbl.next_val，其中tbl.sequence_name =缺省值用于更新
update sequence_tabl e set next_val = 6 where next_val = 5 and sequence_name = default
将identityvs0_.id选为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）
对于Oracle，您可以按照本文。
总之，考虑以下生成器：
public class AssignedSequenceStyleGenerator
extends SequenceStyleGenerator {
$ b $ @Override
public Serializable generate（SessionImplementor session，
Object obj）{
if（obj instanceof Identifiable）{
可识别的可识别的=（可识别的）obj;
Serializable id = identifiable.getId（）;
if（id！= null）{
return id;
}
}
return super.generate（session，obj）;
$ / code $ / pre
您可以按如下方式将它映射到您的实体： / b>
@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 ;
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 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 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;
这篇关于使用MySQL和Oracle进行Hibernate自动密钥生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
