Load child collection DTOs in JPA DTO projection query

I’m using Java EE 7 with Java 8 and Hibernate (5.0.X) on Wildfly 10.1.0-Final, and I need to load a a JPQL query result into DTOs using projections, but I can’t find any documentation on how to load the child collection DTOs as well.

For instance, if I have following entities for User, Role, and Privilege:

@Entity
public class User
{
    @Id
    private long id;

    private String userName;
    private String firstName;
    private String lastName;

    private JobTitle jobTitle;
    private Email email;

    private boolean isRemote;

    @ManyToMany
    private Set<Tag> tags;

    @ManyToMany
    // @JoinColumn definitions...
    private Set<Role> roles;

    // getters/setters...
}

@Entity
public class Role
{
    @Id
    private long id;

    private String name;
    private String description;

    @ManyToMany
    // @JoinColumn definitions...
    private Set<Privilege> privileges;

    // getters/setters...
}

@Entity
public class Privilege
{
    @Id
    private long id;

    private String key;
    private String name;
    private String description;

    // getters/setters...
}

And I want to use projections to load some query results into the following immutable DTOs (assume all have hashCode and equals implemented based on id):

public class UserDTO
{
    private final long id;
    private final String userName;
    private final Set<RoleDTO> roles = new HashSet<>();

    public UserDTO(long id, String userName, Collection<RoleDTO> roles) // not sure if this is correct for projection..
    {
        this.id = id;
        this.userName = userName;
        this.roles.addAll(roles);
    }

    public Set<Role> getRoles()
    {
         return Collections.unmodifiableSet(roles);
    }

    // getters
}

public class RoleDTO
{
    private final long id;
    private final String name;
    private final Set<PrivilegeDTO> privileges = new HashSet<>();

    public RoleDTO(long id, String name, Set<PrivilegeDTO> privileges)
    {
        this.id = id;
        this.name = name;
        this.privileges.addAll(privileges);
    }

    public Set<Privilege> getPrivileges()
    {
         return Collections.unmodifiableSet(privileges);
     }
    // other getters
}

public class PrivilegeDTO
{
    private final long id;
    private final String key;

    public PrivilegeDTO(long id, String key)
    {
        this.id = id;
        this.key = key;
    }
    // getters
}

What would the structure of a JPQL query look like to achieve this? I’m pretty sure I could get the job done by doing the joins and then processing the results into the DTO objects afterwards, something like this (to load the 50 newest users by ID):

List<Object[]> results = em.createQuery("SELECT u.id, u.userName, r.id, "
    + "r.name, p.id, p.key FROM User u "
    + "LEFT JOIN u.roles r "
    + "LEFT JOIN r.privileges p "
    + "ORDER BY u.id DESC")
    .setMaxResults(50).getResultList();
Map<Long, UserDTO> users = new HashMap<>();
Map<Long, RoleDTO> roles = new HashMap<>();
Map<Long, PrivilegeDTO> privileges = new HashMap<>();

for(Object[] objArray : results)
{
  // process these into the DTO objects,
}

The reconstruction would have to happen starting with PrivilegeDTO objects, then RoleDTO, finally UserDTO. This will allow for immutability because you need the PrivilegeDTO objects when you build the RoleDTO objects, or you would have to add them later, meaning RoleDTO is not immutable.

It’d be a fun exercise in Streams, but I’d much prefer to be able to just have this built from the query, it seems like it would have to be faster. Is that even possible?

Thanks a lot!

Answer

Hi Morgan short answer is no, you can’t built from the query because you cant map JPQL to DTO Collections fields. Here it’s a question related with that JPQL: Receiving a Collection in a Constructor Expression

Anyway you could try an approach with Spring projections using spel https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query.spel-expressions

But i think the right solution is just use manual mapping like is explained in this answer https://stackoverflow.com/a/45934668/3449039