JPA Projection查询报错：子查询返回多行结果求解决

我有一个Rating JPA实体，尝试通过原生SQL查询获取评分详情，借助JPA Projection特性，期望返回包含totalRatingCount（评分总数）、averageRating（平均评分）以及List<Rating>（评分列表）的结果。但当前查询报错，提示子查询返回多个值。以下是编写的SQL语句与Projection接口定义：

原查询语句 #

select COUNT(pr.product_id) AS totalRatingCount,
        AVG(pr.rating) AS averageRating, 
        (SELECT pri.product_id FROM Product_rating pri 
        WHERE pr.product_id in (13)
        ) as ratings 
from Product_rating pr where pr.product_id=13;

原Projection接口定义 #

interface ProductRatingResultsI {
    long getTotalRatingCount();
    long getAverageRating();
    List<ProductRatingI> getRatings();
}

interface ProductRatingI {
    long getRatingId();
    double getRating();
    String getComment();
    String getReviewrName();
    Date getReviewDate();
    int getCustomerId();
}

你的子查询(SELECT pri.product_id FROM Product_rating pri WHERE pr.product_id in (13))会返回多条product_id记录，但SQL的标量子查询要求必须返回单个值，因此数据库直接抛出错误。同时该子查询逻辑不符合需求——你需要的是该产品的所有评分记录，而非重复的product_id列表。

方案一：拆分查询（简单直接） #

将统计数据查询与评分列表查询分开，在业务层组装结果，这是最易维护的方案：

1. 定义Repository查询方法 #

// 查询评分统计数据
@Query(value = "SELECT COUNT(pr.product_id) AS totalRatingCount, AVG(pr.rating) AS averageRating " +
               "FROM Product_rating pr WHERE pr.product_id = :productId", nativeQuery = true)
ProductRatingStatsI getRatingStats(@Param("productId") Long productId);

// 查询该产品的所有评分列表
@Query(value = "SELECT pr.rating_id AS ratingId, pr.rating, pr.comment, pr.reviewer_name AS reviewrName, " +
               "pr.review_date AS reviewDate, pr.customer_id AS customerId " +
               "FROM Product_rating pr WHERE pr.product_id = :productId", nativeQuery = true)
List<ProductRatingI> getRatingList(@Param("productId") Long productId);

// 新增统计结果的Projection接口
interface ProductRatingStatsI {
    long getTotalRatingCount();
    double getAverageRating(); // 建议将原接口的long改为double，保留平均分精度
}

2. 业务层组装结果 #

ProductRatingResultsI assembleRatingResult(Long productId) {
    ProductRatingStatsI stats = ratingRepository.getRatingStats(productId);
    List<ProductRatingI> ratings = ratingRepository.getRatingList(productId);
    
    return new ProductRatingResultsI() {
        @Override
        public long getTotalRatingCount() {
            return stats.getTotalRatingCount();
        }

        @Override
        public long getAverageRating() {
            // 若必须返回long，可做精度转换，否则建议修改接口返回double
            return (long) Math.round(stats.getAverageRating());
        }

        @Override
        public List<ProductRatingI> getRatings() {
            return ratings;
        }
    };
}

方案二：JPQL构造查询（符合JPA规范） #

使用JPQL的构造函数查询统计数据，再单独查询评分列表，最后组装到自定义DTO中：

1. 创建DTO类 #

public class ProductRatingResults {
    private long totalRatingCount;
    private double averageRating;
    private List<ProductRating> ratings;

    // 构造函数用于接收统计结果
    public ProductRatingResults(long totalRatingCount, double averageRating) {
        this.totalRatingCount = totalRatingCount;
        this.averageRating = averageRating;
    }

    // getter和setter方法
    public long getTotalRatingCount() { return totalRatingCount; }
    public double getAverageRating() { return averageRating; }
    public List<ProductRating> getRatings() { return ratings; }
    public void setRatings(List<ProductRating> ratings) { this.ratings = ratings; }
}

2. Repository查询与组装 #

// 查询统计数据
@Query("SELECT NEW com.yourpackage.ProductRatingResults(COUNT(pr), AVG(pr.rating)) " +
       "FROM ProductRating pr WHERE pr.product.id = :productId")
ProductRatingResults getRatingStats(@Param("productId") Long productId);

// 查询评分列表（假设已有根据productId查询的方法）
List<ProductRating> getByProductId(Long productId);

// 组装结果
ProductRatingResults result = ratingRepository.getRatingStats(productId);
result.setRatings(ratingRepository.getByProductId(productId));

方案三：原生SQL配合结果转换（特殊场景用） #

若必须单次原生SQL查询，可通过手动转换结果集实现：

1. Repository查询方法 #

@Query(value = "SELECT COUNT(pr.product_id) AS totalRatingCount, AVG(pr.rating) AS averageRating, " +
               "pr.rating_id AS ratingId, pr.rating, pr.comment, pr.reviewer_name AS reviewrName, " +
               "pr.review_date AS reviewDate, pr.customer_id AS customerId " +
               "FROM Product_rating pr WHERE pr.product_id = :productId", nativeQuery = true)
List<Object[]> getRawRatingData(@Param("productId") Long productId);

2. 转换结果集 #

ProductRatingResultsI transformRawData(List<Object[]> rawData) {
    if (rawData.isEmpty()) {
        return new ProductRatingResultsI() {
            @Override public long getTotalRatingCount() { return 0; }
            @Override public long getAverageRating() { return 0; }
            @Override public List<ProductRatingI> getRatings() { return Collections.emptyList(); }
        };
    }

    // 统计数据所有行一致，取第一行的前两个值
    long totalCount = (long) rawData.get(0)[0];
    double avgRating = (double) rawData.get(0)[1];

    List<ProductRatingI> ratings = new ArrayList<>();
    for (Object[] row : rawData) {
        ProductRatingI rating = new ProductRatingI() {
            @Override public long getRatingId() { return (long) row[2]; }
            @Override public double getRating() { return (double) row[3]; }
            @Override public String getComment() { return (String) row[4]; }
            @Override public String getReviewrName() { return (String) row[5]; }
            @Override public Date getReviewDate() { return (Date) row[6]; }
            @Override public int getCustomerId() { return (int) row[7]; }
        };
        ratings.add(rating);
    }

    return new ProductRatingResultsI() {
        @Override public long getTotalRatingCount() { return totalCount; }
        @Override public long getAverageRating() { return (long) Math.round(avgRating); }
        @Override public List<ProductRatingI> getRatings() { return ratings; }
    };
}

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