Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA - How to write correct HQL?

 
Nakataa Kokuyo
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:



 
Carles Gasques
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
 
James Boswell
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • 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
Chrome Eclipse IDE Hibernate
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic