Java 将 JPA 或 Hibernate 投影查询映射到 DTO（数据传输对象）

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

提问by Thai Tran

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

在我的 DAO 层中，我有一个这样的 Find 函数

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)

所以，为了读取数据，我必须使用循环（与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<?> resultin to a list of DTOobject (say, DQCategoryDTO) without using any loop, iterator and calling setter/getter to fill the value?

我的问题是：是否有任何 api、框架可以轻松地将List<?> resultin转换为DTO对象列表（例如 DQCategoryDTO）而不使用任何循环、迭代器和调用 setter/getter 来填充值？

采纳答案by Shailendra

You can use ResultTransformerwhich can convert from alias to bean (DTO) properties. For usage you can refer to the Hibernate docs hereat section 13.1.5

您可以使用ResultTransformer，它可以将别名转换为 bean (DTO) 属性。有关用法，您可以参考此处的 Hibernate 文档，第 13.1.5 节

回答by Sumit Sundriyal

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.

someDTOs 列表将包含按 streetName 列出的结果组数。每个 SomeDTO 对象都包含街道名称和该街道的公寓数量。

SomeDTO.java

一些DTO.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;
}
}

回答by Vlad Mihalcea

As I explained in this article, you have so many options for mapping your projection to a DTO result set:

正如我在本文中所解释的，您有很多选项可以将您的投影映射到 DTO 结果集：

DTO projections using Tuple and JPQL

使用元组和 JPQL 的 DTO 投影

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

使用构造函数表达式和 JPQL 的 DTO 投影

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

For more details, check out this article.

您还可以从 JPA 构造函数表达式中省略 DTO 包名称，并通过其简单的 Java 类名称（例如，PostDTO）引用 DTO 。

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

使用元组和本机 SQL 查询的 DTO 投影

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

这个可以从 Hibernate 5.2.11 获得，所以还有一个升级的理由。

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

使用 ConstructorResult 的 DTO 投影

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

如果我们使用PostDTO前面介绍的相同类类型，我们必须提供以下内容@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:

现在，名为 native query 的 SQL 投影执行如下：

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

使用 ResultTransformer 和 JPQL 的 DTO 投影

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

这一次，您的 DTO 需要具有您需要 Hibernate 从底层 JDBC 填充的属性的设置器ResultSet。

The DTO projection looks as follows:

DTO 投影如下所示：

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

使用 ResultTransformer 和 Native SQL 查询的 DTO 投影

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

回答by Christian Beikov

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

这正是创建Blaze-Persistence 实体视图的用例！

Your DTO looks like

你的 DTO 看起来像

@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

如果您使用 Spring Data，则可以在类似的存储库中使用它

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