Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: HQL with group by across multiple tables
PostPosted: Sun Sep 25, 2016 9:55 am 
Newbie

Joined: Fri Sep 16, 2016 1:56 am
Posts: 2
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();
        }


Top
 Profile  
 
 Post subject: Re: Java Hibernate:HQL with group by across multiple tables
PostPosted: Sun Sep 25, 2016 2:03 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
Why do you want to rewrite it to HQL? You can just fetch entities with native SQL as well.

Also, the SQL query has a Cartesian Product. Why do you think that's going to be efficient?

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Java Hibernate:HQL with group by across multiple tables
PostPosted: Sun Sep 25, 2016 2:28 pm 
Newbie

Joined: Fri Sep 16, 2016 1:56 am
Posts: 2
mihalcea_vlad wrote:
Why do you want to rewrite it to HQL? You can just fetch entities with native SQL as well.


Sorry I am new to Java and Hibernate, can you please help me to understand what you mean by native SQL? You mean use a native SQL in Java Code using session.CreateSQLQuery and use result transformers to map it back to the entity bean?

Quote:
Also, the SQL query has a Cartesian Product. Why do you think that's going to be efficient?

I just wanted to test it out on the large DB that we have to see how it(HQL) performs. If it does not then I may have to look at other alternatives.


Top
 Profile  
 
 Post subject: Re: Java Hibernate:HQL with group by across multiple tables
PostPosted: Mon Sep 26, 2016 1:07 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1038
Quote:
Sorry I am new to Java and Hibernate, can you please help me to understand what you mean by native SQL? You mean use a native SQL in Java Code using session.CreateSQLQuery and use result transformers to map it back to the entity bean?


That's right!

HQL performance is based on the underlying SQL efficiency. For simple queries, HQL is fine. However, for advanced SQL, you need to take advantage of the database-specific SQL capabilities.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.