OpenJPA › OpenJPA Users

ID field auto generation issue for multiple databases supporting

7 messages Options
Embed this post
Permalink
wang yu-3

ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Hello,
I found If I user derby, I need to use
"@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
And for oracle database, I need to use
"@GeneratedValue(strategy=GenerationType.SEQUENCE)".

The question is if I want to use same entity classes to support both
derby and oracle, how should I do?
Do we have best practice for ID field value generation strategy for
multiple databases supporting?

Regards,
Yu Wang
Miłosz Tylenda

Re: ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Yu Wang,

Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using.

Cheers,
Milosz

> Hello,
> I found If I user derby, I need to use
> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
> And for oracle database, I need to use
> "@GeneratedValue(strategy=GenerationType.SEQUENCE)".
>
> The question is if I want to use same entity classes to support both
> derby and oracle, how should I do?
> Do we have best practice for ID field value generation strategy for
> multiple databases supporting?
>
> Regards,
> Yu Wang
>
wang yu-3

Re: ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Milosz,

I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works
fine for oracle but doesn't work for Derby.

Regards,
Yu Wang

On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda<[hidden email]> wrote:

> Yu Wang,
>
> Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using.
>
> Cheers,
> Milosz
>
>> Hello,
>> I found If I user derby, I need to use
>> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
>> And for oracle database, I need to use
>> "@GeneratedValue(strategy=GenerationType.SEQUENCE)".
>>
>> The question is if I want to use same entity classes to support both
>> derby and oracle, how should I do?
>> Do we have best practice for ID field value generation strategy for
>> multiple databases supporting?
>>
>> Regards,
>> Yu Wang
>>
>
Miłosz Tylenda

Re: ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Yu Wang,

Can we see your entity with "@GeneratedValue(strategy=GenerationType.AUTO)" and the stack trace you are receiving with Derby?

Greetings,
Milosz


> Milosz,
>
> I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works
> fine for oracle but doesn't work for Derby.
>
> Regards,
> Yu Wang
>
> On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda wrote:
> > Yu Wang,
> >
> > Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using.
> >
> > Cheers,
> > Milosz
> >
> >> Hello,
> >> I found If I user derby, I need to use
> >> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
> >> And for oracle database, I need to use
> >> "@GeneratedValue(strategy=GenerationType.SEQUENCE)".
> >>
> >> The question is if I want to use same entity classes to support both
> >> derby and oracle, how should I do?
> >> Do we have best practice for ID field value generation strategy for
> >> multiple databases supporting?
> >>
> >> Regards,
> >> Yu Wang
> >>
> >
>
wang yu-3

Re: ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Hello Milosz,
I found OpenJpa created table OPENJPA_SEQUENCE_TABLE automatically in
the runtime, following is a fragment of my log file:

44048  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 3362899> executing prepstmnt 25906379 SELECT
SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
WITH RR [params=(int) 0]
44047  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 3362899> [0 ms] spent
44063  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 3991333> executing prepstmnt 15975876 INSERT INTO
xxx.OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?)
[params=(int) 0, (int) 1]
44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 3991333> [15 ms] spent
44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 28779126> executing prepstmnt 3112190 SELECT
SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
WITH RR [params=(int) 0]
44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 28779126> [0 ms] spent
44094  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 28779126> executing prepstmnt 20716393 UPDATE
xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
SEQUENCE_VALUE = ? [params=(long) 51, (int) 0, (long) 1]
44094  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 28779126> [0 ms] spent
44188  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 19282695> executing prepstmnt 13686851 SELECT
SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
WITH RR [params=(int) 0]
44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 19282695> [0 ms] spent
44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 19282695> executing prepstmnt 29070643 UPDATE
xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
SEQUENCE_VALUE = ? [params=(long) 101, (int) 0, (long) 51]
44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL - <t
20507793, conn 19282695> [0 ms] spent
<2009-05-17 17:04:11,921> <ERROR>
<com.xxx.xxx.ds.Persister.persist:227> <Persistence-thread-0> <The
transaction has been rolled back.  See the nested exceptions for
details on the errors that
occurred.@agent(key=Domain2:machine0:0,machine0;jsessionid=E803F3478172D6F37A78EC6C02A96AE3)>
<openjpa-1.2.1-r752877:753278 fatal store error>
org.apache.openjpa.persistence.RollbackException: The transaction has
been rolled back.  See the nested exceptions for details on the errors
that occurred.
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523)
        at com.xxx.xxx.dao.DAOContainer.commitTransaction(DAOContainer.java:195)
        at com.xxx.xxx.ds.Persister.persist(Persister.java:221)
        at com.xxx.xxx.ds.PersistRunner.run(PersistRunner.java:49)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
        at java.lang.Thread.run(Thread.java:619)
