aspose file tools*
The moose likes Object Relational Mapping and the fly likes JPA - How to write correct HQL? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA - How to write correct HQL?" Watch "JPA - How to write correct HQL?" New topic
Author

JPA - How to write correct HQL?

Nakataa Kokuyo
Ranch Hand

Joined: Jul 24, 2011
Posts: 187
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

Joined: Apr 19, 2013
Posts: 199
    
    1
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

Joined: Jul 24, 2011
Posts: 187
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

Joined: Apr 19, 2013
Posts: 199
    
    1
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

Joined: Jul 24, 2011
Posts: 187
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

Joined: Jul 24, 2011
Posts: 187
One more update is that, the jointcolumn ITEM_GID is not unique....not sure if that caused the issue
James Boswell
Bartender

Joined: Nov 09, 2011
Posts: 1030
    
    5

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

Joined: Jul 24, 2011
Posts: 187
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

Joined: Nov 09, 2011
Posts: 1030
    
    5

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

Joined: Jul 24, 2011
Posts: 187
hmm...Still the same
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JPA - How to write correct HQL?