JPA GenerationType.AUTO not considering column with auto increment

Question

I have a table with a simple int id column with Identity auto increment in SQL Server.

The entity's Id is annotated with @Id and @GeneratedValue

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id", length = 4, precision = 10, nullable = false)
private Integer id;

In SQL Server the column is properly set as Identity with Seed and Increment equals to 1.

When I try to persist an instance of this entity, Hibernate tries to query the hibernate_sequence table to obtain the ID value. Since I haven't created that table in my schema I'm getting an error:

could not read a hi value: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MySchema.hibernate_sequence'

If I change the generation type to IDENTITY everything works as expected

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", length = 4, precision = 10, nullable = false)
private Integer id;

I cannot change it this way, since my App will run both on MS SQL and ORACLE, and the latter does not support auto incremented columns.

As far as I know the AUTO type should use the auto increment behaviour if the underlying database has support to it, so I don't know why is not working.

UPDATE:

It took me some time but I was able to understand exactly what is going on.

I am working with legacy databases with the following behaviours:

• MSSQL: id generation uses table IDENTITY
• ORACLE: id generation uses a trigger. The trigger queries and updates a custom table where all the "next ids" are stored. This table is called SEQ.

Here is the outcome of using some id generation strategies:

• AUTO: does not work in MSSQL, as explained above
• IDENTITY: works in MSSQL but is not supported by Oracle
• "native": works in MSSQL but fails in ORACLE. It fails because Hibernate activates its default sequence strategy, which uses hibernate_sequences.nextval. Since this is a legacy application the values from the SEQ table (mentioned above) and the hibernate_sequences are not synchronized (SEQ's value for that particular table is at 6120, and hibernate_sequences' is at 1, which is expected since it was not used until now).

So what I need to figure out is a way to configure that entity to:

• Use MSSQL Identity feature OR
• When using Oracle, do not automatically set any value to the ID variable and leave everything up to the pre-existing trigger

This can cause me serious issues on Oracle when I need to insert entities that depend on the main entity (via foreign key), because Hibernate won't know which ID value was generated by the "external" trigger.

Answer 1

I had a similar problem and found this information (deeper explained in here).

Adding this property into my persistence.xml file fixed the issue:

<property name="hibernate.id.new_generator_mappings" value="false" />