Caused by: <openjpa-1.2.1-r752877:753278 fatal general error>
org.apache.openjpa.persistence.PersistenceException: The transaction
has been rolled back.  See the nested exceptions for details on the
errors that occurred.
        at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2163)
        at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2010)
        at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1908)
        at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1826)
        at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
        at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1350)
        at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:877)
        at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:512)
        ... 11 more
Caused by: <openjpa-1.2.1-r752877:753278 nonfatal general error>
org.apache.openjpa.persistence.PersistenceException: 尝试修改标识列“ID”。
{INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME, DOMAIN, ERRNO,
EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE, MSGTYPE, MSGWAITTIME,
PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS, SVCNAME, THREADID,
LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1, state=42Z23]
        at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4232)
        at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4197)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
        at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:74)
        at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:543)
        at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:105)
        at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
        at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:717)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
        at org.apache.openjpa.datacache.DataCacheStoreManager.flush(DataCacheStoreManager.java:562)
        at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
        ... 18 more
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
尝试修改标识列“ID”。 {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME,
DOMAIN, ERRNO, EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE,
MSGTYPE, MSGWAITTIME, PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS,
SVCNAME, THREADID, LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1,
state=42Z23]
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:201)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:57)
        at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:228)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:164)
        at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:164)
        at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1531)
        at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:153)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.prepareStatement(PreparedStatementManagerImpl.java:160)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:114)
        at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:82)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
        at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
        ... 27 more



Regards,
Yu Wang







On Wed, Jun 17, 2009 at 3:25 PM, Miłosz Tylenda<[hidden email]> wrote:

> Yu Wang,
>
> Can we see your entity with "@GeneratedValue(strategy=GenerationType.AUTO)" and the stack trace you are receiving with Derby?
>
> Greetings,
> Milosz
>
>
>> Milosz,
>>
>> I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works
>> fine for oracle but doesn't work for Derby.
>>
>> Regards,
>> Yu Wang
>>
>> On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda wrote:
>> > Yu Wang,
>> >
>> > Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using.
>> >
>> > Cheers,
>> > Milosz
>> >
>> >> Hello,
>> >> I found If I user derby, I need to use
>> >> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
>> >> And for oracle database, I need to use
>> >> "@GeneratedValue(strategy=GenerationType.SEQUENCE)".
>> >>
>> >> The question is if I want to use same entity classes to support both
>> >> derby and oracle, how should I do?
>> >> Do we have best practice for ID field value generation strategy for
>> >> multiple databases supporting?
>> >>
>> >> Regards,
>> >> Yu Wang
>> >>
>> >
>>
>
Miłosz Tylenda

Re: ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Hi Yu Wang,

