使用 Oracle 11g 进行休眠无法与“选择”生成器一起使用

Hibernate with Oracle 11g not working with "select" generator

提问by mushion22

I am using Hibernate 3.2.5 and Hibernate Annotations 3.3.1.GA as the JPA provider in a data loading application. I have configured Hibernate to use C3P0 for connection pooling.

我在数据加载应用程序中使用 Hibernate 3.2.5 和 Hibernate Annotations 3.3.1.GA 作为 JPA 提供程序。我已将 Hibernate 配置为使用 C3P0 进行连接池。

My database is: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

我的数据库是：Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

As there is no built in hibernate dialect for 11g, so I have configured it to use

由于 11g 没有内置的休眠方言，所以我已将其配置为使用

org.hibernate.dialect.Oracle10gDialect

JDBC Driver: Oracle JDBC driver, version: 11.2.0.1.0

JDBC驱动：Oracle JDBC驱动，版本：11.2.0.1.0

The application loads some transaction performance logs from a mainframe system into an Oracle DB for later analysis and reporting. It is essentially a batch job that monitors a folder and waits for a new file then reads it and inserts it into the database (averages around 4.5million rows inserted per day), thus I chose Hibernate due to its ability to use JDBC batch inserts which appeared to not work so well in EclipseLink after some comparison testing. The files are in a proprietary binary format thus I cannot use simpler tools such as CSV imports etc.

该应用程序将一些事务性能日志从大型机系统加载到 Oracle DB 中，以便以后进行分析和报告。它本质上是一个批处理作业，它监视一个文件夹并等待一个新文件，然后读取它并将其插入到数据库中（平均每天插入约 450 万行），因此我选择了 Hibernate，因为它能够使用 JDBC 批量插入经过一些比较测试后，它在 EclipseLink 中的效果似乎不太好。这些文件采用专有的二进制格式，因此我无法使用更简单的工具，例如 CSV 导入等。

Originally I developed the application for use with MySQL on my workstation as it was originally for a once of analysis task, but now wish to move it to an enterprise Oracle RAC platform as it has proved to be useful to continue to continue importing data and retaining it for a couple of months for use by myself and a few other analysts. I have had a DBA configure the tables and have adjusted my Entity classes to reflect some minor changes in field names and data types and changed the driver and connection details etc, but I have run into some issues with primary key generation.

最初我在我的工作站上开发了与 MySQL 一起使用的应用程序，因为它最初是用于一次分析任务，但现在希望将其移动到企业 Oracle RAC 平台，因为它已被证明对继续导入数据和保留数据很有用它使用了几个月，供我自己和其他一些分析师使用。我让 DBA 配置了表并调整了我的实体类以反映字段名称和数据类型的一些细微变化，并更改了驱动程序和连接详细信息等，但是我遇到了一些主键生成问题。

There a few tables (main data table with some tables storing various supporting types eg transaction type, usercodes etc). Each has a unique (primary) id column which is auto-generated using a sequence and before-update trigger.

有几个表（主数据表，其中一些表存储各种支持类型，例如交易类型、用户代码等）。每个都有一个唯一的（主要）id 列，它是使用序列和更新前触发器自动生成的。

The DBA has configured the sequences to not be viewable by the users they have created.

DBA 已将序列配置为无法被他们创建的用户查看。

Using the JPA (javax.annotations) generatedvalue types would not work in any case.

使用 JPA (javax.annotations) 生成的值类型在任何情况下都不起作用。

eg:

例如：

@GeneratedValue(strategy = GenerationType.AUTO)

This gives the SQL:

这给出了 SQL：

select hibernate_sequence.nextval from dual

Which the Oracle drivers throws an exception for with the error:

Oracle 驱动程序抛出异常并显示错误：

25/11/2009 11:57:23 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 2289, SQLState: 42000
25/11/2009 11:57:23 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ORA-02289: sequence does not exist

After finding that I did some research and found the options to use the Hibernate JPA annotation extensions "GenericGenerator" with a "select" strategy (http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-declaration-id-generator)

在发现我做了一些研究并找到了使用带有“选择”策略的 Hibernate JPA 注释扩展“GenericGenerator”的选项（http://docs.jboss.org/hibernate/stable/core/reference/en/html/ mapping.html#mapping-declaration-id-generator)

eg

例如

@GeneratedValue(generator="id_anEntity")
@GenericGenerator(name = "id_anEntity",
strategy = "select")

However when I use this I find that Hibernate hangs during EntityManagerFactory creation. It appears to get past building the properties, building the named queries, connecting to the server, then hangs at:

