HQL Hibernate查询中的java左连接

Question

我有2个Entite，每个都存储在mysql表中。 1.productA:{productId（pk），desc，date} 2.productB:{productId（pk），数量，类型，日期}

我要运行此SQL查询：

     select a.* 
     from productA a left join productB b using(productId)   
     where b.productId is null

（从a退回b中不存在的所有产品）

是否可以在Hibernate中编写此查询

谢谢

Answer 1

Is it possible to write this query in Hibernate?

当然可以。从JPA specification 2.1 (4.4.5.2 Left Outer Joins)：

LEFT JOIN and LEFT OUTER JOIN are synonymous. They enable the retrieval of a set of entities where matching values in the join condition may be absent. The syntax for a left outer join is

LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
[join_condition]

An outer join without a specified join condition has an implicit join condition over the foreign key relationship corresponding to the join_association_path_expression. It would typically be mapped to a SQL outer join with an ON condition on the foreign key relationship as in the queries below: Java Persistence query language:

SELECT s.name, COUNT(p)
FROM Suppliers s LEFT JOIN s.products p
GROUP BY s.name

SQL:

SELECT s.name, COUNT(p.id)
FROM Suppliers s LEFT JOIN Products p
ON s.id = p.supplierId
GROUP By s.name

An outer join with an explicit ON condition would cause an additional specified join condition to be added to the generated SQL: Java Persistence query language:

SELECT s.name, COUNT(p)
FROM Suppliers s LEFT JOIN s.products p
ON p.status = 'inStock'
GROUP BY s.name

SQL:

SELECT s.name, COUNT(p.id)
FROM Suppliers s LEFT JOIN Products p
ON s.id = p.supplierId AND p.status = 'inStock'
GROUP BY s.name

Note that the result of this query will be different from that of the following query:

SELECT s.name, COUNT(p)
FROM Suppliers s LEFT JOIN s.products p
WHERE p.status = 'inStock'
GROUP BY s.name

The result of the latter query will exclude suppliers who have no products in stock whereas the former query will include them.

An important use case for LEFT JOIN is in enabling the prefetching of related data items as a side effect of a query. This is accomplished by specifying the LEFT JOIN as a FETCH JOIN as described below.