Forum Stats

  • 3,784,025 Users
  • 2,254,873 Discussions
  • 7,880,652 Comments

Discussions

Oracle Identity issue using with Liquibase and JPA

I have a table like below created using liquibase in Oracle and I am inserting a row to this table by giving id as 1

--liquibase formatted sql

--changeset xxx:211202160323-1
CREATE  TABLE identity_demo  (
    id NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    description VARCHAR(100) not null
);

--changeset xxx:211202160323-2
INSERT INTO identity_demo(id, description) VALUES(1,'test');

And I have the following definition in the JPA entity class

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

@Column(name = "description")
private String description;

After this changeset are executed, when I look at the table in Oracle, I see that this row has been added as follows

After that, I want to add another row using JPA entity manager, I am getting the error:

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement

because it's trying to insert the row with an id of 1 but the value with id 1 has already been added by liquibase changeset. My question is why id didn't continue from 2 when I added with JPA entity manager? How can I insert values to this table both by using the entity manager and sometimes by giving the id value manually with the liquibase script?

My liquibase version is 4.3.5 and the Oracle version is 19c.

Hibernate log is:

09:55:08,843 INFO [stdout] (default task-2) into
09:55:08,843 INFO [stdout] (default task-2) identity_demo
09:55:08,843 INFO [stdout] (default task-2) (description)
09:55:08,843 INFO [stdout] (default task-2) values
09:55:08,843 INFO [stdout] (default task-2) (?)
09:55:08,852 TRACE [org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl] (default task-2) Registering statement [[email protected]ad7866]
09:55:08,852 TRACE [org.hibernate.persister.entity.AbstractEntityPersister] (default task-2) Dehydrating entity: [com.test.server.persistence.entity.IdentityDemo#<null>]
09:55:08,852 TRACE [org.hibernate.type.descriptor.sql.BasicBinder] (default task-2) binding parameter [1] as [VARCHAR] - [new record test]

Whole stack trace of error message:

3:32:14,457 ERROR [org.jboss.as.ejb3.invocation] (default task-2) WFLYEJB0034: EJB Invocation failed on component UserManagerBean for method public abstract void com.test.server.security.UserManagerRemote.updateIdentityDemo(com.test.server.persistence.entity.IdentityDemo): javax.ejb.EJBException: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:266)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:388)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:158)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:509)
    at org.jboss.weld.module.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:81)
    at org.jboss.as.weld.ejb.EjbRequestScopeActivationInterceptor.processInvocation(EjbRequestScopeActivationInterceptor.java:89)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.invocationmetrics.WaitTimeInterceptor.processInvocation(WaitTimeInterceptor.java:47)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.security.SecurityContextInterceptor.processInvocation(SecurityContextInterceptor.java:100)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.deployment.processors.StartupAwaitInterceptor.processInvocation(StartupAwaitInterceptor.java:22)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.interceptors.ShutDownInterceptorFactory$1.processInvocation(ShutDownInterceptorFactory.java:64)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.deployment.processors.EjbSuspendInterceptor.processInvocation(EjbSuspendInterceptor.java:57)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.interceptors.LoggingInterceptor.processInvocation(LoggingInterceptor.java:67)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ee.component.NamespaceContextInterceptor.processInvocation(NamespaceContextInterceptor.java:50)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.ContextClassLoaderInterceptor.processInvocation(ContextClassLoaderInterceptor.java:60)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.InterceptorContext.run(InterceptorContext.java:438)
    at org.wildfly.security.manager.WildFlySecurityManager.doChecked(WildFlySecurityManager.java:628)
    at org.jboss.invocation.AccessCheckingInterceptor.processInvocation(AccessCheckingInterceptor.java:57)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:53)
    at org.jboss.as.ee.component.ViewService$View.invoke(ViewService.java:198)
    at org.wildfly.security.auth.server.SecurityIdentity.runAsFunctionEx(SecurityIdentity.java:421)
    at org.jboss.as.ejb3.remote.AssociationImpl.invokeWithIdentity(AssociationImpl.java:619)
    at org.jboss.as.ejb3.remote.AssociationImpl.invokeMethod(AssociationImpl.java:600)
    at org.jboss.as.ejb3.remote.AssociationImpl.lambda$receiveInvocationRequest$0(AssociationImpl.java:217)
    at org.jboss.as.ejb3.remote.AssociationImpl.execute(AssociationImpl.java:310)
    at org.jboss.as.ejb3.remote.AssociationImpl.receiveInvocationRequest(AssociationImpl.java:263)
    at org.jboss.ejb.protocol.remote.EJBServerChannel$ReceiverImpl.handleInvocationRequest(EJBServerChannel.java:473)
    at org.jboss.ejb.protocol.remote.EJBServerChannel$ReceiverImpl.handleMessage(EJBServerChannel.java:208)
    at org.jboss.remoting3.remote.RemoteConnectionChannel.lambda$handleMessageData$3(RemoteConnectionChannel.java:430)
    at org.jboss.remoting3.EndpointImpl$TrackingExecutor.lambda$execute$0(EndpointImpl.java:991)
    at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
    at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1990)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1486)
    at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1377)
    at org.xnio.XnioWorker$WorkerThreadFactory$1$1.run(XnioWorker.java:1280)
    at java.lang.Thread.run(Thread.java:748)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
    at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:917)
    at org.hibernate.internal.SessionImpl.merge(SessionImpl.java:891)
    at org.jboss.as.jpa.container.AbstractEntityManager.merge(AbstractEntityManager.java:568)
    at com.test.server.persistence.service.IdentityDemoService.createOrUpdate(IdentityDemoService.java:16)
    at com.test.server.security.UserManagerBean.updateIdentityDemo(UserManagerBean.java:188)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:509)
    at org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.delegateInterception(Jsr299BindingsInterceptor.java:79)
    at org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:89)
    at org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.processInvocation(Jsr299BindingsInterceptor.java:102)
    at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:63)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ee.concurrent.ConcurrentContextInterceptor.processInvocation(ConcurrentContextInterceptor.java:45)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:40)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:53)
    at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:52)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.component.interceptors.AdditionalSetupInterceptor.processInvocation(AdditionalSetupInterceptor.java:54)
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:252)
    ... 47 more
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:59)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
    at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3090)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3683)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:645)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:282)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:263)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:317)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:332)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:289)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:196)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:127)
    at org.hibernate.event.internal.DefaultMergeEventListener.saveTransientEntity(DefaultMergeEventListener.java:287)
    at org.hibernate.event.internal.DefaultMergeEventListener.entityIsTransient(DefaultMergeEventListener.java:259)
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:191)
    at org.hibernate.event.internal.DefaultMergeEventListener.onMerge(DefaultMergeEventListener.java:72)
    at org.hibernate.internal.SessionImpl.fireMerge(SessionImpl.java:905)
    ... 79 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (DSA.SYS_C0030335) violated

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1151)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4877)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)


