JPA Criteria Tuple query fails with missing columns from join in group by clause (Spring Boot 2.7.8-CodePudding

I am trying to use the JPA Criteria API to filter the results and aggregate them using simple count, min, avg and max. I am using Spring Boot 2.7.8, so I am trying to use Interface-projections such that these aggregated results look the same as the simpler queries done automatically by the Spring repositories.

My domain entity (simplified for brevity) looks like this:

@Entity
@Table(name = "vehicle_stopped")
@IdClass(VehicleStopped.VehicleStoppedPK.class)
public class VehicleStopped implements Serializable {

    @Id
    @Column(name = "stopped_session_uuid", nullable = false)
    private String stoppedSessionUuid;

    @Id
    @Column(name = "start_ts", nullable = false)
    private OffsetDateTime startTs;

    @Column(name = "end_ts", nullable = false)
    private OffsetDateTime endTs;

    @Column(name = "duration_seconds")
    private Double durationSeconds;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "zone_id")
    private CameraZone cameraZone;

    @Override
    public VehicleStoppedPK getId() {
        VehicleStopped.VehicleStoppedPK pk = new VehicleStopped.VehicleStoppedPK();
        pk.setStartTs(this.getStartTs());
        pk.setStoppedSessionUuid(this.getStoppedSessionUuid());

        return pk;
    }

    public OffsetDateTime getEndTs() {
        return endTs;
    }

    public void setEndTs(OffsetDateTime endTs) {
        this.endTs = endTs;
    }

    public Double getDurationSeconds() {
        return durationSeconds;
    }

    public void setDurationSeconds(Double durationSeconds) {
        this.durationSeconds = durationSeconds;
    }


    public CameraZone getCameraZone() {
        return cameraZone;
    }

    public void setCameraZone(CameraZone cameraZone) {
        this.cameraZone = cameraZone;
    }

    public VehicleType getVehicleType() {
        return vehicleType;
    }

    public void setVehicleType(VehicleType vehicleType) {
        this.vehicleType = vehicleType;
    }

    public String getStoppedSessionUuid() {
        return stoppedSessionUuid;
    }

    public void setStoppedSessionUuid(String stoppedSessionUuid) {
        this.stoppedSessionUuid = stoppedSessionUuid;
    }

    //some details removed for brevity

    @Override
    public static class VehicleStoppedPK implements Serializable {

        private OffsetDateTime startTs;

        private String stoppedSessionUuid;

        public VehicleStoppedPK() {
        }

        public OffsetDateTime getStartTs() {
            return startTs;
        }

        public void setStartTs(OffsetDateTime startTs) {
            this.startTs = startTs;
        }

        public String getStoppedSessionUuid() {
            return stoppedSessionUuid;
        }

        public void setStoppedSessionUuid(String stoppedSessionUuid) {
            this.stoppedSessionUuid = stoppedSessionUuid;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            VehicleStoppedPK that = (VehicleStoppedPK) o;
            return Objects.equals(startTs, that.startTs) && Objects.equals(stoppedSessionUuid, that.stoppedSessionUuid);
        }

        @Override
        public int hashCode() {
            return Objects.hash(startTs, stoppedSessionUuid);
        }

        @Override
        public String toString() {
            return "VehicleStoppedPK{"  
                    "startTs="   startTs  
                    ", stoppedSessionUuid='"   stoppedSessionUuid   '\''  
                    '}';
        }
    }

}

@Entity
@Table(name = "camera_zone")
public class CameraZone implements Serializable {
    @Id
    @SequenceGenerator(name = "camera_zone_id_seq", sequenceName = "camera_zone_id_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "camera_zone_id_seq")
    @Column(name = "id", updatable=false)
    private Integer id;

    @Column(name = "uuid", unique = true)
    private String uuid;

    @Column(name = "type")
    private String type;

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

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUuid() {
        return uuid;
    }

    public void setUuid(String uuid) {
        this.uuid = uuid;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        CameraZone that = (CameraZone) o;
        return Objects.equals(id, that.id) && Objects.equals(uuid, that.uuid) && Objects.equals(camera, that.camera) && Objects.equals(type, that.type) && Objects.equals(name, that.name);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id, uuid, camera, type, name);
    }

}

The code that I have in my Repository implementation looks like this:

