Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: SQL to HQL conversion for Complex Query
PostPosted: Mon Jul 17, 2017 7:56 am 
Newbie

Joined: Mon Jul 17, 2017 7:53 am
Posts: 2
I have the following SQL query, i need to convert it to HQl. Please give me some advice. I tried but failed to do so.

Code:
select * from (select t1.*,m.name from (select sa.id,sa.account_no,sa.member,ma.ADDRESS_LINE1,ma.ADDRESS_LINE2,ma.ADDRESS_LINE3,sa.sub_product from dbo.SAVING_ACCOUNT sa inner join dbo.MEMBER_ADDRESS ma on sa.member = ma.member where sa.STATUS<>'DELETE' and ma.MAILING_ADDRESS = 1) t1 inner join member m on t1.member = m.id) t4 inner join (select st.* from dbo.SAVING_TRANSACTION st inner join (select min(transaction_no) as txn_no,SAVING_ACCOUNT,MEMBER  from dbo.SAVING_TRANSACTION group by member,saving_account) t3 on st.TRANSACTION_NO = t3.txn_no and st.member = t3.member) t5 on t4.id = t5.saving_account and t4.member = t5.member


Top
 Profile  
 
 Post subject: Re: SQL to HQL conversion for Complex Query
PostPosted: Mon Jul 17, 2017 9:49 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1275
It's not possible because HQL does not support derived tables.

However, there is nothing wrong with native SQL queries. Otherwise, why do you think JPA and Hibernate support such a feature?

_________________
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: SQL to HQL conversion for Complex Query
PostPosted: Mon Jul 17, 2017 10:53 pm 
Newbie

Joined: Mon Jul 17, 2017 7:53 am
Posts: 2
vlad wrote:
It's not possible because HQL does not support derived tables.


Is there a way to achieve the result we get from the above SQL query without using nativeQuery feature ??? just using HQL or even criteria builder ?


Top
 Profile  
 
 Post subject: Re: SQL to HQL conversion for Complex Query
PostPosted: Tue Jul 18, 2017 1:32 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1275
Quote:
Is there a way to achieve the result we get from the above SQL query without using nativeQuery feature ??? just using HQL or even criteria builder ?


No. There is not.

_________________
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.