The error message seems to say "Attempt to modify an identity column 'ID'". Probably Derby does not allow you to insert a value to an identity column. I suspect you have created the table when you had GenerationType.IDENTITY in your entity. The table has been created with an identity column. Then you changed the annotation to GenerationType.AUTO which made OpenJPA use a sequence. However, the table definition did not change automatically and OpenJPA tries to insert an explicit value to the identity column. Try to drop the table and let OpenJPA create it again (or create it manualy but don't use "GENERATED BY DEFAULT AS IDENTITY" with ID column). Hope this helps.

Regards,
Milosz



> Hello Milosz,
> I found OpenJpa created table OPENJPA_SEQUENCE_TABLE automatically in
> the runtime, following is a fragment of my log file:
>
> 44048  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 25906379 SELECT
> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
> WITH RR [params=(int) 0]
> 44047  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
> 44063  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 15975876 INSERT INTO
> xxx.OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?)
> [params=(int) 0, (int) 1]
> 44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [15 ms] spent
> 44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 3112190 SELECT
> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
> WITH RR [params=(int) 0]
> 44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
> 44094  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 20716393 UPDATE
> xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
> SEQUENCE_VALUE = ? [params=(long) 51, (int) 0, (long) 1]
> 44094  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
> 44188  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 13686851 SELECT
> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
> WITH RR [params=(int) 0]
> 44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
> 44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 29070643 UPDATE
> xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
> SEQUENCE_VALUE = ? [params=(long) 101, (int) 0, (long) 51]
> 44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
>  
>  
>
> org.apache.openjpa.persistence.RollbackException: The transaction has
> been rolled back.  See the nested exceptions for details on the errors
> that occurred.
> at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523)
> at com.xxx.xxx.dao.DAOContainer.commitTransaction(DAOContainer.java:195)
> at com.xxx.xxx.ds.Persister.persist(Persister.java:221)
> at com.xxx.xxx.ds.PersistRunner.run(PersistRunner.java:49)
> at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
> at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
> at java.util.concurrent.FutureTask.run(FutureTask.java:138)
> at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
> at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207)
> at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
> at java.lang.Thread.run(Thread.java:619)
> Caused by:
> org.apache.openjpa.persistence.PersistenceException: The transaction
> has been rolled back.  See the nested exceptions for details on the
> errors that occurred.
> at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2163)
> at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2010)
> at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1908)
> at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1826)
> at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
> at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1350)
> at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:877)
> at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:512)
> ... 11 more
> Caused by:
> org.apache.openjpa.persistence.PersistenceException: 尝?修改标?列“ID”。
> {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME, DOMAIN, ERRNO,
> EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE, MSGTYPE, MSGWAITTIME,
> PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS, SVCNAME, THREADID,
> LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1, state=42Z23]
> at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4232)
> at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4197)
> at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
> at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
> at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
> at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:74)
> at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:543)
> at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:105)
> at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
> at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
> at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
> at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:717)
> at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
> at org.apache.openjpa.datacache.DataCacheStoreManager.flush(DataCacheStoreManager.java:562)
> at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
> ... 18 more
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
> 尝?修改标?列“ID”。 {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME,
> DOMAIN, ERRNO, EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE,
> MSGTYPE, MSGWAITTIME, PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS,
> SVCNAME, THREADID, LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1,
> state=42Z23]
> at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:201)
> at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:57)
> at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:228)
> at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:164)
> at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
> at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:164)
> at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1531)
> at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:153)
> at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.prepareStatement(PreparedStatementManagerImpl.java:160)
> at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:114)
> at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:82)
> at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
> at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
> ... 27 more
>
>
>
> Regards,
> Yu Wang
>
>
>
>
>
>
>
> On Wed, Jun 17, 2009 at 3:25 PM, Miłosz Tylenda wrote:
> > Yu Wang,
> >
> > Can we see your entity with "@GeneratedValue(strategy=GenerationType.AUTO)" and the stack trace you are receiving with Derby?
> >
> > Greetings,
> > Milosz
> >
> >
> >> Milosz,
> >>
> >> I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works
> >> fine for oracle but doesn't work for Derby.
> >>
> >> Regards,
> >> Yu Wang
> >>
> >> On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda wrote:
> >> > Yu Wang,
> >> >
> >> > Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using.
> >> >
> >> > Cheers,
> >> > Milosz
> >> >
> >> >> Hello,
> >> >> I found If I user derby, I need to use
> >> >> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
> >> >> And for oracle database, I need to use
> >> >> "@GeneratedValue(strategy=GenerationType.SEQUENCE)".
> >> >>
> >> >> The question is if I want to use same entity classes to support both
> >> >> derby and oracle, how should I do?
> >> >> Do we have best practice for ID field value generation strategy for
> >> >> multiple databases supporting?
> >> >>
> >> >> Regards,
> >> >> Yu Wang
> >> >>
> >> >
> >>
> >
>
wang yu-3

Re: ID field auto generation issue for multiple databases supporting

Reply Threaded More More options
Print post
Permalink
Hi Milosz,
Yes, you're right.  And I have checked the source code, the AUTO is
total identical with TABLE in OpenJPA.

Thank you for your patient help!

Regards,
Yu Wang

On Wed, Jun 17, 2009 at 6:02 PM, Miłosz Tylenda<[hidden email]> wrote:

