使用子查询作为选择项时Hibernate CriteriaBuilder生成错误SQL输出

问题概述 #

我有Customer和User两个关联实体，二者为一对多/多对一关系。在通过Criteria API分页获取Customer列表时，尝试用子查询统计每个Customer关联的User数量，但Hibernate生成的SQL中，子查询的关联条件不符合预期：原本应该是cu.customer_id = c.id，实际生成了cu.customer_id = u.id。

实体类代码 #

Customer #

@Getter
@Setter
@Entity
public class Customer {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String code;

    @Column(nullable = false)
    private String name;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "customer")
    private Set<User> users = new HashSet<>();
}

User #

@Getter
@Setter
@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @NotEmpty
    private String firstName;

    private String lastName;

    @Email
    private String email;

    @NotEmpty
    private String password;

    private String code;

    @ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "customer_user",
            joinColumns = {@JoinColumn(name = "user_id")},
            inverseJoinColumns = {@JoinColumn(name = "customer_id")})
    private Customer customer;
}

Repository查询方法 #

public PageableData<List<CustomerBaseDto>> getAllCustomers(PageableBaseRequest<GetFilteredCustomers> request) {
        CriteriaQuery<CustomerBaseDto> query = criteriaBuilder.createQuery(CustomerBaseDto.class);
        Root<Customer> root = query.from(Customer.class);

        Subquery<Long> userSubquery = query.subquery(Long.class);
        Root<User> userRoot = userSubquery.from(User.class);

        userSubquery.select(criteriaBuilder.count(userRoot.get(User_.id)))
                .where(criteriaBuilder.equal(userRoot.get(User_.customer).get(Customer_.id), root.get(Customer_.id)));

        query.multiselect(
                root.get(BaseEntity_.id),
                root.get(BaseEntity_.enabled),
                root.get(Customer_.code),
                root.get(Customer_.email),
                root.get(Customer_.name),
                userSubquery);

        return getPageableData(request.getPageNumber(), request.getPageSize(), session, root, query);
    }

Hibernate生成的错误SQL #

select c.id                          as customer_id,
       c.code                        as customer_code,
       c.name                        as customer_name,
       (select count(u.id)
        from users u
                 left outer join customer_user cu on u.id = cu.user_id
        where cu.customer_id = u.id) as user_count
from customer c
limit ? offset ?

使用版本 #

期望生成的SQL #

select c.id                          as customer_id,
       c.code                        as customer_code,
       c.name                        as customer_name,
       (select count(u.id)
        from users u
                 left outer join customer_user cu on u.id = cu.user_id
        where cu.customer_id = c.id) as user_count
from customer c
limit ? offset ?

解决方案 #

1. 修正实体关联映射（核心问题） #

当前User实体的@ManyToOne使用了@JoinTable，这是多对多关系的配置方式，但业务是一对多关系，这会导致Hibernate解析关联时出现逻辑错误。

将User中的关联配置修改为一对多的正确写法：

@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "customer_id") // 直接在users表中添加customer_id外键
private Customer customer;

如果数据库确实需要中间表customer_user（即实际为多对多关系，但业务上限制为一对多），则需要将Customer的@OneToMany改为@ManyToMany，同时保持User的@JoinTable配置，业务逻辑需额外限制每个User仅关联一个Customer。

2. 优化Criteria子查询写法（可选） #

修正映射后，原有的子查询写法即可正常生成正确的关联条件。如果仍有问题，可以尝试通过显式Join关联子查询中的实体，确保关联条件正确：

Subquery<Long> userSubquery = query.subquery(Long.class);
Root<User> userRoot = userSubquery.from(User.class);
// 显式关联User和Customer
Join<User, Customer> userCustomerJoin = userRoot.join(User_.customer);

userSubquery.select(criteriaBuilder.count(userRoot.get(User_.id)))
        .where(criteriaBuilder.equal(userCustomerJoin.get(Customer_.id), root.get(Customer_.id)));

验证效果 #

修正映射后，Hibernate将生成符合预期的SQL，子查询中的WHERE条件会正确关联外部查询的c.id，从而得到每个Customer对应的User数量。

内容的提问来源于stack exchange，提问作者Elçin Şahmərdanov