如何用JPQL编写带子查询的左外连接查询未分配分支的产品？

针对你需要查询指定租户下未分配给某Branch的Product需求，这里提供几种符合JPQL规范的实现方式，完全匹配你原生SQL的逻辑，且能直接返回Hibernate实体，无需处理原生查询的字段映射问题：

方式一：使用NOT EXISTS子查询（推荐，性能更优） #

这种写法和你原生SQL的逻辑最贴近，通过子查询检查Product是否已关联目标Branch：

SELECT p
FROM Product p
WHERE p.tenantId = :tenantId
AND NOT EXISTS (
    SELECT 1
    FROM Branch b
    JOIN b.products bp
    WHERE b.id = :branchId
    AND bp.id = p.id
)

方式二：使用集合成员检查（写法最简洁） #

利用JPQL的MEMBER OF语法，直接判断目标Branch是否不在Product的关联集合中：

SELECT p
FROM Product p
WHERE p.tenantId = :tenantId
AND :branch NOT MEMBER OF p.branches

注意：这里的:branch参数需要传入完整的Branch实体对象，也可以调整为基于ID的判断：

SELECT p
FROM Product p
WHERE p.tenantId = :tenantId
AND NOT EXISTS (
    SELECT 1
    FROM p.branches b
    WHERE b.id = :branchId
)

方式三：LEFT JOIN关联筛选 #

模拟原生SQL的LEFT JOIN逻辑，只关联目标Branch，然后筛选未匹配到的Product：

SELECT p
FROM Product p
LEFT JOIN p.branches b ON b.id = :branchId
WHERE p.tenantId = :tenantId
AND b IS NULL

实体类配置要求 #

确保你的Product和Branch实体已正确配置多对多关联：

// Product 实体
@Entity
@Table(name = "product")
public class Product {
    @Id
    private Long id;
    
    @Column(name = "tenant_id")
    private String tenantId;
    
    @ManyToMany
    @JoinTable(
        name = "branch_product",
        joinColumns = @JoinColumn(name = "product_id"),
        inverseJoinColumns = @JoinColumn(name = "branch_id")
    )
    private Set<Branch> branches = new HashSet<>();
    
    // 省略getter、setter
}

// Branch 实体
@Entity
@Table(name = "branch")
public class Branch {
    @Id
    private Long id;
    
    @ManyToMany(mappedBy = "branches")
    private Set<Product> products = new HashSet<>();
    
    // 省略getter、setter
}

代码调用示例 #

用EntityManager执行JPQL查询，直接获取Product实体列表：

String jpql = "SELECT p FROM Product p WHERE p.tenantId = :tenantId AND NOT EXISTS (SELECT 1 FROM Branch b JOIN b.products bp WHERE b.id = :branchId AND bp.id = p.id)";
TypedQuery<Product> query = entityManager.createQuery(jpql, Product.class);
query.setParameter("tenantId", "your-tenant-id");
query.setParameter("branchId", 123L); // 目标Branch的ID
List<Product> unassignedProducts = query.getResultList();

这些JPQL写法都会自动由Hibernate转换为适配PostgreSQL的SQL，并且直接返回完整的Product实体，包括JSON类型字段（只要你已为JSON列配置了Hibernate支持的类型映射，比如@Type(type = "jsonb")或者Hibernate 6+的原生JSON类型），无需手动处理原生查询的字段映射工作。

内容的提问来源于stack exchange，提问作者Kevin