Converting SQL to HQL

Question

I'm trying to convert the below SQL query to HQL and am having a few issues. A straight line by line conversion doesn't work, I am wondering if I should be using an Inner Join in the HQL?

        SELECT (UNIX_TIMESTAMP(cosc1.change_date) - UNIX_TIMESTAMP(cosc2.change_date)) 
        FROM customer_order_state_change cosc1  
        LEFT JOIN customer_order_state cos1_new on cosc1.new_state_id = cos1_new.customer_order_state_id  
        LEFT JOIN customer_order_state cos1_old on cosc1.old_state_id = cos1_old.customer_order_state_id  
        LEFT JOIN customer_order_state_change cosc2 on cosc2.customer_order_id = cosc1.customer_order_id  
        LEFT JOIN customer_order_state cos2_new on cosc2.new_state_id = cos2_new.customer_order_state_id  
        LEFT JOIN customer_order_state cos2_old on cosc2.old_state_id = cos2_old.customer_order_state_id 
        WHERE cos1_new.name = "state1" AND  cos2_new.name = "state2" and cosc2.change_date < "2008-11-06 09:00" 
AND cosc2.change_date > "2008-11-06 06:00" GROUP BY cosc1.change_date, cosc2.change_date ;

Query returns time in seconds between state changes for a customer order.

The state names and dates are dynamically inserted into the query.

Edit: Just tried this

"SELECT (UNIX_TIMESTAMP(cosc1.changeDate) - UNIX_TIMESTAMP(cosc2.changeDate))" + 
     " FROM" + 
     " " + CustomerOrderStateChange.class.getName() + " as cosc1" +
     " INNER JOIN " + CustomerOrderStateChange.class.getName() +  " as cosc2" +
     " WHERE cosc1.newState.name = ?" +
     " AND cosc1.order.id = cosc2.order.id" + 
     " AND cosc2.newState.name = ?" +
     " AND cosc2.changeDate < ?" +
     " AND cosc2.changeDate > ?" + 
     " GROUP BY cosc1.changeDate, cosc2.changeDate";

and received exception"outer or full join must be followed by path expression"

Answer 1

Typically you HQL joins are specified using the property on the object, eg, if class Foo and Bar and Foo.bar is of type Bar, then from Foo f inner join f.bar as b is the join. As far as I know, there's no way of performing a self-join in HQL (I could be wrong here).

That said, Hibernate allows you to write (slightly enhanced) SQL queries with session.createSQLQuery(...).

Answer 2

Ended up changing to use native SQL and a PreparedStatement as it seems that Hibernate's session.createSQLQuery() will only work for Managed Entities.