How to use a @ConstructorResult with a Set field

Question

I'm trying to create a @NamedNativeQuery with a @ConstructorResult for a class that has a field with a Set of enum values.

VeterinarianJPA.java:

@Entity
@Table(name = "veterinarians")
@Setter
@Getter
@NoArgsConstructor
@NamedNativeQueries({
        @NamedNativeQuery(
                name = VeterinarianJPA.FIND_ALL_VETS,
                query = "SELECT v.id, v.name, vs.specialisations " +
                        "FROM veterinarians v " +
                        "JOIN veterinarian_specialisations vs ON v.id = vs.vet_id",
                resultSetMapping = VeterinarianJPA.VETERINARIAN_RESULT_MAPPER
        )})
@SqlResultSetMappings({
        @SqlResultSetMapping(
                name = VeterinarianJPA.VETERINARIAN_RESULT_MAPPER,
                classes = @ConstructorResult(
                        targetClass = Veterinarian.class,
                        columns = {
                                @ColumnResult(name = "id", type = Long.class),
                                @ColumnResult(name = "name"),
                                @ColumnResult(name = "specialisations", type = Set.class)
                        }
                )
        )})
class VeterinarianJPA {

    static final String FIND_ALL_VETS = "net.kemitix.naolo.gateway.data.jpa.findAllVets";
    static final String VETERINARIAN_RESULT_MAPPER = "net.kemitix.naolo.gateway.data.jpa.Veterinarian";

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @ElementCollection
    @Enumerated(EnumType.STRING)
    @CollectionTable(
            name = "veterinarian_specialisations",
            joinColumns = @JoinColumn(name = "vet_id")
    )
    private final Set<VetSpecialisation> specialisations = new HashSet<>();
}

Veterinarian.java:

public final class Veterinarian {

    private Long id;
    private String name;
    private Set<VetSpecialisation> specialisations;

    public Veterinarian() {
    }

    public Veterinarian(final long id,
                        final String name,
                        final Set<VetSpecialisation> specialisations) {
        this.id = id;
        this.name = name;
        this.specialisations = new HashSet<>(specialisations);
    }

    public long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public Set<VetSpecialisation> getSpecialisations() {
        return new HashSet<>(specialisations);
    }

}

VetSpecialisation.java:

public enum VetSpecialisation {

    RADIOLOGY,
    DENTISTRY,
    SURGERY

}

When I attempt to execute the named query:

entityManager.createNamedQuery(VeterinarianJPA.FIND_ALL_VETS, Veterinarian.class)
             .getResultStream()

I get the following exception:

java.lang.IllegalArgumentException: Could not locate appropriate constructor on class : net.kemitix.naolo.entities.Veterinarian

    at org.hibernate.loader.custom.ConstructorResultColumnProcessor.resolveConstructor(ConstructorResultColumnProcessor.java:92)
    at org.hibernate.loader.custom.ConstructorResultColumnProcessor.performDiscovery(ConstructorResultColumnProcessor.java:45)
    at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:494)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:2213)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2169)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892)
    at org.hibernate.loader.Loader.scroll(Loader.java:2765)
    at org.hibernate.loader.custom.CustomLoader.scroll(CustomLoader.java:383)
    at org.hibernate.internal.SessionImpl.scrollCustomQuery(SessionImpl.java:2198)
    at org.hibernate.internal.AbstractSharedSessionContract.scroll(AbstractSharedSessionContract.java:1058)
    at org.hibernate.query.internal.NativeQueryImpl.doScroll(NativeQueryImpl.java:217)
    at org.hibernate.query.internal.AbstractProducedQuery.scroll(AbstractProducedQuery.java:1462)
    at org.hibernate.query.internal.AbstractProducedQuery.stream(AbstractProducedQuery.java:1486)
    at org.hibernate.query.Query.getResultStream(Query.java:1110)

I expect that the SQL is returning multiple rows for a multi-valued Set rather than a single value, which is causing the constructor not to match. How do I change the SQL to produce the correct input to the constructor, or is there another configuration change I need to make?

Answer 1

Well, I'm not sure if that's even possible in the way you want to to this. But you can use LISTAGG function on specialisations table to inline the specialisations with veterinarians by using some kind of separator.

So the query should look like this:

SELECT v.id, v.name 
(SELECT LISTAGG(vs.type, ';') 
  WITHIN GROUP (ORDER BY vs.type) 
  FROM veterinarian_specialisations vs 
  WHERE vs.vet_id = v.id) specialisations
FROM veterinarians v;

The query will return veterinarian and his semicolon separated specialisations:

1   NAME   DENTISTRY;RADIOLOGY

And then in your Veterinarian class constructor you must remap String result back to Set of VetSpecialisation. I used Java 8 stream api just for convenience.

public final class Veterinarian {

private Long id;
private String name;
private Set<VetSpecialisation> specialisations;

public Veterinarian() {
}

public Veterinarian(final long id,
                    final String name,
                    final String specialisations) {
    this.id = id;
    this.name = name;
    this.specialisations = Arrays.asList(specialisations.split(";"))
        .stream()
        .map(VetSpecialisation::valueOf) //Map string to VetSpecialisation enum.
        .collect(Collectors.toSet());
}