-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 
  Previous topic | Next topic 
Author Message
Lukasz (Qr)
 Post subject: Bulk operations fails when using composite id
PostPosted: Sun Mar 05, 2006 6:56 pm 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
I have an object that has composited id:
Code:
      <composite-id name="comId" class="ComId">
              <key-property name="id"/>
              <key-property name="rev"/>
      </composite-id>

All the bulk operations fail. For example:
Code:
session.createQuery("update MyObject co set co.someField = false where co.comId = :id ")
    .setInteger("id", myObj.getComId().getId().intValue())
    .executeUpdate();                           


results in two queries:
Code:
insert into HT_MyObject select * from MyObject bulk_target where id=556 and someField=1
update MyObject set someField=0 where (id, rev) IN (select id, rev from HT_MyObject)


second query fails with:
Quote:
Unexpected token: , in statement


Same thing happens for HQL bulk delete "DELETE MyObject co WHERE co.comId.id = :id" causes:

Full stack trace of any exception that occurs:
Code:
uncategorized SQLException for SQL [delete from MyObject where (id, rev) IN (select id, rev from HT_MyObject)];
SQL state [37000]; error code [-11]; Unexpected token: , in statement [delete from MyObject where (id, rev) IN (select id, rev from HT_MyObject)];
nested exception is java.sql.SQLException: Unexpected token: , in statement [delete from MyObject where (id, rev) IN (select id, rev from HT_MyObject)]
java.sql.SQLException: Unexpected token: , in statement [delete from MyObject where (id, rev) IN (select id, rev from HT_MyObject)]
   at org.hsqldb.jdbc.Util.throwError(Unknown Source)
   at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
   at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
   at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:185)
   at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
   at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:93)
   at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:86)
   at org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:113)
   at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:334)
   at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:209)
   at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1148)
   at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)


Name and version of the database you are using: HSQL 1.8.0
Hibernate version: 3.1.2

Are these operation not yet supported, or should I file it to JIRA?

Thanks in advance,
Lukasz
Top
 Profile  
 
steve
 Post subject:
PostPosted: Sun Mar 05, 2006 11:22 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
I believe HSQLDB does not support ANSI-SQL "row constructor" syntax; thats the piece where we try to do the "...where (id, rev) IN...". The underlying database would need support for that in order for us to do the bulk delete against a multi-table structure.

So I assume your MyObject is either part of a <joined-subclass/> or <union-subclass/> hierarchy? If not, the parser should should not be selecting the "insert into temp table" path and that would be a bug of some sort...
Top
 Profile  
 
Lukasz (Qr)
 Post subject:
PostPosted: Mon Mar 06, 2006 10:58 am 
Regular
Regular

Joined: Tue Sep 28, 2004 6:34 pm
Posts: 50
Quote:
So I assume your MyObject is either part of a <joined-subclass/> or <union-subclass/> hierarchy? If not, the parser should should not be selecting the "insert into temp table" path and that would be a bug of some sort...


MyObject indeed takes part in joined-subclass.

So my options are call HSQLDB team to support "ANSI-SQL 'row constructor'", update manualy, change database, do not use bulk operations on joined-classes, change class hierarchy from "table per subclass" to "table per class"?

The only thing that bothers me is that the way that this bulk edit is done -it is in efficient. I have similar mapping to:

Code:
<class name="Payment" table="PAYMENT">
      <composite-id name="comId" class="ComId">
              <key-property name="id"/>
              <key-property name="rev"/>
      </composite-id>
    <property name="amount" column="AMOUNT"/>
    ...
    <joined-subclass name="CreditCardPayment" table="CREDIT_PAYMENT">
        ...
    </joined-subclass>
</class>


When I try to update 'amount' which is in base class, it shouldn't need to do a select into temporary table. Same states for case when I try to delete by comId.id.
Top
 Profile  
 
steve
 Post subject:
PostPosted: Mon Mar 06, 2006 12:34 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Well row constructor syntax is part of ANSI SQL; is it not valid for us to expect it to be available for usage?

There was a choice made whenever bulk updates are done against multi-table structures. Basically, there are two options. We could try to determine if anything in either the SET clause or the WHERE clause references colunms in more then one of the tables. If not, it is possible to just perform the single direct update instead of first doing the select into the temp table. However, for all other cases that is not possible since most databases do not support joined updates. deletes against a multi-table structure are a little different; there we in fact always need to perform the select into temp table.

Why do you think this is an inefficient approach. Consider the alternative which is to perform the select and pull back all matches into memory and then perform the individual table updates/deletes using the matches as individual bind parameters. That is far more inefficient.

See:
http://blog.hibernate.org/cgi-bin/blosx ... #dml-basic
and
http://blog.hibernate.org/cgi-bin/blosx ... ulti-table
for more details
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 4 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.