Managing bidirectional one-to-many with JDBCTransaction

telerice · **Joined:** Wed Dec 10, 2003 10:45 pm **Posts:** 6

Don't know if this is possible. If not, is there a common practice that deal with this kind of problem?

Problem: insert into two-tables (one-to-many relationship) as a atomic operation.

For example,

Code:

trans = session.beginTransaction();
Parent p = new Parent();
session.save(p);  // persist parent first before persisting child
Child c = new Child()
p.addChild( c );
session.save(c);
trans.commit();

This doesn't work because the first save(p) needs to be committed before the second save(c).

How do I work around this problem? Will using JTATransaction make any difference?

Thanks in advance.

p944 · **Posted:** Fri Dec 12, 2003 8:09 am

Depends on your db constraints, but I would guess you just need a

Code:

session.flush();

between the two saves.
This should force the sql to do the insert for the first, but cos you are in a transaction then it should be seen externally as atomic .

emmanuel · **Posted:** Fri Dec 12, 2003 8:40 am

telerice wrote:

This doesn't work because the first save(p) needs to be committed before the second save(c).

Commit stuff has no relationship with the number of sql request done. You commit every sql executed between the begin and the commit at the same time.

telerice · **Joined:** Wed Dec 10, 2003 10:45 pm **Posts:** 6

I'm avoiding using flush. Correct me if I'm wrong - if the a query is flush, I cannot roll it back. (?)

I was able to get it to commit if I allow null for the foreign key field that points to the Parent entity. But when I tested rollback(), the Parent was not inserted - the child was dangling with (NULL) reference back to the parent.

(I have cascade="all-delete-orphan", but I guess it didn't make a difference, since the child was inserted without reference back to its parent).

Here is the detail:

Code:

//s.setFlushMode(FlushMode.COMMIT);
Transaction t = s.beginTransaction();
ESR esr = new ESR(new Integer(52341), "ED");
s.save(esr);
//s.flush();
PatchedFile f1 = new PatchedFile("aa.jar", esr);
PatchedFile f2 = new PatchedFile("bb.jar", esr);
esr.addFile(f2);
esr.addFile(f1);
//esr.addFile("bb.jar");
s.save(f1);
s.save(f2);
//s.flush();
t.rollback();
s.close();

The queries sent:

Code:

Hibernate: insert into patchedfile (file, patchedInESR) values (?, ?)
Hibernate: SELECT LAST_INSERT_ID()
Hibernate: insert into patchedfile (file, patchedInESR) values (?, ?)
Hibernate: SELECT LAST_INSERT_ID()

The parent:

Code:

<class name="com.stc.cm.esr.hibernate.codegen.ESR" table="esr">
  <id column="esrNumber" name="esrNumber" type="int">
     <generator class="assigned"/>
  </id>
  <property column="createDate" length="19" name="createDate" not-null="true" type="timestamp"/>
  <property column="Component" length="11" name="component" not-null="true" type="string"/>
  <set name="patchedFiles" cascade="all-delete-orphan">
     <key column="patchedInESR"/>
     <one-to-many class="com.stc.cm.esr.hibernate.codegen.PatchedFile"/>
  </set>
</class>

The child:

Code:

<class name="com.stc.cm.esr.hibernate.codegen.PatchedFile" table="patchedfile">
  <id column="fileMapID" name="fileMapID" type="long">
     <generator class="identity"/>
  </id>
  <property column="file" length="128" name="file" not-null="true" type="string"/>
  <!-- <property column="patchedInESR" length="7" name="patchedInESR" not-null="true" type="integer"/> -->
  <many-to-one name="esr" class="com.stc.cm.esr.hibernate.codegen.ESR" column="patchedInESR" outer-join="true" not-null="false"/>
</class>

michael · **Posted:** Fri Dec 12, 2003 8:58 am

Of couse you can still rollback after a flush. Flush just means sending the generated Query to the DB, not commiting the transaction.

emmanuel · **Posted:** Fri Dec 12, 2003 9:07 am

telerice wrote:

I'm avoiding using flush. Correct me if I'm wrong - if the a query is flush, I cannot roll it back. (?)

Grrr why on earth flushing sql into a DB would disallow transaction rollback. How do you do in plain JDBC ?

telerice · **Joined:** Wed Dec 10, 2003 10:45 pm **Posts:** 6

eh, dunno how to do it in JDBC. I'm like a super newbie .. or something.

I uncommented the two flush(). Now both the children and parent got written to the DB.

I need to take a look at my db (MySql) setup again to make sure it's using the right database that support transaction.

emmanuel · **Posted:** Fri Dec 12, 2003 11:57 am

telerice wrote:

eh, dunno how to do it in JDBC. I'm like a super newbie .. or something.

Nothing personal, You were the third person supposing the flush = tx stuff today. Anyway I added it to the FAQ.

telerice · **Joined:** Wed Dec 10, 2003 10:45 pm **Posts:** 6

What an idiot! Took me 5 minutes to find out that I'm not using a transacted table. Argh!

MyISAM != tx

All went well after changing to InnoDB.