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

Question

Hello there.

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.

My database is: 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

org.hibernate.dialect.Oracle10gDialect

JDBC Driver: Oracle JDBC driver, version: 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.

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.

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.

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

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

eg:

@GeneratedValue(strategy = GenerationType.AUTO)

This gives the SQL:

select hibernate_sequence.nextval from dual

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

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)

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:

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.

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

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.

Some more info:

Persistence.xml properties:

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

@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
}

Answer 1

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?

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:

@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 strongly suggest you go with sequence instead.