我正在尝试在HQL中执行正确的外部联接。查询创建如下所述:

Query query = this.sessionFactory
            .getCurrentSession()
            .createQuery(
"select
       O.customer.id as id,
       O.customer.firstName as firstName,
       O.customer.lastName as lastName,
       O.customer.address as address,
       O.customer.city as city,
       count(O.id) as totalOrders
 from
       Order O
       right outer join O.customer
 group by
       O.customer.id");

mysql上的SQL查询工作正常,但是HQL查询返回内部联接的结果。

SQL查询是:
select c.id,
    c.firstname,
    c.lastname,
    c.city,
    count(o.id) as total_order
  from orders o right outer join customers c
  on c.id = o.customer_id group by id

最佳答案

问题在于您编写查询的方式。因为您使用O.customer.XXXX,所以Hibernate将Order和Customer之间的内部联接添加到查询中,以便解析O.customer。您需要通过在正确的内部联接中引入O.customer的别名来重新编写查询,以使用正确的内部联接的结果。

select C.id as id, C.firstName as firstName, C.lastName as lastName,
  C.address as address, C.city as city, count(O.id) as totalOrders
from Order O right outer join O.customer C
group by C.id

如果要查看从查询生成的休眠SQL,您会看到它正在执行内连接,并在Order和Customer之间执行右内连接。

09-10 08:09
查看更多