为什么hibernate hql不同，导致左连接的SQL不同？ [英] why does hibernate hql distinct cause an sql distinct on left join?

问题描述

我有这个测试HQL：

 从Order中选择不同的o o left join fetch o.lineItems 
  

和它确实会产生一个不同的SQL：

  select distinct order0_.id as id61_0_，orderline1_.order_id as order1_62_1 _... 
  

$ b

SQL结果集总是相同的（有和没有SQL不同）：

 订单ID |订单名称| orderline id |命令行名称
 --------- + ------------ + -------------- + ------ --------- 
 1 | foo | 1 | foo item 
 1 | foo | 2 |酒吧项目
 1 | foo | 3 |测试项目
 2 |空的| NULL | NULL 
 3 | bar | 4 | qwerty item 
 3 | bar | 5 | asdfgh item 
  

为什么hibernate会生成独特的SQL？ SQL区别没有任何意义，并使查询比需要的慢。
这与常见问题相反，其中提到在这种情况下，hql不同仅仅是一条捷径对于结果转换器：

session.createQuery（从订单中选择不同的
oleft join fetch
o .lineItems）。list（）;

看起来您在这里使用SQL DISTINCT关键字。当然，这不是SQL，这是HQL。在这种情况下，这个区别只是结果变换器的一个捷径。是的，在其他情况下，HQL不同将直接转换为SQL DISTINCT。不是在这种情况下：你不能在SQL级别过滤出重复项，产品/联接的本质禁止这样做 - 你需要重复项或者你不需要获得所有数据。

谢谢

解决方案

仔细看看sql语句hibernate生成 - 是的，它确实使用了distinct关键字，但不是以我期望的方式（或者Hibernate常见问题所暗示的方式），即返回一组独特或独特的命令。

它不使用distinct关键字来返回不同的订单，因为在该SQL查询中，考虑到您也指定了的连接，这不会有意义。 / p>

生成的sql集合仍然需要由ResultTransformer处理，显然sql集合包含重复的订单。这就是为什么他们说HQL distinct关键字不直接映射到SQL distinct关键字。

I've got this test HQL:

select distinct o from Order o left join fetch o.lineItems

and it does generate an SQL distinct without an obvious reason:

select distinct order0_.id as id61_0_, orderline1_.order_id as order1_62_1_...

The SQL resultset is always the same (with and without an SQL distinct):

order id | order name | orderline id | orderline name
---------+------------+--------------+---------------
       1 | foo        |            1 | foo item
       1 | foo        |            2 | bar item
       1 | foo        |            3 | test item
       2 | empty      |         NULL | NULL
       3 | bar        |            4 | qwerty item
       3 | bar        |            5 | asdfgh item

Why does hibernate generate the SQL distinct? The SQL distinct doesn't make any sense and makes the query slower than needed. This is contrary to the FAQ which mentions that hql distinct in this case is just a shortcut for the result transformer:

session.createQuery("select distinct o from Order o left join fetch o.lineItems").list();

It looks like you are using the SQL DISTINCT keyword here. Of course, this is not SQL, this is HQL. This distinct is just a shortcut for the result transformer, in this case. Yes, in other cases an HQL distinct will translate straight into a SQL DISTINCT. Not in this case: you can not filter out duplicates at the SQL level, the very nature of a product/join forbids this - you want the duplicates or you don't get all the data you need.

thanks

解决方案

Have a closer look at the sql statement that hibernate generates - yes it does use the "distinct" keyword but not in the way I think you are expecting it to (or the way that the Hibernate FAQ is implying) i.e. to return a set of "distinct" or "unique" orders.

It doesn't use the distinct keyword to return distinct orders, as that wouldn't make sense in that SQL query, considering the join that you have also specified.

The resulting sql set still needs processing by the ResultTransformer, as clearly the sql set contains duplicate orders. That's why they say that the HQL distinct keyword doesn't directly map to the SQL distinct keyword.

这篇关于为什么hibernate hql不同，导致左连接的SQL不同？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！