I have a problem trying to do a bulk update to a class with composite-id, the generated sql was wrong.
(Hibernate 3.2.2)
Parts of HBM's:
Code:
<class name="P" table="P">
<composite-id>
<key-property name="ddate" column="ddate" type="date"/>
<key-many-to-one name="d" class="D">
<column name="c"/>
<column name="l"/>
<column name="b"/>
<column name="d"/>
</key-many-to-one>
</composite-id>
.....
</class>
Code:
<class name="D">
<composite-id>
<key-many-to-one name="b" class="B">
<column name="c" />
<column name="l" />
<column name="b" />
</key-many-to-one>
<key-property name="d" />
</composite-id>
.....
</class>
Code:
<class name="B">
<composite-id>
<key-many-to-one name="l" class="L">
<column name="l"/>
<column name="l"/>
</key-many-to-one>
<key-property name="b"/>
</composite-id>
.....
</class>
Code:
<class name="L" table="L">
<composite-id>
<key-many-to-one name="c" class="C" column="c"/>
<key-property name="l" type="string" />
</composite-id>
.....
</class>
HQL used:
update P set nominal = 0, value = 0, nominal2 = 0, value2 = 0 where mx = true and d.b.l = :l and ddate = :date
SQL Generated:
update P, set nominal=0, value=0, nominal2=0, value2=0 where mx='Y' and c=? and l=? and ddate=?
the query can't be executed properly (Oracle9i) becasue the colon after P ("P,") must not exist. "ORA-00971: missing SET keyword"
I try another approach, with this HQL
HQL:
update P p set nominal = 0, value = 0, nominal2 = 0, Value2 = 0 where mx = true and exists (select d from D d where d.b.l = :l and d=p.d) and ddate = :date
SQL Generated:
Hibernate: update P set nominal=0, value=0, nominal2=0, value2=0 where mx='Y' and (exists (select (country, lv, branch, deal) from D d1_, B b2_ where d1_.c=b2_.c and d1_.l=b2_.l and d1_.b=b2_.b and b2_.c=? and b2_.l=? and c=P.c and l=P.l and b=POSITIONS.b and d=P.d)) and ddate=?
I think the query is right but oracle throw a error "ORA-00907: missing right parenthesis",
the error happend because the subselect have parenthesis in the select clause, in this part
"...select (country, lv, branch, deal) from D ..." must be "...select country, lv, branch, deal from D..." to work.
Anybody can help me??.
I don't know how can i do.
Thanks.
Login
FAQ
Search