Need help with a query (HQL)

[Dave Brown]

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]

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]

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]

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]

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]

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]

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