将SQL转换为HQL [英] Converting SQL to HQL

问题描述

我试图将下面的SQL查询转换为HQL，并遇到一些问题。直线逐行转换不起作用，我想知道是否应该在HQL中使用Inner Join？

  SELECT（UNIX_TIMESTAMP（cosc1.change_date） -  UNIX_TIMESTAMP（cosc2.change_date））
 FROM customer_order_state_change cosc1 
 LEF JOIN customer_order_state cos1_new on cosc1.new_state_id = cos1_new.customer_order_state_id 
 LEF JOIN cosc1上的customer_order_state cos1_old。 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 =state1AND cos2_new.name =state2and cosc2.change_date< 2008-11-06 09:00
和cosc2.change_date> 2008-11-06 06:00GROUP 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 =？ + 
和cosc2.changeDate<？ + 
和cosc2.changeDate>？ + 
GROUP BY cosc1.changeDate，cosc2.changeDate; 
  

并收到异常 outer或full join必须后跟路径表达式

解决方案

通常，您使用对象的属性来指定HQL连接，例如，如果Foo和Bar以及Foo.bar是Bar类型的，然后从Foo内部连接f.bar获取，因为b 是连接。据我所知，没有办法在HQL中执行自连接（我可能在这里是错误的）。
$ b

也就是说，Hibernate允许你写（略）增强型）SQL查询与 session.createSQLQuery（...）。

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"

解决方案

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(...).

这篇关于将SQL转换为HQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！