I have a (so far) simple application with subscribers and attributes associated to subscribers. My mapping is as follows:



In my java code I do (simplified a bit to make it short):



After I run the code, subscriber is added and all the attributes as well. But what troubles is the way they are created (I know this for sure because I have set profileSql=true in my JDBC settings):

1. a new row is inserted in the subscriber table:
insert into subscriber ...

2. id is fetched from the DB:
SELECT LAST_INSERT_ID()

3. a row to subscriber_attribute table is inserted three times and after each insert id is fetched from the DB:
insert into subscriber_attribute (attrKey, attrValue, subscriberId) values ('key1', 'value1', 1)
SELECT LAST_INSERT_ID()
insert into subscriber_attribute (attrKey, attrValue, subscriberId) values ('key2', 'value2', 1)
SELECT LAST_INSERT_ID()
insert into subscriber_attribute (attrKey, attrValue, subscriberId) values ('key3', 'value3', 1)
SELECT LAST_INSERT_ID()

4. each row inserted in 3. is then updated:
update subscriber_attribute set subscriberId=1 where attributeId=1
update subscriber_attribute set subscriberId=1 where attributeId=2
update subscriber_attribute set subscriberId=1 where attributeId=3

What makes the rows in subscriber_attribute to be updated in the end? I can see no reason for that, the subscriberId is already set when the rows were inserted.

Is there any way to have Hibernate insert the rows (in subscriber_attribute) in one query rather than inserting them one by one?

I'm using Hibernate 2.0.3 and MySQL (with INNODB).

Please read Parent/Child relationship doco.

That's so obvious, thanks Gavin! Stupid me...

That solves the insert/update problem, but is there any way to have Hibernate insert the rows (in subscriber_attribute) in one query rather than inserting them one by one? According to the documentation there isn't, am I right?

Eh? A SQL INSERT can only insert one row at a time.

I don't get you.

Nope. In MySQL you can insert several rows with just one insert clause. Like this:

insert into mytable values (a1,b1,c1),(a2,b2,c2),(a3,b3,c3);

oh ok this is nonstandard.

Does MySQL driver support this via the JDBC batch update API? They should, in which case Hibenate can do it already...

DB2 also supports this syntax.

I *could* probably be convinced that it would be worth supporting this syntax, but only if JDBC drivers don't do it already, under the covers.


Would you be able to do a little bit of research into this please?

TIA.

I tried setting:


It got initialized correctly:


and I got no errors, but still the inserts were made one by one. Am I right assuming that the MySQL connector I'm using (3.0.8) doesn't support batch updates?

well, the other way they could do it is to have the JDBC driver pass the whole batch of single inserts in one request (this would be the normal way). The end result should be the same performance-wise. Why not email the MySQL list?