Load child collection DTOs in JPA DTO projection query

Question

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!

Leisha

what do you want at the end of day???? You have the query what else you need here? – Sectorial

Answer 1

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://mcmap.net/q/355535/-mapping-jpa-or-hibernate-projection-query-to-dto-data-transfer-object

Gymkhana

Answer 2

That's not possible out of the box like lucsbelt answered already, but I think this is a perfect use case for Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(User.class)
public interface UserDTO {
    @IdMapping
    Long getId();
    String getUserName();
    Set<RoleDto> getRoles();

    @EntityView(Role.class)
    interface RoleDto {
        @IdMapping
        Long getId();
        String getName();
        // To avoid the cartesian product, use a fetch strategy other than JOIN
        @Mapping(fetch = FetchStrategy.MULTISET)
        Set<PrivilegeDto> getPrivileges();
    }

    @EntityView(Privilege.class)
    interface PrivilegeDto {
        @IdMapping
        Long getId();
        String getKey();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

UserDTO a = entityViewManager.find(entityManager, UserDTO.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<UserDTO> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

Lashkar