This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java and have Dr. Raoul-Gabriel Urma & Richard Warburton on-line!
See this thread for details.
Win a copy of Real-World Software Development: A Project-Driven Guide to Fundamentals in Java this week in the Agile and Other Processes forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Liutauras Vilda
  • Knute Snortum
  • Bear Bibeault
Sheriffs:
  • Devaka Cooray
  • Jeanne Boyarsky
  • Junilu Lacar
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • salvin francis
Bartenders:
  • Tim Holloway
  • Piet Souris
  • Frits Walraven

JPA - How to write correct HQL?

 
Ranch Hand
Posts: 189
  • Mark post as helpful
  • send pies
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • Quote
  • Report post to moderator
hmm...Still the same
 
keep an eye out for scorpions and black widows. But the tiny ads are safe.
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!