Hibernate: ID generator using increment and Oracle Schema

Question

I am using Hiberbnate 3.1.3. I have a mapping as below and when I try to insert a record into the TEST_TABLE, I get an Exception: 'Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not fetch initial value for increment generator'

<class name="com.test.app.to.TestTable" table="TEST_TABLE" schema="TEST">
        <id name="testId" type="long">
            <column name="TEST_ID" precision="12" scale="0" />
            <generator class="increment"></generator>
        </id>
</class>

I have default schema set as below in the cfg.xml as I need to use tables from the OTHER_SCHEMA as well in my application.

<property name="hibernate.default_schema">OTHER_SCHEMA</property>

In the above case it seems to be a Hibernate Bug as a read using a TestTable object works fine and uses the 'TEST' schema correctly, but the '<generator class="increment"></generator>' does not use the 'TEST' schema but uses the default 'OTHER_SCHEMA' for getting the max ID. The query generated for max ID reads as below:

Hibernate: select max(TEST_ID) from OTHER_SCHEMA.TEST_TABLE

I am not able to specify a schema for the generator and it is not using the schema="TEST" attribute of the class which I would expect it to use.

How can this issue be resolved?

Answer 1

You can specify schema for your generator using schema parameter:

<generator class="increment">
    <param name="schema">TEST</param>
</generator>

Sadly, this is not well described in Hibernate documentation; you'd have to look at the API javadoc in order to find that out.

That said, Mark is right with regards to "increment" not being very efficient - it's also not safe in a cluster environment.

Answer 2

If the hibernate default schema is OTHER_SCHEMA then if you do not supply a schema for a table then it will automatically add the default

Solution is you have to add a schema for all table names except one the default. In this case you might be better off if you set the hibernate default to your schema.

Looking at the sequence using max(id) like that is not usually efficient, especially in Oracle which has sequences exactly for this use.