A coworker of mine asked me the following question:

I am trying to just do a simple inner join on a table through hibernate to pull a count of how many rows there are. I had myeclipse create all my business objects and configuration mappings. My table structure is like this:

SdtProgramTable – primary key = ProgramID

IdmsPartProgTable – foreign key = ProgramID

In the SdtProgramTable mapping file eclipse created the following set for this relationship.
<set name="idmsPartProgTableSet" inverse="true">
<key column="PROGRAM_ID"/>
<one-to-many class="IdmsPartProgTable"/>
</set>

For the IdmsPartProgTable, eclipse created a IdmsPartProgTable class and a IdmsPartProgTableKey class with the following mappings.

<composite-id name="id" class="IdmsPartProgTableKey">
<key-many-to-one name="idmsPartTable" column="PART_ID" class="IdmsPartTable"/>
<key-many-to-one name="sdtProgramTable" column="PROGRAM_ID" class="SdtProgramTable"/>
</composite-id>

So I am not sure how to do a HQL query that will be like this in sql:

Select count(*) from IdmsPartProgTable, SdtProgramTable
Where sdtProgramTable.programId = idmsPartProgTable.programID;

I'd think you'd just be able to do:



Does that not work?

You can turn the generated SQL on and see what it actually does...

If that doesn't work, you can try adding the WHERE clause to the end of the select...

Well, you could use SQL query like this

SQLQuery sqlQ = s.createSQLQuery( "select count(c.id) as cnt from ParentTable p, ChildTable c " +
"where c.u_id = p.id ");
sqlQ.addScalar("cnt", Hibernate.LONG);
Long o = (Long) sqlQ.uniqueResult();

I do not think that this has any portability implications since SQL is very standard and that classes are not going to be mapped to different tables in different databases.

thanks, I passed on the info - She said the first one gets her what she was looking for - being that join is implicit when you do the mapping

That's what I thought. Cool, glad she got it working!

How do I return count of resultset using createSQLQuery in Hibernate 2.x? The solution given above
viz,
SQLQuery sqlQ = s.createSQLQuery( "select count(c.id) as cnt from..")

doesnt work in Hibernate 2.x.