Hi Guys,

I have some issues with hql bulk insert select statement.

Currently i have the following sql
sqlB.append("insert into event_group(case_id,event_seq,group_seq,name,");
sqlB.append("publicity_date,source_of_info,invno,country,comment,target_acquiry,lum,ltm) ");
sqlB.append("select '" + group.getCaseId() + "', " + group.getEventSequence());
sqlB.append(", isnull(max(group_seq),0)+1, '" + name.toUpperCase() + "',");
sqlB.append("me.event_date, '" + source + "', me.invno, ");
sqlB.append("'" + country.toUpperCase());
sqlB.append("', '" + comments + "', '" + group.getTypeCode() + "',");
if (userId == null) {
sqlB.append("null");
} else {
sqlB.append("'" + userId + "'");
}
sqlB.append(",getdate() from market_event me, event_group eg ");
sqlB.append("where me.case_id *= eg.case_id and ");
sqlB.append("me.event_seq *= eg.event_seq and ");
sqlB.append("me.case_id = '" + group.getCaseId() + "' and ");
sqlB.append("me.event_seq = " + group.getEventSequence());
sqlB.append(" group by me.event_date, me.invno");

I want the above query to be written using hql.
Can any one please help me out. I know the Hibernate 3.1.2 support
hql insert select . But the problem that i face is, i have the following mapping document.

<class name="EventGroup" table="event_group">
<composite-id name="key" class="EventGroupKey">
<key-property name="caseId" type="string" column="case_id"/>
<key-property name="eventSeq" type="integer" column="event_seq"/>
<key-property name="groupSeq" type="integer" column="group_seq"/>
</composite-id>
<property name="name" type="string" column="name"/>
<property name="publicityDate" type="date" column="publicity_date"/>
<property name="sourceOfInfo" type="string" column="source_of_info"/>
<property name="invNo" type="string" column="invno"/>
<property name="country" type="string" column="country"/>
<property name="commentText" type="string" column="commenttext"/>
<property name="targetAcquiry" type="string" column="target_acquiry"/>
<property name="lastModifiedBy" type="string" column="lum"/>
<property name="lastModifiedDate" type="string" column="ltm"/>

</class>

from this mapping document, you will be able to understand, that EventGroup entity has composite id. This composite id is the one that is creating me problem. Since i am not able to give an alias name for insert table. I am not able to access the properties of the composite id class.

If you see the above current sql, all the properties of the composite id class are not directly from the select table columns value. Some property values are taken from info object.

Can any pls help me, how to write the above sql using hql. If that is not possible, can any one tell me how to use the sql directly in hibernate.

If you need more information, please let me know.


Hibernate version: Hibernate 3.1.2

oracle 10g

with thanks
krishna