但是，当我使用它时，我发现 Hibernate 在 EntityManagerFactory 创建期间挂起。它似乎通过构建属性，构建命名查询，连接到服务器，然后挂在：

25/11/2009 1:40:50 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory

and doesn't return.

并且不返回。

I found the same thing happened when I didn't specify the dialect in the persistence.xml file.

我发现当我没有在 persistence.xml 文件中指定方言时会发生同样的事情。

It works fine if I use the "increment" strategy, although this means the sequences are then broken as the value has been incremented without the sequence having been incremented, which is less-than-ideal.

如果我使用“增量”策略，它可以正常工作，尽管这意味着序列会被破坏，因为值已经增加而序列没有增加，这不太理想。

The "native" strategy gives the same output as using GenerationType.AUTO (ORA-02289: sequence does not exist).

“本机”策略提供与使用 GenerationType.AUTO 相同的输出（ORA-02289：序列不存在）。

I am not sure if this is due to me using the wrong key generation strategy, or an error in my configuration, or a bug.

我不确定这是由于我使用了错误的密钥生成策略，还是我的配置错误或错误。

Any help in either making the "select" strategy work, or a better alternative is much appreciated. I could potentially go back to using pure JDBC with prepared statements and such but this tends to get a little messy and I prefer the JPA approach.

非常感谢在使“选择”策略起作用或更好的替代方案方面的任何帮助。我可能会重新使用带有准备好的语句等的纯 JDBC ，但这往往会变得有点混乱，我更喜欢 JPA 方法。

Some more info:

更多信息：

Persistence.xml properties:

Persistence.xml 属性：

        <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.c3p0.min_size" value="5"/>
        <property name="hibernate.c3p0.max_size" value="20"/>
        <property name="hibernate.c3p0.timeout" value="1800"/>
        <property name="hibernate.c3p0.max_statements" value="100000"/>
        <property name="hibernate.jdbc.use_get_generated_keys" value="true"/>
        <property name="hibernate.cache.use_query_cache" value="false"/>
        <property name="hibernate.cache.use_second_level_cache" value="false"/>
        <property name="hibernate.order_inserts" value="true"/>
        <property name="hibernate.order_updates" value="true"/>
        <property name="hibernate.connection.username" value="myusername"/>
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>
        <property name="hibernate.connection.password" value="mypassword"/>
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
        <property name="hibernate.connection.url" value="jdbc:oracle:thin:@(DESCRIPTION =
    (ADDRESS      = (PROTOCOL = TCP) (HOST = myoracleserver) (PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = myservicename))
  )"/>
        <property name="hibernate.jdbc.batch_size" value = "100000" />

A sample of the declaration of the ID field in one of the entity classes using annotations:

使用注释的实体类之一中的 ID 字段声明示例：

@Entity
@Table(name = "myentity",
catalog = "",
schema = "mydb")
public class myEntity implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @GeneratedValue(generator="id_anEntity")
    @GenericGenerator(name = "id_anEntity",
    strategy = "select")
    @Column(name = "MYENTITYID",
    nullable = false)
    private Integer myEntityID;

   //... other column mappings

    public Integer getMyEntityID() {
        return myEntityID;
    }

    public void setMyEntityID(Integer myEntityID) {
        this. myEntityID = myEntityID;
    }

   //... other getters & setters
}

回答by ChssPly76

I'm a bit unclear on what you mean by "The DBA has configured the sequences to not be viewable by the users they have created." - does that mean that the sequence not visible to you? Why not?

我有点不清楚您所说的“DBA 已将序列配置为他们创建的用户无法查看”的意思。- 这是否意味着序列对您不可见？为什么不？

In order to use sequence-based generator where sequence name is not "hibernate_sequence" (which it never is in real life; that's just the default) you need to specify the appropriate generator:

为了使用基于序列的生成器，其中序列名称不是“hibernate_sequence”（它在现实生活中从未出现过；这只是默认值），您需要指定适当的生成器：

@SequenceGenerator(name="myentity_seq", sequenceName="my_sequence")
public class MyEntity {
 ...

 @Id
 @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="myentity_seq")
 private Integer myEntityID;
 ...
}

"select" generator strategy means Hibernate will try to select the row you've just inserted using a unique key (other than PK, obviously). Do you have that defined? I would stronglysuggest you go with sequence instead.

“选择”生成器策略意味着 Hibernate 将尝试使用唯一键（显然不是 PK）选择您刚刚插入的行。你有这个定义吗？我强烈建议你改用序列。