> Hi Yu Wang,
>
> The error message seems to say "Attempt to modify an identity column 'ID'". Probably Derby does not allow you to insert a value to an identity column. I suspect you have created the table when you had GenerationType.IDENTITY in your entity. The table has been created with an identity column. Then you changed the annotation to GenerationType.AUTO which made OpenJPA use a sequence. However, the table definition did not change automatically and OpenJPA tries to insert an explicit value to the identity column. Try to drop the table and let OpenJPA create it again (or create it manualy but don't use "GENERATED BY DEFAULT AS IDENTITY" with ID column). Hope this helps.
>
> Regards,
> Milosz
>
>
>
>> Hello Milosz,
>> I found OpenJpa created table OPENJPA_SEQUENCE_TABLE automatically in
>> the runtime, following is a fragment of my log file:
>>
>> 44048  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 25906379 SELECT
>> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
>> WITH RR [params=(int) 0]
>> 44047  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
>> 44063  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 15975876 INSERT INTO
>> xxx.OPENJPA_SEQUENCE_TABLE (ID, SEQUENCE_VALUE) VALUES (?, ?)
>> [params=(int) 0, (int) 1]
>> 44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [15 ms] spent
>> 44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 3112190 SELECT
>> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
>> WITH RR [params=(int) 0]
>> 44078  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
>> 44094  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 20716393 UPDATE
>> xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
>> SEQUENCE_VALUE = ? [params=(long) 51, (int) 0, (long) 1]
>> 44094  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
>> 44188  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 13686851 SELECT
>> SEQUENCE_VALUE FROM xxx.OPENJPA_SEQUENCE_TABLE WHERE ID = ? FOR UPDATE
>> WITH RR [params=(int) 0]
>> 44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
>> 44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  executing prepstmnt 29070643 UPDATE
>> xxx.OPENJPA_SEQUENCE_TABLE SET SEQUENCE_VALUE = ? WHERE ID = ? AND
>> SEQUENCE_VALUE = ? [params=(long) 101, (int) 0, (long) 51]
>> 44203  xxx  TRACE  [Persistence-thread-0] openjpa.jdbc.SQL -  [0 ms] spent
>>
>>
>>
>> org.apache.openjpa.persistence.RollbackException: The transaction has
>> been rolled back.  See the nested exceptions for details on the errors
>> that occurred.
>>       at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:523)
>>       at com.xxx.xxx.dao.DAOContainer.commitTransaction(DAOContainer.java:195)
>>       at com.xxx.xxx.ds.Persister.persist(Persister.java:221)
>>       at com.xxx.xxx.ds.PersistRunner.run(PersistRunner.java:49)
>>       at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
>>       at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
>>       at java.util.concurrent.FutureTask.run(FutureTask.java:138)
>>       at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
>>       at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:207)
>>       at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
>>       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
>>       at java.lang.Thread.run(Thread.java:619)
>> Caused by:
>> org.apache.openjpa.persistence.PersistenceException: The transaction
>> has been rolled back.  See the nested exceptions for details on the
>> errors that occurred.
>>       at org.apache.openjpa.kernel.BrokerImpl.newFlushException(BrokerImpl.java:2163)
>>       at org.apache.openjpa.kernel.BrokerImpl.flush(BrokerImpl.java:2010)
>>       at org.apache.openjpa.kernel.BrokerImpl.flushSafe(BrokerImpl.java:1908)
>>       at org.apache.openjpa.kernel.BrokerImpl.beforeCompletion(BrokerImpl.java:1826)
>>       at org.apache.openjpa.kernel.LocalManagedRuntime.commit(LocalManagedRuntime.java:81)
>>       at org.apache.openjpa.kernel.BrokerImpl.commit(BrokerImpl.java:1350)
>>       at org.apache.openjpa.kernel.DelegatingBroker.commit(DelegatingBroker.java:877)
>>       at org.apache.openjpa.persistence.EntityManagerImpl.commit(EntityManagerImpl.java:512)
>>       ... 11 more
>> Caused by:
>> org.apache.openjpa.persistence.PersistenceException: 尝?修改标?列“ID”。
>> {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME, DOMAIN, ERRNO,
>> EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE, MSGTYPE, MSGWAITTIME,
>> PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS, SVCNAME, THREADID,
>> LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1, state=42Z23]
>>       at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4232)
>>       at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4197)
>>       at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
>>       at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:88)
>>       at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:64)
>>       at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:74)
>>       at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:543)
>>       at org.apache.openjpa.jdbc.kernel.ConstraintUpdateManager.flush(ConstraintUpdateManager.java:105)
>>       at org.apache.openjpa.jdbc.kernel.BatchingConstraintUpdateManager.flush(BatchingConstraintUpdateManager.java:59)
>>       at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:89)
>>       at org.apache.openjpa.jdbc.kernel.AbstractUpdateManager.flush(AbstractUpdateManager.java:72)
>>       at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.flush(JDBCStoreManager.java:717)
>>       at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
>>       at org.apache.openjpa.datacache.DataCacheStoreManager.flush(DataCacheStoreManager.java:562)
>>       at org.apache.openjpa.kernel.DelegatingStoreManager.flush(DelegatingStoreManager.java:130)
>>       ... 18 more
>> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException:
>> 尝?修改标?列“ID”。 {INSERT INTO xxx.MON_SERVICE (ID, ATTRIBUTES, CPUTIME,
>> DOMAIN, ERRNO, EXECTIME, TUXGROUP, MACHINE, MSGQUEUED, MSGSIZE,
>> MSGTYPE, MSGWAITTIME, PID, PROCESSNAME, PROCTYPE, SERVERID, STATUS,
>> SVCNAME, THREADID, LOGTIME, xxxVERSION, URCODE) VALUES (?, ?, ?, ?, ?,
>> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} [code=-1,
>> state=42Z23]
>>       at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:201)
>>       at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$000(LoggingConnectionDecorator.java:57)
>>       at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection.prepareStatement(LoggingConnectionDecorator.java:228)
>>       at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:164)
>>       at org.apache.openjpa.lib.jdbc.ConfiguringConnectionDecorator$ConfiguringConnection.prepareStatement(ConfiguringConnectionDecorator.java:140)
>>       at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:164)
>>       at org.apache.openjpa.jdbc.kernel.JDBCStoreManager$RefCountConnection.prepareStatement(JDBCStoreManager.java:1531)
>>       at org.apache.openjpa.lib.jdbc.DelegatingConnection.prepareStatement(DelegatingConnection.java:153)
>>       at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.prepareStatement(PreparedStatementManagerImpl.java:160)
>>       at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:114)
>>       at org.apache.openjpa.jdbc.kernel.BatchingPreparedStatementManagerImpl.flushAndUpdate(BatchingPreparedStatementManagerImpl.java:82)
>>       at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushInternal(PreparedStatementManagerImpl.java:89)
>>       at org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flush(PreparedStatementManagerImpl.java:72)
>>       ... 27 more
>>
>>
>>
>> Regards,
>> Yu Wang
>>
>>
>>
>>
>>
>>
>>
>> On Wed, Jun 17, 2009 at 3:25 PM, Miłosz Tylenda wrote:
>> > Yu Wang,
>> >
>> > Can we see your entity with "@GeneratedValue(strategy=GenerationType.AUTO)" and the stack trace you are receiving with Derby?
>> >
>> > Greetings,
>> > Milosz
>> >
>> >
>> >> Milosz,
>> >>
>> >> I have tried "@GeneratedValue(strategy=GenerationType.AUTO)", it works
>> >> fine for oracle but doesn't work for Derby.
>> >>
>> >> Regards,
>> >> Yu Wang
>> >>
>> >> On Sat, Jun 13, 2009 at 7:19 PM, Miłosz Tylenda wrote:
>> >> > Yu Wang,
>> >> >
>> >> > Try using "@GeneratedValue(strategy=GenerationType.AUTO)". This should automatically pick up either IDENTITY or SEQUENCE depending on the database you are using.
>> >> >
>> >> > Cheers,
>> >> > Milosz
>> >> >
>> >> >> Hello,
>> >> >> I found If I user derby, I need to use
>> >> >> "@GeneratedValue(strategy=GenerationType.IDENTITY)" for ID field.
>> >> >> And for oracle database, I need to use
>> >> >> "@GeneratedValue(strategy=GenerationType.SEQUENCE)".
>> >> >>
>> >> >> The question is if I want to use same entity classes to support both
>> >> >> derby and oracle, how should I do?
>> >> >> Do we have best practice for ID field value generation strategy for
>> >> >> multiple databases supporting?
>> >> >>
>> >> >> Regards,
>> >> >> Yu Wang
>> >> >>
>> >> >
>> >>
>> >
>>
>
Free Embeddable Forum Powered by Nabble Help