Spring Boot 3.1.2 subselect query failing - Hibernate 6.2

Question

I would like to be able to use a native Hibernate query in Spring Boot 3.1.2. The reason for this is as I understand it, HQL can handle nested SELECTS (not in where) better than JPQL.

My model is Bird has OneToMany on HealthCheck HealthCheck has OneToMany on Task (abstract) LengthMeasurements (extends Task) WeightMeasurement (extends Task)

Bird

public class Bird {
private Long id;
@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "bird")
private List<HealthCheck> listHealthCheck = new ArrayList<>();

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;

Length Measurement

public class LengthMeasurements extends Task {

private Double beakLength;
private Double legLength;
private Double armWidth;
private Double armDepth;

WeightMeasurement

public class WeightMeasurements extends Task {
    private Double weight;

The abstract class Task means I can't use hibernate path expressions.

I want to ultimately construct a query the gets the most recent (based on check date) average of the most recent collection of measurements and the average of the most recent weights.

Something like:

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

There is a number of partial examples but they are old and the Spring Boot auto configuration interferes with what I have tried to date.

To give some context on my attempt so far I have the following code:

@Repository
public class CustomBirdRepositoryImpl {

@PersistenceContext
private EntityManager entityManager;


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

    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();
    }

   }
}

If I try to run the query above in JPA I get:

  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";)

UPDATE

When trying the query in a Hibernate session I get the more informative error:

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

How can I perform this sub select statement?

Answer 1

It turns out that inner SELECT statements in hibernate 6 are relatively new feature.

Although the inner SELECT works on its own, when used in the LEFT JOIN the values need aliases.

So this fragment: SELECT MAX(w.healthCheck.catchDateTime), AVG(w.weight) becomes SELECT MAX(w.healthCheck.catchDateTime) AS weight_date, AVG(w.weight) AS avg_weight

Putting it all together :

@Service
@Transactional
public class CustomBirdRepositoryImpl implements CustomBirdRepository {

@PersistenceContext
private EntityManager entityManager;

@Override
public Object customQuery(Long id) {
    return entityManager.createQuery(
                    "SELECT MAX(h.catchDateTime), AVG(l.beakLength), AVG(l.tarsusLength),  AVG(l.tarsusWidth), b.name, b.id, weight_date, avg_weight FROM LengthMeasurements l " +
                            "JOIN l.healthCheck h " +
                            "JOIN h.bird b " +
                            "LEFT JOIN(SELECT MAX(w.healthCheck.catchDateTime) AS weight_date, AVG(w.weight) AS avg_weight, w.healthCheck.bird.id as bid FROM WeightMeasurements w WHERE w.healthCheck.bird.id=:id) AS x " +
                            "ON b.id = x.bid " +
                            "WHERE b.id=:id " +
                            "GROUP BY b.id, h.id ORDER BY l.healthCheck.catchDateTime DESC LIMIT 1")
            .setParameter("id", id)
            .getSingleResult();
    }
}

For completeness CustomBirdRepository

@Repository
public interface CustomBirdRepository {
    Object customQuery(Long id);
}