public class SpecificationVehicleStoppedRepositoryImpl
    implements SpecificationVehicleStoppedRepository {
  @Autowired private EntityManager em;

  @Autowired ProjectionFactory projectionFactory;

  @Override
  public List<VehicleStoppedAggregate> getStoppedVehiclesCount(Specification<VehicleStopped> spec) {
    CriteriaBuilder builder = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> query = builder.createTupleQuery();

    Root<VehicleStopped> root = query.from(VehicleStopped.class);
    Predicate predicate = spec.toPredicate(root, query, builder);

    if (predicate != null) {
      query.where(predicate);
    }

    Path<Number> duration = root.get("durationSeconds");
    Path<CameraZone> zone = root.get("cameraZone");

    query
        .multiselect(zone,
            builder.count(root).alias("totalVehicles"),
            builder.min(duration).alias("minDuration"),
            builder.avg(duration).alias("avgDuration"),
            builder.max(duration).alias("maxDuration"))
        .groupBy(zone);

    List<Tuple> rawResultList = em.createQuery(query).getResultList();
    return project(rawResultList, VehicleStoppedAggregate.class);
  }

  private <P> List<P> project(List<Tuple> results, Class<P> projectionClass) {

    return results.stream()
            .map(tuple -> {
              Map<String, Object> mappedResult = new HashMap<>(tuple.getElements().size());
              for (TupleElement<?> element : tuple.getElements()) {
                String name = element.getAlias();
                mappedResult.put(name, tuple.get(name));
              }
              return projectionFactory.createProjection(projectionClass, mappedResult);
            })
            .collect(Collectors.toList());
  }
}

The interface-based projection I am trying to populate (using SpelAwareProxyProjectionFactory) is this:

public interface VehicleStoppedAggregate {

    CameraZone getCameraZone();

    Integer getTotalVehicles();

    Double getMinDuration();

    Double getAvgDuration();

    Double getMaxDuration();
}

The call to getStoppedVehiclesCount() fails with the following error: ERROR: column "camerazone1_.id" must appear in the GROUP BY clause or be used in an aggregate function

This error is coming from the PostgreSQL database, and rightly so because the SQL hibernate generates is incorrect:

select
        vehiclesto0_.zone_id as col_0_0_,
        count(*) as col_1_0_,
        min(vehiclesto0_.duration_seconds) as col_2_0_,
        avg(vehiclesto0_.duration_seconds) as col_3_0_,
        max(vehiclesto0_.duration_seconds) as col_4_0_,
        camerazone1_.id as id1_2_,
        camerazone1_.name as name2_2_,
        camerazone1_.type as type3_2_,
        camerazone1_.uuid as uuid4_2_
    from
        vehicle_stopped vehiclesto0_ 
    inner join
        camera_zone camerazone1_ 
            on vehiclesto0_.zone_id=camerazone1_.id cross 
    where
        vehiclesto0_.start_ts>=? 
        and vehiclesto0_.start_ts<=? 
        and 1=1 
        and 1=1 
        and 1=1 
    group by
        vehiclesto0_.zone_id

It is not grouping by the other fields it is requesting from the joined table.

If I had to use a normal class, instead of a Tuple, it would work, but it would mean I would have to create a class with a huge constructor for all fields for Hibernate to populate it.

Somehow, when I use Interface-based projections with Spring's repositories rather than my criteriaquery, the same scenario works. They manage to populate the one-to-many relationships just fine.

Is there a way to fix this and make Hibernate ask for the right fields?

I am using Hibernate 5.6.14.Final (as bundled with Spring Boot 2.7.8).

CodePudding user response：

I believe the "solution" is two create two "independent" query roots and join them together:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createTupleQuery();

Root<VehicleStopped> root = query.from(VehicleStopped.class);

// instead of Path<CameraZone> zone = root.get("cameraZone")
Root<CameraZone> zone = query.from(CameraZone.class);
query.where(builder.equal(zone, root.get("cameraZone")));


Path<Number> duration = root.get("durationSeconds");

query
        .multiselect(zone,
                builder.count(root).alias("totalVehicles"),
                builder.min(duration).alias("minDuration"),
                builder.avg(duration).alias("avgDuration"),
                builder.max(duration).alias("maxDuration"))
        .groupBy(zone);

session.createQuery(query).getResultList();

In that case Hibernate 5 produces following SQL (which actually looks weird from my perspective due to missing columns in group by clause):

    select
        naturalidc1_.id as col_0_0_,
        count(*) as col_1_0_,
        min(naturalidc0_.duration_seconds) as col_2_0_,
        avg(naturalidc0_.duration_seconds) as col_3_0_,
        max(naturalidc0_.duration_seconds) as col_4_0_,
        naturalidc1_.id as id1_0_,
        naturalidc1_.name as name2_0_,
        naturalidc1_.type as type3_0_,
        naturalidc1_.uuid as uuid4_0_ 
    from
        vehicle_stopped naturalidc0_ cross 
    join
        camera_zone naturalidc1_ 
    where
        naturalidc1_.id=naturalidc0_.zone_id 
    group by
        naturalidc1_.id

FYI. Your initial query does work in Hibernate 6 and produced SQL does look more correct but still weird:

    select
        c1_0.id,
        c1_0.name,
        c1_0.type,
        c1_0.uuid,
        count(*),
        min(v1_0.duration_seconds),
        avg(v1_0.duration_seconds),
        max(v1_0.duration_seconds) 
    from
        vehicle_stopped v1_0 
    join
        camera_zone c1_0 
            on c1_0.id=v1_0.zone_id 
    group by
        1,
        2,
        3,
        4