如何用JPA Specifications实现拥有指定数量书籍的用户查询

需要实现接口 /users?numberOfBooksGreatherThan=5，获取拥有5本以上书籍的用户列表，计划使用JPA Specification API实现。

尝试的实现代码如下：

public static Specification<User> userNumberOfBooksGreaterThan(Long number){
    return ((root, query, criteriaBuilder) -> {
        Join<User, Book> userBooks = root.join("userBooksList");
        return criteriaBuilder.greaterThan(criteriaBuilder.count(userBooks.get("owner")), number);
    });
}

执行后抛出错误：

org.postgresql.util.PSQLException: ERROR: aggregate functions are not allowed in WHERE 

后台生成的SQL语句为：

select b1_0.user_id,b1_0.name from users b1_0 join books b2_0 on b1_0.user_id=b2_0.owner_id where count(b2_0.owner_id)>5

已知正确的SQL逻辑是左外连接+Group By+Having，如下所示，需要将其转换为JPA Specification实现：

select b1_0.user_id,b1_0.name from users b1_0 left outer join books b2_0 on b1_0.user_id=b2_0.owner_id group by user_id having count(b2_0.owner_id)>=5;

相关实体定义：
User实体：

@Entity(name = "users")
public class User {
    @Id
    private Long userId;
    private String name;
    @JsonManagedReference
    @OneToMany(mappedBy = "owner")
    private List<Book> userBooksList;
}

Book实体：

@Entity(name = "books")
public class Book {
    @Id
    private Long bookId;
    @ManyToOne
    @JoinColumn(name = "ownerId",referencedColumnName = "userId",nullable = false)
    private User owner;
    private Float price;
}

要实现GROUP BY + HAVING的逻辑，需在Specification中调整以下两点：设置分组条件、通过having()替代直接返回聚合条件，代码修改如下：

public static Specification<User> userNumberOfBooksGreaterThan(Long number) {
    return (root, query, criteriaBuilder) -> {
        // 使用左外连接，保留所有用户（包括无书籍的用户）
        Join<User, Book> userBooks = root.join("userBooksList", JoinType.LEFT);
        
        // 按用户主键分组，确保聚合统计的正确性
        query.groupBy(root.get("userId"));
        
        // 添加HAVING子句，过滤书籍数量符合要求的用户
        return criteriaBuilder.having(
            criteriaBuilder.greaterThanOrEqualTo(
                criteriaBuilder.count(userBooks.get("bookId")), 
                number
            )
        );
    };
}

关键细节说明 #

1. 左外连接：使用JoinType.LEFT确保没有书籍的用户也会被纳入查询，后续HAVING子句会自动过滤掉书籍数量不足的用户；
2. 分组逻辑：按用户主键userId分组即可（主键唯一，JPA会自动关联用户的其他字段），若部分JPA实现要求显式分组所有非聚合字段，可添加root.get("name")到groupBy参数中；
3. 聚合统计：选择bookId作为统计字段，避免左外连接时owner字段为null导致的统计误差（count()会自动忽略null值）；
4. 条件匹配：使用greaterThanOrEqualTo对应需求中的"5本以上"（包含5本），若需要严格大于5本，替换为greaterThan即可。

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