Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help with a query (HQL)

 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 210
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic