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" />

Answer 2

Orcale 12c supports IDENTITY and SQL SERVER 2012 supports SEQUENCES. I believe a SEQUENCE is always a better choice than an IDENTITY. IDENTITY disables batching and SEQUENCES allow you to provide optimizers, such as the pooled-lo optimization strategy.

This is how the actual identifier generator is chosen for the configured GenerationType value:

switch ( generatorEnum ) {
    case IDENTITY:
        return "identity";
    case AUTO:
        return useNewGeneratorMappings
                ? org.hibernate.id.enhanced.SequenceStyleGenerator.class.getName()
                : "native";
    case TABLE:
        return useNewGeneratorMappings
                ? org.hibernate.id.enhanced.TableGenerator.class.getName()
                : MultipleHiLoPerTableGenerator.class.getName();
    case SEQUENCE:
        return useNewGeneratorMappings
                ? org.hibernate.id.enhanced.SequenceStyleGenerator.class.getName()
                : "seqhilo";
}

• If you use the new identifier generators:

  properties.put("hibernate.id.new_generator_mappings", "true");

  The AUTO will actually use a SequenceStyleGenerator and where the database doesn't support sequences, you end up using a TABLE generator instead (which is a portable solution but it's less efficient than IDENTITY or SEQUENCE).

• If you use the legacy identifier generators, you then end up with the "native" generation strategy, meaning:

  public Class getNativeIdentifierGeneratorClass() {
      if ( supportsIdentityColumns() ) {
          return IdentityGenerator.class;
      }
      else if ( supportsSequences() ) {
          return SequenceGenerator.class;
      }
      else {
          return TableHiLoGenerator.class;
      }
  }   
  

If a new Oracle12gDialect is going to be added and it will support IDENTITY, then AUTO might switch to IDENTITY rather than SEQUENCE, possibly breaking your current expectations. Currently there is no such dialect available so on Oracle you have SEQUENCE and in MSSQL you have IDENTITY.

Conclusion:

Try it like this:

 @Id
 @GenericGenerator(name = "native_generator", strategy = "native")
 @GeneratedValue(generator = "native_generator")
 private Long id;

• make the id a Long instead of Integer, and you can let the HBMDDL handle the primary key column type.
• force Hibernate to use the "native" generator

If your legacy system uses a table for generating sequence values and there was no hilo optimization ever used you can use a table identifier generator:

@Id
@GeneratedValue(generator = "table", strategy=GenerationType.TABLE)
@TableGenerator(name = "table", allocationSize = 1
)
private Long id;

You can also use the JPA table generator, just make sure you configure the right optimizer. For more info check my Hibernate tutorial

Answer 3

because

@GeneratedValue(strategy = GenerationType.AUTO)

use SequenceStyleGenerator by default in earlier versions

you have to look at this https://hibernate.atlassian.net/browse/HHH-11014