JBoss 6 - hibernate/jpa/oracle issues

[pskrzynski]

Hello,

I've got an application which is deployed on JBoss M3 and has been working perfectly well on it. However when I try to migrate to final release the same application does not work. The problem is that whenever I try to save the record in the Oracle database through JPA and I call persist method on entity manager I receive an exception:

13:10:45,182 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96) [:3.6.0.Final]

    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) [:3.6.0.Final]

    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275) [:3.6.0.Final]

    at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114) [:3.6.0.Final]

...

Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (GETIN8.REQUEST_PARAMETER_PK) violated

    at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:345) [:Oracle JDBC Driver version - "10.2.0.5.0"]

    at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10844) [:Oracle JDBC Driver version - "10.2.0.5.0"]

    at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:774) [:6.0.0.Final]

    at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70) [:3.6.0.Final]

    at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268) [:3.6.0.Final]

    ... 73 more

13:10:45,184 WARN  [com.arjuna.ats.arjuna] ARJUNA-12125 TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffffc0a8167a:397a:4d3eb67d:2e6, org.hibernate.transaction.synchronization.HibernateSynchronizationImpl@68538fd0 >: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

The problem is that when I create an object and try to save it, the id of the object is obtained through sequence. And calling nextval on Oracle sequence through JPA does not work correctly since it returns the id that is already busy, look:

13:10:45,110 INFO  [test] Saving object: Request [requestId=0, operationName=login, userId=12345678, operationDate=Tue Jan 25 13:10:45 CET 2011]

13:10:45,111 INFO  [test] Object saved: Request [requestId=11349, operationName=login, userId=12345678, operationDate=Tue Jan 25 13:10:45 CET 2011]

In the JBoss 6 M3 tha same sequence return values greter by ~30 which is correct. I've just noticed that tha same application and the same JBoss configuration in M3 outputs:

13:08:31,338 INFO  [test] Saving object: Request [requestId=0, operationName=login, userId=12345678, operationDate=Tue Jan 25 13:08:31 CET 2011]

13:08:31,408 INFO  [STDOUT] Hibernate: select request_request_id_seq.nextval from dual

13:08:31,415 INFO  [test] Object saved: Request [requestId=11386, operationName=login, userId=12345678, operationDate=Tue Jan 25 13:08:31 CET 2011]

So as you see the sequence value is obtained through Hibernate through expected SQL query. In final release Hibernate does not print this query to the output despite the fact that in both cases it later prints the insert query in the same manner:

13:10:45,158 INFO  [STDOUT] Hibernate: insert into request (operation_date, operation_name, user_id, request_id) values (?, ?, ?, ?)

I annotate the requestId attribute in the Request class like this:

    @Id

    @Column(name = "request_id")   

    @GeneratedValue(generator="request_increment")

    @SequenceGenerator(name="request_increment", sequenceName = "request_request_id_seq")

    public long getRequestId() {

        return requestId;

    }

Any hints how to make it work on final release? Thanks for help.

Pawel

[scd]

Hi,

You can create a trigger that handles id (request_id) automatic .

For example:

create or replace TRIGGER "BI_REQUEST"

  before insert on "REQUEST"              

  for each row 

begin  

    select "request_request_id_seq".nextval into :NEW.request_id from dual;

end;

Your SQL request in this case becomes :  insert into request (operation_date, operation_name, user_id) values (?, ?, ?)

so you no longer need to have the value of the sequence in your application.

Serigne

[pskrzynski]

Well it will not wirk either. The problem is that this record is further referenced from RequestParameter records. So if I do trigger the inserted id will be different that id set by hibernate in request object hence the foreign key would reference wrong object which is not right.

Apparantly I did something like that on Oracle database:

alter sequence request_request_id_seq increment by 1000;

alter sequence request_request_id_seq increment by 1;

and it works okay. However I still do not understand the difference in handling the issue between M3 and Final release.

Rgds.

Pawel

[nickarls]

The Hibernate versions have probably advanced between M3 and Final, you might want to hunt in the Hibernate forums for changes in this area.

[juergen.zimmermann]

AFAIK, JBossAS 6.0.0.Final is using Hibernate 3.6.0 -- while 3.5.x was used before.

[wolfgangknauf]

Hi Pawel,

to trace the sql parameters in the hibernate logs, see this: http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/session-configuration.html#configuration-logging

In the JBoss logging config file (\server\default\deploy\jboss-logging.xml), add this (I created a new appender, because I don't want trace log for the rest of the output):

   <appender name="CONSOLE.HIBERNATE_PARAMETERS" class="org.apache.log4j.ConsoleAppender">
      <errorHandler class="org.jboss.logging.util.OnlyOnceErrorHandler"/>
      <param name="Target" value="System.out"/>
      <param name="Threshold" value="TRACE"/>

      <layout class="org.apache.log4j.PatternLayout">
         <param name="ConversionPattern" value="%d{ABSOLUTE} %-5p [%c{1}] %m%n"/>
      </layout>
   </appender>

    <category name="org.hibernate.type">
      <priority value="TRACE"/>
      <appender-ref ref="CONSOLE.HIBERNATE_PARAMETERS"/>
   </category>

This snippet was for AS 5.1, but I hope it will work in 6.0, too.

Best regards

Wolfgang

[pskrzynski]

Hello,

I found a solution but can't give an explanation. If you annotate the entity class like this:

    @Id

    @Column(name = "request_id")   

    @GeneratedValue(generator="request_increment")

    @SequenceGenerator(name="request_increment", sequenceName = "request_request_id_seq", allocationSize=1)

    public long getRequestId() {

        return requestId;

    }

it works just fine (I've put bold on the change). Hope that it helps if someone faces the same issue. Still the explanation between different behaviour of Hiberante 3.5.8 (used by 6.0.0.M3) and 3.6.0 (used by 6.0.0.Final) would be appreciated.

Rgds.

Pawel