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?

Naturally

You can use resultTransformer which can convert from alias to bean (DTO) properties. Take look at #19629259 – Laminated

@Shailendra: it works. Thank. Please make your comment as the answer :) – Naturally

glad it helped ! I have added this as an answer ! – Laminated

Answer 1

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

Laminated

Answer 2

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();

Uninspired

how can I use DTO projections using a ConstructorResult for Composite Object ? – Phene

thanks for the article, I'm following the same, but my DTO is complex, lets say PostDTO has list of PostCommentDTO as a field, In this case how can I Map result to constructor ? – Phene

thanks for the example, but recursion is not my case, I'm finding it as hard to understand your query... (⌣̩̩́_⌣̩̩̀) – Phene

You can ignore the query and focus on the ResultTransformer which builds a hierarchy of DTOs. – Uninspired

sorry to bother you sir, but I'm stuck while returning result of ResultTransformer, for eg. I have 6 Posts, and total 12 PostComments , I'm getting result as 12 Posts, with associated PostComment – Phene

Now working solved with the help of, transformList method – Phene

sir how to deal with returning single Post with all its PostComments ? – Phene

setResultTransformer does not exists on the last example, neither the NativeQuery.class – Juncaceous

Yes it does. It's inherited from org.hibernate.query.NativeQuery base class. – Uninspired

would it be possible to create NamedNativeQuery and SqlResultSetMapping without any Entity or is required? – Courier

The @Target annotation says TYPE, so you need to add it to an Entity or MappedSuperclass. – Uninspired

How can I use "DTO projections using a Constructor Expressionand and JPQL" when I have field-collection. For instance Set<Title> titles and relation One-to-Many between Post and Titles – Obannon

Answer 3

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);
}

Irvin

Answer 4

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;
}
}

Desiccator