I also tried the syntax and commands learned from the java cheat sheet.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,026 Red Diamond

    You created "by default" identity column which means you need to keep track and control it. Why? Identity column is tied to system generated sequence. When identity column is GENERATED ALWAYS we can't provide column in insert statement - Oracle always gets identity column sequence next value and uses it as column value. When identity column in GENERATED BY DEFAULT we can issue insert statement with or without identity column. If we issue it with identity column Oracle will use our provided value. If we don't provide identity column in insert then Oracle gets identity column sequence next value and uses it as column value. And since in your case GENERATED BY DEFAULT identity column is PK it becomes your responsibility to make sure explicitly provided values don't collide with sequence generated values. This has nothing to do with Liquibase or JPA. Question is do you really need GENERATED BY DEFAULT? You dictating PK values essentially means column becomes natural key (key value has specific meaning, otherwise why would you need to insert specific value) comparing to surrogate key when using GENERATED ALWAYS.

    SQL> CREATE  TABLE identity_demo  (
      2      id NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      3      description VARCHAR(100) not null
      4  );
    
    Table created.
    
    SQL> insert into identity_demo values(1,'abc');
    
    1 row created.
    
    SQL> insert into identity_demo(description) values('xyz');
    insert into identity_demo(description) values('xyz')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.SYS_C0028985) violated
    
    SQL> DROP TABLE identity_demo PURGE
      2  /
    
    Table dropped.
    
    SQL> CREATE  TABLE identity_demo  (
      2      id NUMBER(19,0) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      3      description VARCHAR(100) not null
      4  );
    
    Table created.
    
    SQL> insert into identity_demo(id,description) values(1,'abc')
      2  /
    insert into identity_demo(id,description) values(1,'abc')
                              *
    ERROR at line 1:
    ORA-32795: cannot insert into a generated always identity column
    
    SQL> insert into identity_demo(description) values('abc')
      2  /
    
    1 row created.
    
    SQL> insert into identity_demo(description) values('xyz')
      2  /
    
    1 row created.
    
    SQL> select  *
      2    from  identity_demo
      3  /
    
            ID DESCRIPTION
    ---------- -----------
             1 abc
             2 xyz
    
    SQL>
    

    SY.