• 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:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
Sheriffs:
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

JPA - How to write correct HQL?

 
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Good day,

I have a DB query as following which give me correct result, and when i try on my HQL and it provide me incorrect result.

Database:



SQL query :
Result : 2 rows return correctly


My HQL:
Result: Return 2 set of 4 rows records, both set's records are same. (Question: 1- Why it show me 2 set of same record? 2- What need to change in my HQL?)



Below is my entity class:



 
Ranch Hand
Posts: 199
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

Seems that is converted to an outer join...
What happens when you add "AND s.id = status.slaitemid' to the query?
Could you enable sql trace?


Best regards,

 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Carles for enlightenment, how do we enable the SQL trace? I'm using JPA and Hibernate with persistence.xml and Spring applicationContext.xml

Between, I already set my relationship and I'm using 'JOIN', it suppose treat as inner join right ?
 
Carles Gasques
Ranch Hand
Posts: 199
1
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
To enable sql trace add <property name="hibernate.show_sql" value="true"/> to your persistence unit properties (persistence xml).


Cheers,

 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Carles,

My IDE show the SQL like below



found there is statement show in console about "composite-id class does not override equals() and hashcode()", do i need to do something for it but how?

p/s: I modified above to avoid company data security concern.

Question :
1. Why it fire 2 query ?
2. Look like first SQL is correct, but not sure why second SQL have ITEM_GID=?
 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
One more update is that, the jointcolumn ITEM_GID is not unique....not sure if that caused the issue
 
Bartender
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think this HQL will work for you:


You need to be careful not to mix property names (Java) and column names (SQL).
 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks James for your guidance, I have try and the result appear the same ...I think it could be ITEM_gid duplication.

Currently, I'm pull all the record and use the if statement to filter out unnecessary return record..sound bad but working at the moment...I'll definitely try to make a simple sample after my tasks meet the timeline..

I'm just curious, our entity already define the relationship, do we still need to join again in the HQL? would it take as extra join?
 
James Boswell
Bartender
Posts: 1051
5
Hibernate Eclipse IDE Chrome
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How about this:

It looks like unique entities are not returned by default. Hence, you use the distinct keyword to remove duplicates.
 
Nakataa Kokuyo
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hmm...Still the same
 
WHAT is your favorite color? Blue, no yellow, ahhhhhhh! Tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic