Pivot-like result with JPA/QueryDSL

Question

We are using JPA2, Spring Data and QueryDSL in our project. I have the following tables and related JPA entities:

table Person (id, ...) 

table Activity (id, type, ...)

@Entity
@Configurable
public class Activity {
   @ElementCollection
   @CollectionTable(joinColumns = @JoinColumn(name = "ACTIVITY_ID"))
   @NotEmpty
   @Valid
   private Set<ActivityName> names = new HashSet<>();

table ActivityName(activity_id, name, ...) 

@Embeddable
  @Immutable
  @Table(uniqueConstraints = @UniqueConstraint(columnNames = "NAME"))
  public static class ActivityName { ... }

table ActivityLevel(person_id, activity_id, level) 

@Entity
@Immutable
@Validated
public final class ActivityLevel{...}

1..n for Actitivy to ActivityName - an activity might have different names (e.g. running, jogging)

A person might have a certain level for a given activity and can perfom several activities (each with a defined level).

• There should be a search with activity names (e.g. running etc.) as parameters (a list of activity names)
• As a result all persons should be found who perform the related activity.
• The result should contains all activities search for with their corresponding level, the person's name and the overall sum of the persons activities

Example the following data:

• Person = (id=1, name=Bob)
• Person = (id=2, name=Mary)
• Activity = (1, ...)
• Activity = (2, ...)
• ActivityName = (activity_id=1, name ="jogging")
• ActivityName = (activity_id=1, name = "running")
• ActivityName = (activity_id=2, name = "dancing")
• ActivityLevel = (person_id=1, activity_id=1, level=0.7f)
• ActivityLevel = (person_id=1, activity_id=2, level=0.1f)
• ActivityLevel = (person_id=2, activity_id=1, level=0.5f)

Searching for persons who are "running" or "dancing" should get a result like this:

Person[Name]   ActitiyName  ActivityLevel ActitiyName  ActivityLevel  Sum
Bob             running         0.7         dancing         0.1       0.8
Mary            running         0.5                                   0.5          

My Question: Is there a JPA QL / QueryDSL way to get such a result with one expression / projection? What I already have is a multi-step solution - selecting activity names and levels, performing the grouping and sum with Java8. If I do the grouping with querydsl, I do not get the single level entries. Vice versa, in my solution I have to perform several other steps.

Would be nice to know if this is possible just by using a query.

Answer 1

Pure JPA & QueryDsl works only with entities. So you could make a db view that aggregates the data you're looking for and map it to a new entity, which you can simply query.

Another solution is to use QueryDsl's native jpa query support. See http://www.querydsl.com/static/querydsl/3.6.1/reference/html/ch02.html bottom half. You would need the lowest paragraph (Query and project into DTO).

It boils down to:

• Generate Q classes by pointing to the db (schema) (i'm unsure why that's needed, but it's in the docs)
• Construct a query using the com.mysema.query.jpa.sql.JPASQLQuery class
• Make sure to list all necessary result field from the query in the list method
• Create a dto bean class to which you can project the result
• Project the result to the dto class