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?
Well, I'm not sure if that's even possible in the way you want to to this. But you can use
LISTAGG
function onspecialisations
table to inline thespecialisations
withveterinarians
by using some kind of separator.So the query should look like this:
The query will return veterinarian and his semicolon separated specialisations:
And then in your
Veterinarian
class constructor you must remap String result back to Set ofVetSpecialisation
. I used Java 8 stream api just for convenience.