Returning list of strings in JPA @Query using projection

Question

I have an entity Dog and then an entity of Event that the dogs participate in. I want to extract a list(string) of each Event that has a corresponding DogId. I am using JPA projection because I need a subset of the entity with the addition of the eventId.

public class DogSubset {

private String dogId;
private String dogName;
private Date birthDate;
private List<String> eventId

}

@Query("SELECT new project.entity.DogSubset(d.dogId, d.dogName, d.birthDate, e.eventId) FROM Dog d JOIN Event e on d.dogId = e.dogId WHERE d.dogId = :dogId")
public List<DogSubset> getDogSubSetsByDogId(String dogId);

Next, based on another answer here is my attempt to join on e.EventId and extract as a collection, but it does not work.

@Query("SELECT new project.entity.DogSubset(d.dogId, d.dogName, d.birthDate, x) FROM Dog d JOIN Event e on d.dogId = e.dogId JOIN FETCH e.eventId x WHERE d.dogId = :dogId")
public List<DogSubset> getDogSubSetsByDogId(String dogId);

After more research I am not sure if what I am trying to do is possible, is there a way to return the List of Strings without a separate query?

Answer 1

If you are trying to get DogSubset instances built with List eventId within it - no, you cannot. JPA and its query languages do not support aggregating into lists. Options you have are to take the dogId,dogName,birthDate,String eventId, put them in a dogEvent (or just deal with a tuple or Object[] result) and aggregate them yourself into DogSubset instances.

Alternatively, you could create a DogSubset view 'entity' and map it to the Dog table with the Event.eventId mapped as an @ElementCollection. For example:

@Entity
@Table(name = "dog_table")
public class DogSubset {
  @Id
  private String dogId;
  private String dogName;
  private Date birthDate;
  @ElementCollection 
  @CollectionTable(name = "event_table", joinColumns = @JoinColumn(name = "dogId"))
  private List<String> eventId
}

Just make sure you never try creating new instances or updating them, as this will mess up your Dog and Event entity data.