java - Pivot-like result with JPA/QueryDSL -

we using jpa2, spring data , querydsl in our project. have following tables , 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 actitivy activityname - activity might have different names (e.g. running, jogging)

a person might have level given activity , can perfom several activities (each defined level).

• there should search activity names (e.g. running etc.) parameters (a list of activity names)
• as result persons should found perform related activity.
• the result should contains activities search corresponding level, person's name , overall sum of persons activities

example 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 persons "running" or "dancing" should result 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: there jpa ql / querydsl way such result one expression / projection? have multi-step solution - selecting activity names , levels, performing grouping , sum java8. if grouping querydsl, not single level entries. vice versa, in solution have perform several other steps.

would nice know if possible using query.

pure jpa & querydsl works entities. make db view aggregates data you're looking , map new entity, can query.

another solution use querydsl's native jpa query support. see http://www.querydsl.com/static/querydsl/3.6.1/reference/html/ch02.html bottom half. need lowest paragraph (query , project dto).

it boils down to:

• generate q classes pointing db (schema) (i'm unsure why that's needed, it's in docs)
• construct query using com.mysema.query.jpa.sql.jpasqlquery class
• make sure list necessary result field query in list method
• create dto bean class can project result
• project result dto class