Spring Boot 3.1.2中Hibernate子查询执行失败问题求助

需求说明 #

在Spring Boot 3.1.2项目中，需使用原生Hibernate查询（HQL）实现以下需求：

• 模型关系：Bird与HealthCheck为一对多，HealthCheck与抽象类Task为一对多，LengthMeasurements、WeightMeasurements继承自Task
• 查询目标：获取单只鸟最新检查日期对应的长度测量平均值（喙长、臂宽、臂深），以及最新体重测量平均值，结果格式如下：

+---------+-----------+---------------------+------------+-----------+-----------+--------------------+------------+
| bird.id | bird.name | recent measure date | avg length | avg width | avg depth | recent weight date | avg weight |
+---------+-----------+---------------------+------------+-----------+-----------+--------------------+------------+

模型代码 #

Bird类 #

public class Bird {
    private Long id;
    private String name; // 补充结果所需的name字段
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "bird")
    private List<HealthCheck> listHealthCheck = new ArrayList<>();
    // 省略getter/setter
}

HealthCheck类 #

public class HealthCheck {
    private Long id;

    @JsonManagedReference
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    private Bird bird;
    @JsonManagedReference
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy="healthCheck")
    private List<Task> tasks;
    private LocalDateTime catchDateTime;
    // 省略getter/setter
}

LengthMeasurements类 #

public class LengthMeasurements extends Task {
    private Double beakLength;
    private Double legLength;
    private Double armWidth;
    private Double armDepth;
    // 省略getter/setter
}

WeightMeasurements类 #

public class WeightMeasurements extends Task {
    private Double weight;
    // 省略getter/setter
}

问题重现 #

尝试自定义Repository实现查询时，出现语法和别名错误：

尝试的代码 #

@Repository
public class CustomBirdRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

    final SessionFactory sf = entityManager.unwrap(Session.class).getSessionFactory();

    public void customHql(Long id) {
        Session session = sf.openSession();

        String hql = " SELECT MAX(h.catchDateTime), AVG(l.beakLength), " +
                "AVG(l.tarsusLength),  AVG(l.tarsusWidth), b.name FROM LengthMeasurements l " +
                " JOIN l.healthCheck.bird b " +
                " JOIN l.healthCheck h " +
                " LEFT JOIN (SELECT MAX(w.healthCheck.catchDateTIme), AVG(w.weight), w.healthCheck.bird.id as bid FROM WeightMeasurements w WHERE w.healthCheck.bird.id=1) AS x" +
                " ON x.bid = w.healthCheck.bird.id" +
                " WHERE b.id=1 GROUP BY b.id, h.id ORDER BY l.healthCheck.catchDateTime DESC LIMIT 1";
        org.hibernate.query.Query<BirdDetailsDto> query = session.createQuery(hql);

        for(BirdDetailsDto b: query.getResultList()) {
            b.toString();
        }
    }
}

错误信息 #

1. JPA运行时语法错误：

src\main\java\com\nz\kiwi\repository\BirdRepository.java:54: error: ')' expected
    "WHERE b.id = :id ORDER BY l.healthCheck.catchDate DESC LIMIT 1) lm";)

2. Hibernate Session运行时错误：

Caused by: java.lang.IllegalArgumentException: Component at index 0 has no alias, but alias is required

解决方案 #

问题根源在于HQL子查询语法错误、字段未加别名，以及未正确关联最新检查日期的统计数据。以下是修正后的实现：

1. 定义结果DTO #

创建用于接收查询结果的DTO类：

public class BirdDetailsDto {
    private Long birdId;
    private String birdName;
    private LocalDateTime recentMeasureDate;
    private Double avgBeakLength;
    private Double avgArmWidth;
    private Double avgArmDepth;
    private LocalDateTime recentWeightDate;
    private Double avgWeight;

