Mssql GenerationType.AUTO

Hibernate ORM
1

Hello we are using 4 database, oracle,mssql-2019, mssql-2022, mysql
Hibernate:6.4.4

@Id
    @GenericGenerator(
        name = "sequenceGenerator",
        type = SequenceStyleGenerator.class ,
        parameters = {
            @org.hibernate.annotations.Parameter(
                name = "optimizer",
                value = "pooled-lo"
            ),
            @org.hibernate.annotations.Parameter(
                name = "initial_value",
                value = "1"
            ),
            @org.hibernate.annotations.Parameter(
                name = "increment_size",
                value = "5"
            )
        }
    )
    @GeneratedValue(
        strategy = GenerationType.AUTO,
        generator = "sequenceGenerator"
    )
    private Long id;

When I insert record to DB it works with mysql and oracle.However it throw exception for mssql-2019 +mssql-2022
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement [Cannot insert explicit value for identity column in table 'X' when IDENTITY_INSERT is set to OFF.] [insert into ledger (.//) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; SQL [....]) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
Yes, i don’t set any IDs manually.
The funny part it doesnt throw exception when inserted X record, it throwException when fetch it.(I verified the records in db)
.
When i change GenerationType as IDENTIFY

@Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

It is starting works properly.
But i don’t want to change GenerationType because as I said we are using 4 db :frowning:
Besides all this why this weird issue happening , any idea ?

2

Read the migration guide which will explain that the default for the AUTO strategy changed for databases that don’t support sequences natively to use TABLE based sequences.

The funny part it doesnt throw exception when inserted X record, it throwException when fetch it.(I verified the records in db)

Since insert statements are held back as long as possible to benefit from JDBC batching, you might only see the statements being executed once you run a SQL select statement which could depend on data that was previously inserted.

But i don’t want to change GenerationType because as I said we are using 4 db :frowning:

All 4 databases support the IDENTITY strategy, so why not use the same strategy on all databases? Either you do that and change the Oracle schema to also use identity columns, or you change the schemas of all other databases to not use identity columns anymore.

1 Like
3

Thanks @beikov for clarification. Yes migration to Identify is solved our problem well.
I was against(For backwards compatibility) to do it but other solutions was much more efforts.