Oracle Identity issue using with Liquibase and JPA-CodePudding

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 following definition in JPA entity class

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

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

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

ID	DESCRIPTION
1	test

After that, I want to add an 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 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 [org.jboss.jca.adapters.jdbc.jdk8.WrappedPreparedStatementJDK8@5aad7866]
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 stacktrace 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)

Thanks

CodePudding user response：

After inserting a record by giving id with liquibase, altering table with the ALTER TABLE identity_demo MODIFY id NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) command as mentioned on this question updates the sequence generator. So that inserting new records to the table with entity manager can be done without constraint error.