I am trying to write the below MYSQL query in HQL and below is one version that I have written that works with some limitations, please help me with a more optimized way to write the same query. The main complexity lies in group by clauses across multiple tables with having clause.
Code:
select t1.* from patient t1 inner join person t2 on t1.patient_id = t2.person_id inner join person_name t3 on
t2.person_id = t3.person_id,
(SELECT
person1.gender, person1.birthdate, pn1.given_name, pn1.family_name
FROM
patient p1
inner join person person1 on p1.patient_id = person1.person_id AND p1.voided = FALSE
INNER JOIN person_name pn1 on person1.person_id = pn1.person_id
group by person1.gender, person1.birthdate, pn1.given_name, pn1.family_name
having count(*) > 1) t4 where
t2.gender = t4.gender and t2.birthdate = t4.birthdate and t3.given_name = t4.given_name and
t3.family_name = t4.family_name;
Below mentioned HQL works but I want to avoid the multiple column IN clause (below) since it slows down the performance also it does not work with unit testing via H2 DB. Can you please help me with this? I tried using Hibernate API's as well but found it very complex.
Code:
List<Patient> patients = new Vector<Patient>();
if (attributes.size() > 0) {
Session s = sessionFactory.getCurrentSession();
Query query = s.createQuery("select pt1 from Patient pt1, " + "Person p1, " + "PersonName pn1 where "
+ "pt1.patientId = p1.personId and " + "p1.personId = pn1.person.personId and "
+ "(p1.gender,p1.birthdate, pn1.familyName, pn1.givenName) in "
+ "(select pn2.person.gender, pn2.person.birthdate, pn2.familyName, pn2.givenName "
+ "from Patient pt2, " + "Person p2, PersonName pn2 where " + "pt2.patientId=p2.personId and "
+ "p2.personId=pn2.person.personId "
+ "group by pn2.person.gender, pn2.person.birthdate, pn2.familyName, pn2.givenName "
+ "having count(*) > 1) " + "order by p1.gender, p1.birthdate, pn1.familyName, pn1.givenName");
patients = query.list();
}
Login
FAQ
Search
