6 Replies Latest reply on Jan 26, 2011 10:01 AM by Pawel Skrzynski

    JBoss 6 - hibernate/jpa/oracle issues

    Pawel Skrzynski Newbie

      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

        • 1. JBoss 6 - hibernate/jpa/oracle issues
          serigne DIOP Newbie

          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

          • 2. JBoss 6 - hibernate/jpa/oracle issues
            Pawel Skrzynski Newbie

            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

            • 3. JBoss 6 - hibernate/jpa/oracle issues
              Nicklas Karlsson Master

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

              • 4. JBoss 6 - hibernate/jpa/oracle issues
                Juergen Zimmermann Master

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

                • 5. JBoss 6 - hibernate/jpa/oracle issues
                  Wolfgang Knauf Master

                  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

                  • 6. JBoss 6 - hibernate/jpa/oracle issues
                    Pawel Skrzynski Newbie

                    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