Using HQL to add into a relationship table.

lielar · **Joined:** Wed Jul 11, 2007 12:28 pm **Posts:** 5

Hi

I want to construct a Hibernate object that refers to two database tables. I have one object called Attribute and another called Activity. The object Attribute is read only, and has a Many-to-Many relationship with Activity. The following is the definition of Attribute

Code:

    

 <class name="Attribute" table="DAW_ATTRIBUTE_TABLE">
    <id name="id" column="ATTRIBUTE_ID">
         <generator class="native">
           <param name="sequence">DAW_ATTRIBUTE_S</param>
         </generator>
    </id>
    <property name="name" column="NAME"/>
    <property name="description" column="DESCRIPTION" />
    
    
    <set name="activities" table="DAW_USERATTRIBUTEACTIVITY_TABLE">
       <key column="ATTRIBUTE_ID" />
       <many-to-many column="ACTIVITY_ID" class="org.sakaiproject.dwa.api.model.Activity" />
    </set>
    
    
 </class>
    <query name="getAttributeById">
    <![CDATA[from Attribute attribute where attribute.id= :attribute]]>
    </query>
 
    <query name="getActivitiesAttributes">
    <![CDATA[from Attribute as attrib inner join attrib.activities as activity where activity.userUuid= :userUuid and activity.context= :context]]>
    </query>
 

Here is the Activity hbm.xml file

Code:

    
   <class name="Activity" table="DAW_ACTIVITY_TABLE">
       <id name="id" column="ID" length="19" unsaved-value="0">
         <generator class="native">
           <param name="sequence">DAW_ACTIVITY_S</param>
         </generator>
       </id>
      
      <property name="userUuid" column="USER_UUID" not-null="true" />
      <property name="context" column="LOCATION_ID" not-null="true" />
       <property name="name" column="NAME" not-null="true" />
      <property name="type" column="TYPE" not-null="true" />
       <property name="importance" column="IMPORTANCE" length="1" not-null="true" />
       <property name="status" column="STATUS" not-null="true" />
       <property name="notes" column="NOTES" length="4000" not-null="false" />
       <property name="dateCreated" column="CREATED_DATE" not-null="true" />
         <property name="duration" column="DURATION" not-null="true" />
         <property name="startTime" column="START_TIME"/>
       <property name="editedDate" column="EDITED_DATE" not-null="false" />
   </class>

My questions are

insert

delete

UPDATE: Considering the fact that HQL inserts only support inserts from another table, how do you reference the foreign keys in the relationship table (that is the attribute and activity identities) in HQL?
Here is my attempt -

Code:

insert into Attribute.activities (Attribute.id, Activity.id) 
    select attrib.id, activity.id from Attribute as attrib, Activity as activity where attrib.id= :attribute and activity.id = :activity

Error message - Attribute.activities isn't map!

Can I use a java.util.Set in the Attribute object only as a place holder and not load data into it, because I rather load the relationship in a separate object?

Do I just use <sql-query> to load the other object by specifying the bean properties of the other object?

Thanks
Patrick

littypreethkr · **Posted:** Wed Mar 11, 2009 12:50 am

lielar wrote:

Can I use a java.util.Set in the Attribute object only as a place holder and not load data into it, because I rather load the relationship in a separate object?

Then why dont you have a one-to-many to this entity in the Attribute class?

Also why cant you use session.save() instead of HQL insert?

lielar · **Joined:** Wed Jul 11, 2007 12:28 pm **Posts:** 5

littypreethkr wrote:

Then why dont you have a one-to-many to this entity in the Attribute class?

Also why cant you use session.save() instead of HQL insert?

Isn't save() saves the entire object whereas I only want to write to the relationship table.

one-to-many relationship doesn't work for our purposes.

littypreethkr · **Posted:** Wed Mar 11, 2009 1:12 am

To the existing Attribute object's activities set, you add the relating Activity objects and then call session.update(attributeObject)

This will insert the rows in link table.

lielar wrote:

one-to-many relationship doesn't work for our purposes.

What I meant is; you said that your link object is a separate entity. So basically your associations become like:

LinkObject MTO Attribute
LinkObject MTO Activity

Isn't it?

lielar · **Joined:** Wed Jul 11, 2007 12:28 pm **Posts:** 5

littypreethkr wrote:

To the existing Attribute object's activities set, you add the relating Activity objects and then call session.update(attributeObject)

This will insert the rows in link table.

How do I reference the two columns in the relationship table using HQL - something like

Code:

update Attribute set Attribute.activities.Activity.id= :activity, Attribute.activities.Attribute.id = :attribute where Attribute.id= :attribute

Is that how you would do it?

littypreethkr wrote:

What I meant is; you said that your link object is a separate entity. So basically your associations become like:

LinkObject MTO Attribute
LinkObject MTO Activity

Isn't it?

I have an AssociatedAttribute object which links the two identities. I am not sure how to use the Link object?

littypreethkr · **Posted:** Wed Mar 11, 2009 1:32 am

No no no... What i meant is:

Code:

Attribute a = session.load(Attribute.class, 1);
Activity b = session.load(Activity.class, 2);
a.getActivities().add(b);
session.update(a);

lielar · **Joined:** Wed Jul 11, 2007 12:28 pm **Posts:** 5

littypreethkr wrote:

Code:

Attribute a = session.load(Attribute.class, 1);
Activity b = session.load(Activity.class, 2);
a.getActivities().add(b);
session.update(a);

Thanks for your help so far.

Is it possible to specify the <sql-update> so that only the relationship table is updated? If so, what would it be?

How can I do a join to load all the activities for a particular attribute?

littypreethkr · **Posted:** Wed Mar 11, 2009 3:18 am

I am still not clear why you want to do this with HQL? Using the session methods is the right way to do these things.

littypreethkr · **Posted:** Wed Mar 11, 2009 3:29 am

lielar wrote:

How can I do a join to load all the activities for a particular attribute?

You dont need a join for this. You can get this with:

Code:

select activities from Attribute attr where attr.id=:id