JPA (or Hibernate) way to project over a related entity collection in criteria

Question

So here's some sample entities:

@Entity
@Table(name = "assessment")
public class Assessment implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    @NotNull
    @Column(name = "created_date", nullable = false, updatable = false)
    private ZonedDateTime createdDate;

    @NotNull
    @Column(name = "score")
    private Float score;

    @ManyToOne
    private BusinessUnit businessUnit;

    @NotNull
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "assessment_context_type",
        joinColumns = @JoinColumn(name = "assessment_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "context_type_id", referencedColumnName = "id"))
    @JsonIgnore
    private Set<ContextType> contextTypes = new HashSet<>();

...
}

@Entity
@Table(name = "context_type")
public class ContextType implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

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

What I'm trying to do is to get a slimmed down version of the assessment

public class SlimAssessment {
    public final Long id;
    public final ZonedDateTime createdDate;
    public final Float score;
    public final Long buId;
    public final Set<Long> contextTypeIds;

    public SlimAssessment(Long id, ZonedDateTime createdDate, Float score, Long buId, Set<Long> contextTypeIds) {
        this.id = id;
        this.createdDate = createdDate;
        this.score = score;
        this.buId = buId;
        this.contextTypeIds = contextTypeIds;
    }
}

More stuff omitted, of course, but I'm trying to do this via the JPA Criteria API

CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<SlimAssessment> query = builder.createQuery(SlimAssessment.class);
        Root<Assessment> ra = query.from(Assessment.class);
    
        Join<RiskAssessment, BusinessUnit> buJoin = ra.join(Assessment_.BUSINESS_UNIT);
        SetJoin<Assessment, ContextType> contextTypeJoin = ra.join(Assessment_.contextTypes);
        query.multiselect(
            ra.get(Assessment_.ID),
            ra.get(Assessment_.SCORE),
            buJoin.get(BusinessUnit_.ID),
            contextTypeJoin.get(ContextType_.ID)
        ).orderBy(builder.asc(ra.get(Assessment_.CREATED_DATE)));

But the problem is it's saying it can't find the constructor because it's thinking the contextTypeIds is just a single Long. How can I get the query to project over the collection and get the IDs? If I were doing this in straight SQL I could just join to the join table to get the associated IDs and I'd like to limit the number of joins to make this query as fast as possible (for example, the business unit ID is stored on the assessment table, so I shouldn't need to join there, so maybe I should switch that?)

Any help appreciated

Answer 1

As I outlined in this answer, Hibernate or Spring Data Projection only support flat results. I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Assessment.class)
public interface SlimAssessment {
    @IdMapping
    Long getId();
    ZonedDateTime getCreatedDate();
    Float getScore();
    String getName();
    @Mapping("businessUnit.id")
    Long getBuId();
    // You can also map just the ids if you like
    // @Mapping("contextTypes.id")
    // Set<Long> getContextTypeIds();
    Set<ContextTypeIdView> getContextTypes();

    @EntityView(ContextType.class)
    interface ContextTypeIdView {
        @IdMapping
        Long getId();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

SlimAssessment a = entityViewManager.find(entityManager, SlimAssessment.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<SlimAssessment> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!