    // 构造函数参数顺序需与HQL查询字段顺序完全一致
    public BirdDetailsDto(Long birdId, String birdName, LocalDateTime recentMeasureDate,
                          Double avgBeakLength, Double avgArmWidth, Double avgArmDepth,
                          LocalDateTime recentWeightDate, Double avgWeight) {
        this.birdId = birdId;
        this.birdName = birdName;
        this.recentMeasureDate = recentMeasureDate;
        this.avgBeakLength = avgBeakLength;
        this.avgArmWidth = avgArmWidth;
        this.avgArmDepth = avgArmDepth;
        this.recentWeightDate = recentWeightDate;
        this.avgWeight = avgWeight;
    }

    // 省略getter/toString方法
}

2. 修正后的HQL查询实现 #

@Repository
public class CustomBirdRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

    public BirdDetailsDto getBirdLatestMeasurements(Long birdId) {
        Session session = entityManager.unwrap(Session.class);

        // 子查询1：获取该鸟最新长度测量对应的检查日期
        String latestLengthCheckSubquery = "(SELECT MAX(h.catchDateTime) " +
                "FROM HealthCheck h " +
                "JOIN h.tasks t ON TYPE(t) = LengthMeasurements " +
                "WHERE h.bird.id = :birdId)";

        // 子查询2：获取该鸟最新体重测量对应的检查日期
        String latestWeightCheckSubquery = "(SELECT MAX(h.catchDateTime) " +
                "FROM HealthCheck h " +
                "JOIN h.tasks t ON TYPE(t) = WeightMeasurements " +
                "WHERE h.bird.id = :birdId)";

        // 主查询：关联最新日期的统计数据
        String hql = "SELECT " +
                "b.id as birdId, " +
                "b.name as birdName, " +
                "lm.recentMeasureDate, " +
                "lm.avgBeakLength, " +
                "lm.avgArmWidth, " +
                "lm.avgArmDepth, " +
                "wm.recentWeightDate, " +
                "wm.avgWeight " +
                "FROM Bird b " +
                "LEFT JOIN (" +
                    "SELECT " +
                    "h.bird.id as birdId, " +
                    "MAX(h.catchDateTime) as recentMeasureDate, " +
                    "AVG(l.beakLength) as avgBeakLength, " +
                    "AVG(l.armWidth) as avgArmWidth, " +
                    "AVG(l.armDepth) as avgArmDepth " +
                    "FROM LengthMeasurements l " +
                    "JOIN l.healthCheck h " +
                    "WHERE h.catchDateTime = " + latestLengthCheckSubquery +
                    "AND h.bird.id = :birdId " +
                    "GROUP BY h.bird.id" +
                ") lm ON lm.birdId = b.id " +
                "LEFT JOIN (" +
                    "SELECT " +
                    "h.bird.id as birdId, " +
                    "MAX(h.catchDateTime) as recentWeightDate, " +
                    "AVG(w.weight) as avgWeight " +
                    "FROM WeightMeasurements w " +
                    "JOIN w.healthCheck h " +
                    "WHERE h.catchDateTime = " + latestWeightCheckSubquery +
                    "AND h.bird.id = :birdId " +
                    "GROUP BY h.bird.id" +
                ") wm ON wm.birdId = b.id " +
                "WHERE b.id = :birdId";

        Query<BirdDetailsDto> query = session.createQuery(hql, BirdDetailsDto.class);
        query.setParameter("birdId", birdId);

        return query.uniqueResult();
    }
}

关键修正点 #

• 给所有投影字段添加别名，匹配DTO构造函数参数，解决"Component at index 0 has no alias"错误
• 使用TYPE(t) = 子类名筛选抽象类Task的具体子类，避免路径表达式限制
• 拆分逻辑：先通过子查询获取最新检查日期，再基于该日期统计对应测量值，确保只统计最新批次数据
• 替换硬编码ID为参数:birdId，提升复用性
• 使用LEFT JOIN确保即使某类测量数据不存在，也能返回其他有效数据

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