• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

Need help with a query (HQL)

 
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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....
 
Ranch Hand
Posts: 210
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 187
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
reply
    Bookmark Topic Watch Topic
  • New Topic