• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Complex HQL query conversion (from SQL)

 
Rick Smith
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, I'm having trouble constructing a query in HQL for my mapping.

Please excuse the size of the following but I feel including the bits of code concered will help matters, (I've tried to remove fields/detail not relevent to this example).

I have a database which maintains the status of an order.

The current status of the order is determined by the most recent status record, it is possible for an order to swap between states such that it may end up in the same state twice during its life-cycle.


My hibernate mapping is:

Which maps to the Java classes:

What I am trying to retrieve is the set of Orders in a specified state, I have an SQL query which will retrieve the data I want (for my unit test) but I do not expect this to traslate direclty into HQL:

I have tried a number of different approaches that I thought would work to take advangate of my mapping rather than falling back on SQL in my Data access layer but I'm now struggling to make progress, any help would be greatfully recieved.
 
Santosh Ramachandrula
Ranch Hand
Posts: 252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you are tring to use Native Sql in Hibernate? If yes please see http://www.hibernate.org/hib_docs/reference/en/html/querysql.html
 
Rick Smith
Greenhorn
Posts: 24
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Santosh,

Thank you for your reply.

In aswer to your question, no I did not want to query using native SQL, I wanted to use HQL, late yesturday I had help from a collegue who managed to get most of what I wanted working, the query we used is below:


This passed my unit tests and returned to results I was expecting so unless anyone can see any issues with it I should be happy! The only thing left to do it to make a variant of the query which returned a cut down object (OrderLite) for summary list pages - perhaps this is somthing that could/should be done is SQL for some reason?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic