HQL右外连接 [英] HQL right outer join

问题描述

我试图在HQL中执行正确的外连接。创建查询的过程如下所示：

pre code查询查询= this.sessionFactory
.getCurrentSession（）
.createQuery（
选择O.customer.id作为id，O.customer.firstName作为firstName，O.customer.lastName作为lastName，O.customer.address作为地址，O.customer.city作为城市，count （O.id）作为来自Order O的外部联合O.customer组的OOrders作为O.customer.id的totalOrders）;

mysql上的SQL查询工作正常，但HQL查询正在返回内连接的结果。 / p>

SQL查询是：

  select c.id，
 c.firstname，
 c.lastname，
 c.city，
 count（o.id）作为total_order 
来自订单o右外部加入客户c 
 on c.id = o.customer_id group by id 
  

解决方案

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

code>选择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 $ b订单O中的$ b右外部连接O.customer C
group by C.id

如果您要查看由查询生成的hibernate的SQL，您会发现它在和Customer 之间执行内部联接和右侧内部联接

。

I am trying to perform right outer join in HQL. Query creation is done as mentioned below:

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");

SQL query on mysql is working fine, but the HQL query is returning the result for inner join.

SQL query is:

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

解决方案

The problem is with the way you've written your query. Because you use O.customer.XXXX, Hibernate adds an inner join between Order and Customer to the query in order to resolve O.customer. You need to re-write your query to use the results of the right inner join by introducing an alias for O.customer in the right inner join.

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

If you were to look at the SQL that hibernate generated from your query, you would see that it is performing both an inner join and a right inner join between Order and Customer.

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