Mapping JPA or Hibernate projection query to DTO (Data Transfer Object)

Question

In my DAO layer, I have a Find function like this

public List<?> findCategoryWithSentenceNumber(int offset, int maxRec) {
  Criteria crit = getSession().createCriteria(Category.class, "cate");
    crit.createAlias("cate.sentences", "sent");

    crit.setProjection(Projections.projectionList().
    add(Projections.property("title"), "title").
    add(Projections.count("sent.id"), "numberOfSentence").
    add(Projections.groupProperty("title"))
  );

  crit.setFirstResult(offset);
  crit.setMaxResults(maxRec);

  return crit.list();
}

So, in order to read the data, I have to use a Loop (with Iterator)

List<?> result = categoryDAO.findCategoryWithSentenceNumber(0, 10);
// List<DQCategoryDTO> dtoList = new ArrayList<>(); 

for (Iterator<?> it = result.iterator(); it.hasNext(); ) {
  Object[] myResult = (Object[]) it.next();

  String  title = (String) myResult[0];
  Long count = (Long) myResult[1];


  assertEquals("test", title); 
  assertEquals(1, count.intValue()); 

  // dQCategoryDTO = new DQCategoryDTO();
  // dQCategoryDTO.setTitle(title);
  // dQCategoryDTO.setNumberOfSentence(count);
  // dtoList.add(dQCategoryDTO);

}

My question is: is there any api, framework to easily convert the List<?> result in to a list of DTO object (say, DQCategoryDTO) without using any loop, iterator and calling setter/getter to fill the value?

Answer 1

You have so many options for mapping your projection to a DTO result set:

DTO projections using Tuple and JPQL

List<Tuple> postDTOs = entityManager.createQuery("""
    select
        p.id as id,
        p.title as title
    from Post p
    where p.createdOn > :fromTimestamp
    """, Tuple.class)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of(2016, 1, 1, 0, 0, 0)
        .toInstant(ZoneOffset.UTC )))
.getResultList();

assertFalse(postDTOs.isEmpty());
 
Tuple postDTO = postDTOs.get(0);
assertEquals( 
    1L, 
    postDTO.get("id") 
);

DTO projections using a Constructor Expression and JPQL

List<PostDTO> postDTOs = entityManager.createQuery("""
    select new com.vladmihalcea.book.hpjp.hibernate.query.dto.projection.jpa.PostDTO(
        p.id,
        p.title
    )
    from Post p
    where p.createdOn > :fromTimestamp
    """, PostDTO.class)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

You can also omit the DTO package name from the JPA constructor expression, and reference the DTO by its simple Java class name (e.g., PostDTO).

List<PostDTO> postDTOs = entityManager.createQuery("""
    select new PostDTO(
        p.id,
        p.title
    )
    from Post p
    where p.createdOn > :fromTimestamp

      """, PostDTO.class)

.setParameter( "fromTimestamp", Timestamp.from(
  LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
      .toInstant( ZoneOffset.UTC ) ))
.getResultList();

DTO projections using Tuple and native SQL queries

This one is available from Hibernate 5.2.11 so yet one more reason to upgrade.

List<Tuple> postDTOs = entityManager.createNativeQuery("""
    SELECT
           p.id AS id,
           p.title AS title
    FROM Post p
    WHERE p.created_on > :fromTimestamp
    """, Tuple.class)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

DTO projections using a ConstructorResult

If we use the same PostDTO class type introduced previously, we have to provide the following @SqlResultSetMapping:

@NamedNativeQuery(
    name = "PostDTO",
    query = """
        SELECT
               p.id AS id,
               p.title AS title
        FROM Post p
        WHERE p.created_on > :fromTimestamp
        """,
    resultSetMapping = "PostDTO"
)
@SqlResultSetMapping(
    name = "PostDTO",
    classes = @ConstructorResult(
        targetClass = PostDTO.class,
        columns = {
            @ColumnResult(name = "id"),
            @ColumnResult(name = "title")
        }
    )
)

Now, the SQL projection named native query is executed as follows:

List<PostDTO> postDTOs = entityManager.createNamedQuery("PostDTO")
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 )
        .toInstant( ZoneOffset.UTC ) ))
.getResultList();

DTO projections using ResultTransformer and JPQL

This time, your DTO requires to have the setters for the properties you need Hibernate to populate from the underlying JDBC ResultSet.

The DTO projection looks as follows:

List<PostDTO> postDTOs = entityManager.createQuery("""
    select
           p.id as id,
           p.title as title
    from Post p
    where p.createdOn > :fromTimestamp
    """)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
.unwrap( org.hibernate.query.Query.class )
.setResultTransformer( Transformers.aliasToBean( PostDTO.class ) )
.getResultList();

DTO projections using ResultTransformer and a Native SQL query

List postDTOs = entityManager.createNativeQuery("""
    select
           p.id as \"id\",
           p.title as \"title\"
    from Post p
    where p.created_on > :fromTimestamp
    """)
.setParameter( "fromTimestamp", Timestamp.from(
    LocalDateTime.of( 2016, 1, 1, 0, 0, 0 ).toInstant( ZoneOffset.UTC ) ))
.unwrap( org.hibernate.query.NativeQuery.class )
.setResultTransformer( Transformers.aliasToBean( PostDTO.class ) )
.getResultList();

Answer 2

That's exactly the use case for which Blaze-Persistence Entity Views has been created for!

Your DTO looks like

@EntityView(Category.class)
interface DQCategoryDTO  {
  String getTitle();
  @Mapping("SIZE(sentences)")
  int getCount();
}

and if you use Spring Data, you can use it in a repository like

interface CategoryRepository extends Repository<Category, Long> {
  List<DQCategoryDTO> findAll(Pageable pageable);
}

Answer 3

Following is the complete example of how addresses are group together based on street name using Projection.

Criteria criteria = getCurrentSession().createCriteria(Address.class);
// adding condition
criteria.add(Restrictions.eq("zip", "12345"));
// adding projection
criteria.setProjection(Projections.projectionList()
.add(Projections.groupProperty("streetName"), "streetName")
.add(Projections.count("apartment"), "count"));
// set transformer
criteria.setResultTransformer(new AliasToBeanResultTransformer(SomeDTO.class));

List<SomeDTO> someDTOs = criteria.list();

someDTOs list will contain number of result group by streetName. Each SomeDTO object contain street name and number of apartment in that street.

SomeDTO.java

public class SomeDTO{

private String streetName;
private Long count;

public void setStreetName(String streetName){
    this.streetName=streetName;
}
public String getStreetName(){
    return this.streetName;
}
public Long getCount() {
    return count;
}
public void setCount(Long count) {
    this.count = count;
}
}

Answer 4

You can use ResultTransformer which can convert from alias to bean (DTO) properties. For usage you can refer to the Hibernate docs here at section 13.1.5