aspose file tools*
The moose likes Object Relational Mapping and the fly likes Need help with a query (HQL) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Need help with a query (HQL)" Watch "Need help with a query (HQL)" New topic
Author

Need help with a query (HQL)

Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
Hi all, Using Spring / JPA & Hibernate, with my DAOS using the getJPATemplate() method of querying my db I'm stuck designing a particular query..

What I need to do is query to create a list of objects where there is more than one occurance of an element over particular dates.. Ill show you a class structure to describe this..


class A {
String name;
Date theDate;
... lots of other fields
}

so what I need is to create a list containing just the name and theDate fields from object A where the name occurs more than once .. e.g. say this is my table contents:

Name TheDate

dave 1/1/1
dave 2/1/1
john 1/1/1
frank 1/3/1


if that was my data set i'm query against I want my resulting list to contain the two daves... because it occurs on more than one date...

I hope this makes sense.. I'm not sure how to do this kind of query with JPA & Hibernate.. any help appreciated..

thank you


Regards, Dave Brown
SCJP 6 - [url]http://www.dbws.net/[/url] - Check out Grails Forum
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Do you know SQL at any way? How would you do it with SQL? Once found out, it's easy to "translate" it to HQL.
Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
Bauke Scholtz wrote:Do you know SQL at any way? How would you do it with SQL? Once found out, it's easy to "translate" it to HQL.


Well I imagine I'd be doing something along the lines of checking the COUNT() > 1 and doing a group by but getting that into HQL totally puzzles me with this particular query....
Rahul Babbar
Ranch Hand

Joined: Jun 28, 2008
Posts: 210
A query something like
Select name, count(*) from A group by name having count(*) >1
should work...
I am pretty sure the same query works for HQL also.


Rahul Babbar
Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
Well I believe I got it working finally late last night, it could probably be improved upon but for now it will do.. What I ended up doing was creating a subquery containing where the elements only appear ONCE, and using a NOT IN to check if the element was in the this table.. which therefore meant it appeared more than once..

getJpaTemplate().find(
"FROM ScheduleElement AS s " +
"JOIN fetch s.element "+
"JOIN fetch s.schedule "+
"WHERE s.schedule.station = ?1 AND s.elementID NOT IN (" +
" SELECT e.elementID " +
" FROM ScheduleElement AS e " +
" LEFT JOIN e.element AS ie " +
" GROUP BY ie.title " +
" HAVING COUNT(ie.title) > 1 ) " +
"ORDER BY s.element.title ASC, s.elementDate ASC",station);

Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
Sadly I spoke too soon.. That query I thought was working, doesnt.. I tend to get 'crazyJpa' null pointerexceptions from hibernate.. So back to the query itself.. Does anyone think the query below could be done more efficiently than using a NOT IN clause ?

"FROM ScheduleElement AS s " +
"JOIN fetch s.element "+
"JOIN fetch s.schedule "+
"WHERE s.schedule.station = ?1 AND s.elementID NOT IN (" +
" SELECT e.elementID " +
" FROM ScheduleElement AS e " +
" LEFT JOIN e.element AS ie " +
" GROUP BY ie.title " +
" HAVING COUNT(ie.title) > 1 ) " +
"ORDER BY s.element.title ASC, s.elementDate ASC"

Thanks
Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
I imagine in SQL your query would look something like:

select distinct name,count(distinct date) from table group by name having count(distinct date) > 1

which in HQL should translate to

select distinct s.name, count(distinct s.date) from entity s group by s.name having count(distinct s.date) > 1
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Need help with a query (HQL)