JPA Criteria API与原生SQL排序结果不一致问题求助

在PostgreSQL中使用JPA（Hibernate）进行数据访问时，需要按照ReviewLike中positive=true的数量对Review列表排序。但获取包含所有关联关系的Review列表并进行排序分页时，发现Criteria API与原生SQL结果不一致：原生SQL排序正常，Criteria API返回的结果顺序混乱。尝试Blaze Persistence也未成功。

实体类定义 #

WebResource #

@Entity
@Table(name = "web_resource")
public class WebResource {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    private String url;

    @Enumerated(EnumType.STRING)
    @Column(name = "resource_type")
    private ResourceType resourceType;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="parent_id")
    private WebResource parent;
}

Review #

@Entity
@Table(name = "reviews")
public class Review {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    @Column(name = "user_id")
    private String userId;

    @Column(name = "resource_id")
    private String resourceId;

    @Column(name = "review_id")
    private String reviewId;

    private Byte rating;

    private String text;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "resource_id", nullable = false, insertable = false, updatable = false)
    private WebResource webResource;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false, insertable = false, updatable = false)
    private User user;

    @OneToMany(mappedBy = "review", fetch = FetchType.LAZY)
    private Set<ReviewLike> likes;
}

User #

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

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    @Column(name = "user_name")
    private String userName;

    private String email;

    private String password;

    @Column(name = "full_name")
    private String fullName;

    @Column(name = "avatar_file_name")
    private String avatarFileName;

    @Column(name = "email_confirmed")
    private Boolean emailConfirmed;
}

ReviewLike #

@Entity
@Table(name = "reviews_likes")
public class ReviewLike {

    @Id
    @GeneratedValue(generator = "uuid")
    @GenericGenerator(name = "uuid", strategy = "uuid")
    @Column(columnDefinition = "CHAR(36)")
    private String id;

    @Column(name = "user_id")
    private String userId;

    @Column(name = "review_id")
    private String reviewId;

    private Boolean positive;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "review_id", nullable = false, insertable = false, updatable = false)
    private Review review;
}

正常工作的原生SQL #

select review.*
from reviews review
         join web_resource webResource on review.resource_id = webResource.id
         left join reviews_likes reaction on review.id = reaction.review_id
         join users u on review.user_id = u.id
group by review.id, webResource.url, webResource.resource_type
order by sum(case when reaction.positive = true then 1 else 0 end) DESC
limit 10 offset 0;

错误的Criteria API（Spring Specification）代码 #

List<Predicate> predicates = new ArrayList<>();

SetJoin<Review, ReviewLike> reviewLikeJoin = reviewRoot.joinSet(Review_.LIKES);
Expression<?> sortExpression sortExpression = criteriaBuilder.sum(
    criteriaBuilder.<Integer>selectCase()
    .when(criteriaBuilder.equal(reviewLikeJoin.get(ReviewLike_.POSITIVE), true), 1)
    .otherwise(0)
);

criteriaQuery.groupBy(reviewRoot.get(Review_.ID));
criteriaBuilder.desc(sortExpression);

错误的Blaze Persistence代码 #

CriteriaBuilder<Review> criteriaBuilder = criteriaBuilderFactory.create(entityManager, Review.class, "review")
            .leftJoinFetch("review.likes", "reviewLike")
            .leftJoinFetch("review.webResource", "webResource")
            .leftJoinFetch("review.user", "user")
            .groupBy("review.id", "review.likes.reviewId", "review.likes.id")
            .orderByDesc("SUM(case when reviewLike.positive = true then 1 else 0 end)");

List<Review> reviews = criteriaBuilder.getResultList();

问题分析与解决方案 #

Criteria API 问题点与修复 #

1. 连接类型错误：joinSet(Review_.LIKES)是内连接，会过滤掉没有点赞的Review，和原生SQL的left join逻辑不一致，需改为leftJoinSet。
2. 排序规则未生效：代码仅创建了排序表达式，但未将其添加到criteriaQuery.orderBy()中，导致排序不生效。
3. 分组字段完整性：JPA规范要求分组包含所有非聚合查询字段，可显式添加必要字段，或开启Hibernate的hibernate.query.allow_partial_group_by配置允许仅按主键分组。

修复后的Criteria API代码：

// 左连接likes，保留无点赞的Review
SetJoin<Review, ReviewLike> reviewLikeJoin = reviewRoot.leftJoinSet(Review_.LIKES);

// 构建positive=true的点赞数统计表达式
Expression<Integer> positiveCount = criteriaBuilder.sum(
    criteriaBuilder.<Integer>selectCase()
        .when(criteriaBuilder.equal(reviewLikeJoin.get(ReviewLike_.POSITIVE), true), 1)
        .otherwise(0)
);

// 按Review主键分组
criteriaQuery.groupBy(reviewRoot.get(Review_.ID));

// 添加排序规则到查询
criteriaQuery.orderBy(criteriaBuilder.desc(positiveCount));

// 关联webResource和user（与原生SQL保持一致）
reviewRoot.join(Review_.WEB_RESOURCE);
reviewRoot.join(Review_.USER);

// 执行分页查询
TypedQuery<Review> query = entityManager.createQuery(criteriaQuery);
query.setFirstResult(0);
query.setMaxResults(10);
List<Review> result = query.getResultList();

Blaze Persistence 问题点与修复 #

错误在于groupBy包含了review.likes的字段，导致每个点赞记录生成独立分组，破坏了按Review聚合的逻辑。只需按review.id分组即可，Blaze Persistence会自动处理关联字段的抓取。

修复后的Blaze Persistence代码：

CriteriaBuilder<Review> criteriaBuilder = criteriaBuilderFactory.create(entityManager, Review.class, "review")
        .leftJoinFetch("review.likes", "reviewLike")
        .leftJoinFetch("review.webResource", "webResource")
        .leftJoinFetch("review.user", "user")
        // 仅按Review主键分组
        .groupBy("review.id")
        // 统计positive=true的数量并降序排序
        .orderByDesc("SUM(CASE WHEN reviewLike.positive = TRUE THEN 1 ELSE 0 END)");

// 添加分页参数
criteriaBuilder.setFirstResult(0);
criteriaBuilder.setMaxResults(10);

List<Review> reviews = criteriaBuilder.getResultList();

额外注意事项 #

• 确保实体类元模型（如Review_、ReviewLike_）正确生成，避免字段引用错误。
• 分页必须在数据库层面完成，禁止内存中二次排序，否则会导致分页结果错乱。
• 开启Hibernate的show_sql日志，对比生成的SQL与原生SQL的差异，快速定位问题。

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