我正在尝试将以下SQL查询转换为HQL,并且遇到了一些问题。直线逐行转换不起作用,我想知道是否应该在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 ;
查询返回客户订单状态更改之间的时间(以秒为单位)。
状态名称和日期会动态插入查询中。
编辑:刚刚尝试过
"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";
并收到异常“ 外部或完全连接后必须跟路径表达式 ”
通常,您使用对象上的属性指定HQL连接,例如,如果类Foo和Bar和Foo.bar是Bar类型,则from Foo f inner join f.bar as b该连接是。据我所知,无法在HQL中执行自联接(在这里我可能错了)。
from Foo f inner join f.bar as b
也就是说,Hibernate允许您使用编写(略有增强)SQL查询session.createSQLQuery(...)。
session.createSQLQuery(...)