Saturday, December 3, 2016

Hibernate - Cannot insert explicit value for identity column in table 'MYTABLE' when IDENTITY_INSERT is set to OFF


When using Hibernate targeting an SQL Server database could happen to have the error "Cannot insert explicit value for identity column in table 'MYTABLE' when IDENTITY_INSERT is set to OFF".
Here is how to deal with that.


This happens because you used an auto increment field as ID for the table. It means that you don't have to specify an ID when inserting a row in the table.
But sometimes you still have to specify a value for ID, i.e. in case of data import.
In this case you have to explicitly tell hibernate to allow inserting a custom value for ID.

The following example supposes that you already retrieved an Hibernate Session named "session" and you have your Hibernate Entity (the table you mapped) named "entity":

final String phisycalTableName = entity.getClass().getAnnotation(Table.class).name();

session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        connection.setAutoCommit(false);
        Statement statement = connection.createStatement();
        statement.execute(String.format("SET IDENTITY_INSERT %s ON", phisycalTableName));
        session.saveOrUpdate(entity);
        session.flush();
        statement.execute(String.format("SET IDENTITY_INSERT %s OFF", phisycalTableName));
    }
});

You will put this code snippet between an hibernate transaction, that you will commit or rollback as you normally would do.
The code takes the Table annotation of the entity to retrieve phisycal table name, if you don't have an @Table annotation on your entity you can specify the phisycal table name directly or in another way.

1 comment:

(c) Copyright 2020 - MyTroubleshooting.com