QueryDsl projection ElementCollection

Question

I'm trying to figure out how to do a DTO projection of an Entity with a list of Enums (@ElementCollection). Unfortunately, the QueryDsl Documentation is lacking and here I only find results for version 3 which are not applicable for version 4.

@Entity
public class User {
    private String username;

    @Enumerated(EnumType.STRING)
    @ElementCollection(targetClass = Permission.class)
    private Set<Permission> permissions;
}

And I'd like a DTO with a set/list/array of either Permission-Enums or simply Strings (will be converted to JSON anyway). A simple constructor expression doesn't work:

List<UserDto> users = new JPAQueryFactory(eM).select(
            Projections.constructor(UserDto.class,
                    QUser.user.username, QUser.user.permissions))
            .from(QUser.user)
            .fetch();

Gives me org.hibernate.QueryException: not an entity

All the examples with .transform() I've seen use a groupBy and return a Map. I'm generating these queries dynamically and I want a List of DTOs, not sometimes a List of DTO and sometimes a Map.

EDIT:

Something like that if I were to write a native PostgreSQL query:

select id, username, array_remove(array_agg(up.permissions), null) as permissions
from users u
left join users_permissions up on up.uid = u.id
group by u.id;

EDIT 2:

I guess this is what I'd have to do with JPQL? :puke:

List<UserDto> users = (List<UserDto>) eM.getEntityManager().createQuery(
                "SELECT u.id, u.username, u.tenantId, u.fullname, u.active, u.localeKey, perms " +
                        "FROM User u " +
                        "LEFT JOIN u.permissions perms")
                .unwrap(org.hibernate.query.Query.class)
                .setResultTransformer(
                        new ResultTransformer() {
                            private Map<Long, UserDto> res = new HashMap<>();

                            @Override
                            public Object transformTuple(Object[] tuple, String[] aliases) {
                                UserDto u = res.get(tuple[0]);
                                if (u == null) {
                                    u = new UserDto((Long) tuple[0], (String) tuple[1], "", (String) tuple[2], (String) tuple[3], (boolean) tuple[4], (String) tuple[5], EnumSet.of((Permission) tuple[6]));
                                    res.put(u.getId(), u);
                                } else {
                                    u.getPermissions().add((Permission) tuple[6]);
                                }

                                return null;
                            }

                            @Override
                            public List<UserDto> transformList(List tuples) {
                                return new ArrayList<>(res.values());
                            }
                        })
                .getResultList();

Answer 1

OK, I finally figured it out. In this case you actually have to use a transformer, which makes sense, as you want to aggregate several rows.

I've had to dig through QueryDsl's unit tests. The static imports actually make it tricky if you're not using an IDE, but read it on Github like me. I almost had the Solution, but I used Expressions.set(), instead of GroupBy.set():

EnumPath<Permission> perm = Expressions.enumPath(Permission.class, "perm");

List<UserDto> users = new JPAQueryFactory(eM.getEntityManager())
                .selectFrom(QUser.user)
                .leftJoin(QUser.user.permissions, perm)
                .transform(
                        groupBy(QUser.user.id)
                        .list(Projections.constructor(UserDto.class,
                                QUser.user.id, QUser.user.username, Expressions.stringTemplate("''"), QUser.user.tenantId,
                                QUser.user.fullname, QUser.user.active, QUser.user.localeKey, GroupBy.set(perm))));

And this is much nicer on the eye then the JPQL/Hibernate-ResultTransformer version.

Answer 2

Here is a full example of using .transform() method.

import com.querydsl.core.ResultTransformer;
import com.querydsl.core.Tuple;
import com.querydsl.core.group.GroupBy;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQuery;
import lombok.AccessLevel;
import lombok.RequiredArgsConstructor;
import lombok.experimental.FieldDefaults;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

@Repository
@FieldDefaults(level = AccessLevel.PRIVATE, makeFinal = true)
@RequiredArgsConstructor
public class MyDaoImpl implements MyDao {

  @PersistenceContext
  EntityManager entityManager;

  @Override
  public List<Dto> findAll(Pageable pageable, Predicate predicate, OrderSpecifier<?>[] sorting) {
    return buildQuery()
        .where(predicate)
        .limit(pageable.getPageSize())
        .offset(pageable.getOffset())
        .orderBy(sorting)
        .transform(buildDtoTransformer());
  }

  private JPAQuery<Tuple> buildQuery() {
    return new JPAQuery<>(entityManager)
        .from(QEntity.entity)
        .select(buildSelectExpressions());
  }

  private ResultTransformer<List<Dto>> buildDtoTransformer() {
    return GroupBy
        .groupBy(QEntity.entity.id)
        .list(Projections.constructor(Dto.class, buildSelectExpressions()));
  }

  private Expression<?>[] buildSelectExpressions() {
    return new Expression[] {
        QEntity.entity.id,
        QEntity.entity.mnemonic,
        QEntity.entity.label,
        QEntity.entity.lastUpdateDate,
        QEntity.entity.status,
        QEntity.entity.updatedBy,
    };
  }
}

Pros

• The same expression array for .select() and .transform() methods. It means is expressions can be in one place.

Cons

• No grouping. It means no nested collections.

• Expressions order and DTO constructor params order should be the same.

  new Expression[] {
     QEntity.entity.id,
     QEntity.entity.mnemonic,
     QEntity.entity.label,
     QEntity.entity.lastUpdateDate,
     QEntity.entity.status,
     QEntity.entity.updatedBy,
  };
  
  public class Dto {
  
     public Dto(Integer id,
               String mnemonic,
               String label,
               LocalDateTime lastUpdateDate,
               Boolean status,
               String updatedBy) {
      this.id = id;
      this.mnemonic = mnemonic;
      this.label = label;
      this.lastUpdateDate = lastUpdateDate;
      this.status = status;
      this.updatedBy = updatedBy;
    }
  }
  

• Creation nested objects logic should be in a DTO constructor.

Conclusion

You can use .transform() for single-level simple objects.

If you need extra actions (as a grouping to build hierarchical response structure), you should make your own mapper class. The mapper class will receive Collection<Tuple> and returns List<YourDto> or Set<YourDto>.