How do I do a query like this using HibernateDaoSupport?

[theone]

Hi,

I have a USERLOG table and an ACTIVITY table. the relationship is one-to-many respectively.

USERLOG has a primary key made up of userid and date fields.
ACITITY has a foreign key made up of userid and date fields
and a primary key made up of userid, date and taskno fields

So, any user can have many entries in ACTIVITY table on a given date.

How do I do query like this in HibernateDaoSupport?
I want to query total count of taskno and group by taskno

SELECT taskno, count(taskno) from ACTIVITY group by taskno order by taskno

Mapping files

<hibernate-mapping>
<class 
    name="com.test.model.Userlog" >
    table="evalogs"
    <composite-id name="comp_id" class="com.test.model.UserlogPK" unsaved-value="any">
        <meta attribute="field-description" inherit="false">
           @hibernate.id
            generator-class="assigned"
        </meta>
        <key-property 
            name="userid" 
            column="userid" 
            type="java.lang.Integer"
            length="4"
        >
        </key-property>
        <key-property 
            name="date" 
            column="date" 
            type="java.lang.String"
            length="10"
        >
        </key-property>
    </composite-id>


    <!-- Associations -->
    <!-- derived association&#40;s&#41; for compound key -->
    <!-- end of derived association&#40;s&#41; -->
  
    <!-- bi-directional one-to-many association to Activity -->
    <set
        name="activity"
        lazy="true"
        inverse="true"
		cascade="all"
    >
        <key>
            <column name="userid" />
            <column name="date" />
        </key>
        <one-to-many 
            class="com.test.model.Activity"
        />
    </set>

</class>
</hibernate-mapping>


<hibernate-mapping>
<class 
    name="com.test.model.Activity" 
    table="activity"
>

    <composite-id name="comp_id" class="com.test.model.ActivityPK" unsaved-value="any">
        <meta attribute="field-description" inherit="false">
           @hibernate.id
            generator-class="assigned"
        </meta>
        <key-property 
            name="userid" 
            column="userid" 
            type="java.lang.Integer"
            length="4"
        >
        </key-property>
        <key-property 
            name="date" 
            column="date" 
            type="java.lang.String"
            length="10"
        >
        </key-property>
        <key-property 
            name="taskNo" 
            column="taskno" 
            type="java.lang.Integer"
            length="4"
        >
        </key-property>
    </composite-id>    


    <!-- Associations -->
    <!-- derived association&#40;s&#41; for compound key -->
    <!-- bi-directional many-to-one association to Userlog -->
    <many-to-one
        name="Userlog"
	    class="com.test.model.Userlog"
	    update="false"
	    insert="false"
	>
        <meta attribute="field-description">
           @hibernate.many-to-one
            update="false"
            insert="false"
        </meta>
        <meta attribute="field-description">
           @hibernate.column
            name="date"
        </meta>
        <meta attribute="field-description">
           @hibernate.column
            name="userid"
        </meta>
    	<column name="userid" />
    	<column name="date" />
	</many-to-one>
	
    <!-- end of derived association&#40;s&#41; -->
  

</class>
</hibernate-mapping>

[Paul Newport]

Hi,

I have a USERLOG table and an ACTIVITY table. the relationship is one-to-many respectively.

I think this a pretty much HIbernate speciifc problem, so best asked on the hibernate forum. The HibernateDaoSupport is really a Spring convenience wrapper to calling hibernate code, your problem seems to be purely hibernate related.

[theone]

Sorry,

I'm not getting any response from Hibernate forum. Can someone help?

[irbouho]

theone,
take a look at 11.10. The group by clause in Hibernate online documentation.

HTH