Nested collection flush throw batch update exception

xtophe31 · **Joined:** Wed Jun 11, 2008 4:43 am **Posts:** 20

In my app, I have two nested collections. Person class has a collection of childs which has a collection of elements. All seems to work well until I call a refresh. It gives me a very generic exception with which I can't figure out what is wrong (org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update, nested with a java.sql.BatchUpdateException: failed batch exception).

I've tried different things to save the child before adding the elements but it didn't helped, even if I flush the session just after saving the Child.

Is there a particular way to handle such nested collections?

Hibernate version: 3.2.6

Mapping documents:

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.myapp.tests.hibernate.model">
  <class name="Person" table="PERSONS">
     <id name="id" column="PERSON_ID">
        <generator class="increment"></generator></id>
     <property name="name"></property>
     <bag name="childs" cascade="all,delete-orphan" inverse="true">
        <key column="PERSON_ID"></key>
        <one-to-many class="Child" />
     </bag></class>
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.myapp.tests.hibernate.model">
  <class name="Child">
     <id name="id" column="CHILDID">
        <generator class="increment"></generator>
     </id>
     <property name="name"></property>
     <many-to-one name="parent" column="PERSON_ID" class="Person"></many-to-one>
     <set name="elements" inverse="true" cascade="all,delete-orphan">
        <key column="ELMTID"></key>
        <one-to-many class="Element" />
        
     </set>
  </class>
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.myapp.tests.hibernate.model">
  <class name="Element">
     <id name="id" column="ELMTID">
        <generator class="increment"></generator></id>
     <property name="title"></property>
     <many-to-one name="parent" class="Child" column="CHILDID"></many-to-one>
  </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

         Person theEvent = (Person) session.load(Person.class, (Long) 1L);
         Child child;
         for (int i=1;i<10;i++) {
            child = new Child();
            child.setName("child_00"+i);
            for (int j=1;j<10;j++) {
               Element newElement = new Element("element_00"+i+"_00"+j);
               child.addElement(newElement);
               session.save(newElement);
            }
            theEvent.addChild(child);
            session.save(child);
            for (Iterator<Child> iter = theEvent.getChilds().iterator();iter.hasNext();) {
               child = iter.next();
               if (child==null) {
                  logger.debug("NULL child!!!");
               }
               else {
                  logger.debug(child.getName());
               }
            }
         }
         session.flush();
         session.getTransaction().commit();
      } catch (HibernateException e) {
         e.printStackTrace();
         session.getTransaction().rollback();
      } finally {
         hibernate.getSessionFactory().close();
      }

c
Full stack trace of any exception that occurs:

Code:

org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:92)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:87)
   at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:222)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2229)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2665)
   at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:60)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
   at java.lang.reflect.Method.invoke(Unknown Source)
   at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:301)
   at $Proxy0.flush(Unknown Source)
   at org.myapp.tests.hibernate.model.PersonManager.createAndStoreEvent(PersonManager.java:70)
   at org.myapp.tests.hibernate.model.PersonManager.main(PersonManager.java:21)
Caused by: java.sql.BatchUpdateException: failed batch
   at org.hsqldb.jdbc.jdbcStatement.executeBatch(Unknown Source)
   at org.hsqldb.jdbc.jdbcPreparedStatement.executeBatch(Unknown Source)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
   ... 20 more

Name and version of the database you are using: HSQLDB 1.8

The generated SQL (show_sql=true):

Code:

Hibernate: select max(ELMTID) from Element
Hibernate: select person0_.PERSON_ID as PERSON1_6_0_, person0_.name as name6_0_ from PERSONS person0_ where person0_.PERSON_ID=?
Hibernate: select max(CHILDID) from Child
Hibernate: select childs0_.PERSON_ID as PERSON3_1_, childs0_.CHILDID as CHILDID1_, childs0_.CHILDID as CHILDID7_0_, childs0_.name as name7_0_, childs0_.PERSON_ID as PERSON3_7_0_ from Child childs0_ where childs0_.PERSON_ID=?
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)
Hibernate: insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)

Debug level Hibernate log excerpt:

Code:

00:05,152 DEBUG AbstractFlushingEventListener:290 - executing flush
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Child#1]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Child#1]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#1]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#1]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#2]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#2]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#3]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#3]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#4]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#4]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#5]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#5]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#6]
00:05,152 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#6]
00:05,152 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#7]
00:05,168 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#7]
00:05,183 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#8]
00:05,183 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#8]
00:05,183 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Element#9]
00:05,183 DEBUG AbstractEntityPersister:1997 - Dehydrating entity: [org.myapp.tests.hibernate.model.Element#9]
00:05,183 DEBUG AbstractEntityPersister:2209 - Inserting entity: [org.myapp.tests.hibernate.model.Child#2]
00:05,183 DEBUG JDBCExceptionReporter:69 - Could not execute JDBC batch update [insert into Element (title, CHILDID, ELMTID) values (?, ?, ?)]

xtophe31 · **Joined:** Wed Jun 11, 2008 4:43 am **Posts:** 20

Ok, I went a little bit further...
When I add only a few items, all is ok. But if I want to add more than 10 I get an exception.
Is there something that I've missed to configure?

xtophe31 · **Joined:** Wed Jun 11, 2008 4:43 am **Posts:** 20

Duplicate post deleted!

Cameron McKenzie · **Posted:** Wed Jun 25, 2008 9:52 am

Could it be as simple as you exhausing the number of database connections available to you? Or perhaps too many sessions open? Maybe a little inspection of the various active classes on your JVM might point in some direction.

I'd really be interested in the resource management of this part of the application. The code looks good on first inspection, but the fact that multiple writes at the same time cause problems keep making me think there's somethign there in the resource management.

xtophe31 · **Joined:** Wed Jun 11, 2008 4:43 am **Posts:** 20

Thanks for your reply and suggestions.
In fact the problem came from the mapping. It created a constraint on the ELMID, that caused the error.
In the meantime, I changed the DB from embedded HSQLDB to MySQL and I now have really nice error messages! At least, error messages are give sufficient information to investigate errors and can interrogate the DB to make some checks!
The correct mappings are (Person mapping was correct):

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.myapp.tests.hibernate.model">
  <class name="Child">
     <id name="id" column="CHILDID">
        <generator class="increment"></generator>
     </id>
     <property name="name"></property>
     <many-to-one name="parent" column="PERSON_ID" class="Person"
        not-null="true">
     </many-to-one>
     <set name="elements" inverse="true" cascade="all,delete-orphan">
        <key column="CHILDID"></key>
        <one-to-many class="Element" ></one-to-many>
        
     </set>
  </class>
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="org.myapp.tests.hibernate.model">
  <class name="Element">
     <id name="id" column="ELMTID">
        <generator class="increment"></generator></id>
     <property name="title"></property>
     <many-to-one name="parent" class="Child" not-null="true"
        column="CHILDID">
     </many-to-one>
  </class>
</hibernate-mapping>