4

I want to run this query from JPA from my code. But its not working.

Please help.

SET IDENTITY_INSERT "+tableName+" ON

UPDATE

The exact code line is this

Query query = entityManager.createNativeQuery("SET IDENTITY_INSERT   [tableName] ON");
int updated = query.executeUpdate();

It gives 0 for the updated. Does not give any error on this line execution. But when try to insert after this, it gives constraint violation exception like below.

2015-03-09 15:46:52,922 WARN  org.hibernate.util.JDBCExceptionReporter.logExceptions:233 - SQL Error: 544, SQLState: 23000
2015-03-09 15:46:52,923 ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions:234 - Cannot insert explicit value for identity column in table 'table_name' when IDENTITY_INSERT is set to OFF.
2015-03-09 15:46:52,924 ERROR org.hibernate.event.def.AbstractFlushingEventListener.performExecutions:324 - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: could not insert: [com.entities.EntityName]
muasif80
  • 5,586
  • 4
  • 32
  • 45
  • Have you tried using `em.createNativeQuery()`? – Predrag Maric Mar 09 '15 at 15:10
  • Yes I tried that and i executed the code within a transaction wrapping the insert operation between off and on for identity_insert. But it had no effect for some reason. – muasif80 Mar 09 '15 at 15:29
  • I assume all of this is running in the same transaction? – Alan Hay Mar 09 '15 at 15:55
  • Yes. I believe JPA can't run ddl statements like this and I will need to fetch underlying hibernate session and will have to try that. Session session = entityManager.unwrap(Session.class); – muasif80 Mar 09 '15 at 15:57
  • 1
    So this is really http://stackoverflow.com/questions/28941849/jpa-how-to-set-autoincrement-on-an-entity-to-false-temporarily – Neil Stockton Mar 09 '15 at 16:24

2 Answers2

3

This and this have helped me and I have got this working as below.

Also from this link I got the answer that JPA will not support DDL operation.

If anyone can add to this answer, that will be great too.

EntityTransaction tx = entityManager.getTransaction();

try {
// entitiesMap hold the entity class/table name pairs which have autoincrement primary keys in the sql server database
if(entitiesMap.containsKey(entityName)){
    String tableName = entitiesMap.get(entityName);
    Session session = (Session) entityManager.getDelegate();
    session.connection().createStatement().execute("SET IDENTITY_INSERT [dbo]." + tableName + " ON");
}

tx.begin();
entityObject = jpaTemplate.merge(entity);
tx.commit();

if(entitiesMap.containsKey(entityName)){
    String tableName = entitiesMap.get(entityName);
    Session session = (Session) entityManager.getDelegate();
    session.connection().createStatement().execute("SET IDENTITY_INSERT [dbo]." + tableName + " OFF");
}

return entityObject;
} catch (Exception e) {
}finally{
}
Community
  • 1
  • 1
muasif80
  • 5,586
  • 4
  • 32
  • 45
0

You don't want quotes around the table name.

="SET IDENTITY_INSERT "+tableName+" ON"

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22