Java 如何使用 SqlResultSetMapping 将 JPA NativeQuery 的结果集映射到 POJO

How to map the result set of a JPA NativeQuery to a POJO using SqlResultSetMapping

提问by mhlandry

I am attempting to map the results of a Native query to a POJO using @SqlResultSetMapping with @ConstructorResult. Here is my code:

我正在尝试使用 @SqlResultSetMapping 和 @ConstructorResult 将本机查询的结果映射到 POJO。这是我的代码：

@SqlResultSetMapping(name="foo",
    classes = {
        @ConstructorResult(
                targetClass = Bar.class,
                columns = {
                    @ColumnResult(name = "barId", type = Long.class),
                    @ColumnResult(name = "barName", type = String.class),
                    @ColumnResult(name = "barTotal", type = Long.class)
                })
    })

public class Bar {

private Long barId;
private String barName;
private Long barTotal;

...

And then in my DAO:

然后在我的 DAO 中：

Query query = em.createNativeQueryBar(QUERY, "foo");
... set some parameters ...
List<Bar> list = (List<Bar>) query.getResultList();

I have read that this functionality is only supported in JPA 2.1, but that is what I am using. Here's my dependency:

我已经读到此功能仅在 JPA 2.1 中受支持，但这就是我正在使用的。这是我的依赖：

        <dependency>
            <groupId>org.hibernate.javax.persistence</groupId>
            <artifactId>hibernate-jpa-2.1-api</artifactId>
            <version>1.0.0.Final</version>
        </dependency>

I found a couple of resources, including this one: @ConstructorResult mapping in jpa 2.1. But I am still not having any luck.

我找到了一些资源，包括这个：@ConstructorResult mapping in jpa 2.1。但我仍然没有任何运气。

What am I missing? Why can't the SqlResultSetMapping be found?

我错过了什么？为什么找不到SqlResultSetMapping？

javax.persistence.PersistenceException: org.hibernate.MappingException: Unknown SqlResultSetMapping [foo]

采纳答案by zbig

@SqlResultSetMappingannotation should not be put on a POJO. Put it at (any) @Entityclass. "Unknown SqlResultSetMapping [foo]" tells you, that JPA provider doesn't see any mapping under name 'foo'. Please see another answer of mine for the correct example

@SqlResultSetMapping注释不应放在 POJO 上。把它放在（任何）@Entity班级。“Unknown SqlResultSetMapping [foo]”告诉您，JPA 提供程序在名称“foo”下看不到任何映射。请参阅我的另一个答案以获取正确示例

• JPA- Joining two tables in non-entity class

• JPA-连接非实体类中的两个表

回答by Emerson Moretto

I able to do it this way:

我可以这样做：

Session session = em().unwrap(Session.class);
SQLQuery q = session.createSQLQuery("YOUR SQL HERE");
q.setResultTransformer( Transformers.aliasToBean( MyNotMappedPojoClassHere.class) );
List<MyNotMappedPojoClassHere> postList = q.list();

回答by kinjelom

Short working example:

简短的工作示例：

• DTO POJO class

  @lombok.Getter
  @lombok.AllArgsConstructor
  public class StatementDto {
      private String authorName;
      private Date createTime;
  }
  

• Repository bean:

  @Repository
  public class StatementNativeRepository {      
      @PersistenceContext private EntityManager em;
  
      static final String STATEMENT_SQLMAP = "Statement-SQL-Mapping";
  
      public List<StatementDto> findPipelinedStatements() {
          Query query = em.createNativeQuery(
              "select author_name, create_time from TABLE(SomePipelinedFun('xxx'))",
              STATEMENT_SQLMAP);
          return query.getResultList();
      }
  
      @SqlResultSetMapping(name= STATEMENT_SQLMAP, classes = {
          @ConstructorResult(targetClass = StatementDto.class,
              columns = {
                  @ColumnResult(name="author_name",type = String.class),
                  @ColumnResult(name="create_time",type = Date.class)
              }
          )
      }) @Entity class SQLMappingCfgEntity{@Id int id;} // <- workaround
  
  }
  

• DTO POJO 类

  @lombok.Getter
  @lombok.AllArgsConstructor
  public class StatementDto {
      private String authorName;
      private Date createTime;
  }
  

• 存储库bean：

  @Repository
  public class StatementNativeRepository {      
      @PersistenceContext private EntityManager em;
  
      static final String STATEMENT_SQLMAP = "Statement-SQL-Mapping";
  
      public List<StatementDto> findPipelinedStatements() {
          Query query = em.createNativeQuery(
              "select author_name, create_time from TABLE(SomePipelinedFun('xxx'))",
              STATEMENT_SQLMAP);
          return query.getResultList();
      }
  
      @SqlResultSetMapping(name= STATEMENT_SQLMAP, classes = {
          @ConstructorResult(targetClass = StatementDto.class,
              columns = {
                  @ColumnResult(name="author_name",type = String.class),
                  @ColumnResult(name="create_time",type = Date.class)
              }
          )
      }) @Entity class SQLMappingCfgEntity{@Id int id;} // <- workaround
  
  }
  

回答by benito

@Entity
@SqlResultSetMapping(name="ConnexionQueryBean",
   entities={
         @EntityResult(entityClass=com.collecteJ.business.bean.ConnexionQueryBean.class, fields={ 
        @FieldResult(name="utilisateurId", column="UTILISATEUR_ID"),
        @FieldResult(name="nom", column="NOM"),
        @FieldResult(name="prenom", column="PRENOM"),
        @FieldResult(name="nomConnexion", column="NOM_CONNEXION"),
        @FieldResult(name="codeAgence", column="CODE_AGENCE"),
        @FieldResult(name="codeBanque", column="CODE_BANQUE"),
        @FieldResult(name="codeDevise", column="CODE_DEVISE"),
        @FieldResult(name="codeCollecteur", column="CODE_COLLECTEUR")})
   })
public class ConnexionQueryBean implements Serializable {

@Id
private long utilisateurId;
private String codeCollecteur;
private String nom;
private String prenom;
private String nomConnexion; 
private String codeAgence;
private String codeBanque;
private String codeDevise;


public ConnexionQueryBean() {
}


public long getUtilisateurId() {
    return utilisateurId;
}

public void setUtilisateurId(long utilisateurId) {
    this.utilisateurId = utilisateurId;
}

public String getCodeCollecteur() {
    return codeCollecteur;
}

public void setCodeCollecteur(String codeCollecteur) {
    this.codeCollecteur = codeCollecteur;
}


public String getNom() {
    return nom;
}

public void setNom(String nom) {
    this.nom = nom;
}

public String getPrenom() {
    return prenom;
}

public void setPrenom(String prenom) {
    this.prenom = prenom;
}

public String getNomConnexion() {
    return nomConnexion;
}

public void setNomConnexion(String nomConnexion) {
    this.nomConnexion = nomConnexion;
}

public String getCodeAgence() {
    return codeAgence;
}

public void setCodeAgence(String codeAgence) {
    this.codeAgence = codeAgence;
}

public String getCodeBanque() {
    return codeBanque;
}

public void setCodeBanque(String codeBanque) {
    this.codeBanque = codeBanque;
}

public String getCodeDevise() {
    return codeDevise;
}

public void setCodeDevise(String codeDevise) {
    this.codeDevise = codeDevise;
}

@Override
public String toString() {
    return "ConnexionQueryBean{" + "utilisateurId=" + utilisateurId + ", codeCollecteur=" + codeCollecteur + ", nom=" + nom + ", prenom=" + prenom + ", nomConnexion=" + nomConnexion + ", codeAgence=" + codeAgence + ", codeBanque=" + codeBanque + ", codeDevise=" + codeDevise + '}';
}

This is not really an entity as it does not match any database table. But the @Entityand the @Idannotations are compulsories for JPA to understand the mapping. If you don't really want to have @Entity / @Idin that class, you can remove the @SqlResultSetMappingannotation and put it in any other entity as far as JPA can scan it.

这不是真正的实体，因为它不匹配任何数据库表。但是@Entity和@Id注释是 JPA 理解映射所必需的。如果你真的不想@Entity / @Id在那个类中，你可以删除 @SqlResultSetMapping注释并将它放在任何其他实体中，只要 JPA 可以扫描它。

You should also make sure that your @ComponentScancontent the corresponding package, if you are using a java based spring configuration, you should explicitly declare your entity in the persistence.xml/orm.xmlunder the META-INFdirectory.

你还应该确保你的@ComponentScan内容对应的包，如果你使用的是基于java的spring配置，你应该persistence.xml/orm.xml在META-INF目录下显式声明你的实体。

This is the call

这是电话

String connexionQuery = "SELECT u.UTILISATEUR_ID, u.NOM, u.PRENOM, u.NOM_CONNEXION, a.CODE_AGENCE, a.CODE_BANQUE, a.CODE_DEVISE, c.CODE_COLLECTEUR FROM UTILISATEUR u, AGENCE a, COLLECTEUR c "
            + " WHERE (a.CODE_AGENCE = c.CODE_AGENCE AND u.UTILISATEUR_ID = c.UTILISATEUR_ID AND u.NOM_CONNEXION = '"+nomConnextion+"')";

    ConnexionQueryBean ConnexionResults = (ConnexionQueryBean) defaultService.getEntityManager().createNativeQuery(connexionQuery,"ConnexionQueryBean").getSingleResult();
    System.out.println(ConnexionResults.toString());

I'm using Spring, JPA 2.1, Hibernate 5 and Oracle, i think this might not be possible with JPA lower version, find more http://www.thoughts-on-java.org/result-set-mapping-complex-mappings/

我正在使用 Spring、JPA 2.1、Hibernate 5 和 Oracle，我认为 JPA 较低版本可能无法实现，找到更多http://www.thoughts-on-java.org/result-set-mapping-complex-映射/

回答by Simon Martinelli

QLRM could be a alternative: http://simasch.github.io/qlrm/

QLRM 可能是另一种选择：http://simasch.github.io/qlrm/

It is not related to a specific JPA implementation and also works with JDBC.

它与特定的 JPA 实现无关，也适用于 JDBC。

回答by Artanis Zeratul

I have a slightly varied answer which is just derived from wildloop's answer.
Here is my answer:

我有一个稍微不同的答案，它只是从 Wildloop 的答案中得出的。
这是我的回答：

Constants class: Constants.java

常量类：Constants.java

public class Constants {
    public final String TESTQUERYRESULT_MAPPING_NAME = "TestQueryResultMapping";
}

Result Mapping Class: TestQueryResult.java

结果映射类：TestQueryResult.java

import lombok.Getter;
import lombok.Setter;

import javax.persistence.Entity;
import javax.persistence.EntityResult;
import javax.persistence.FieldResult;
import javax.persistence.Id;
import javax.persistence.SqlResultSetMapping;

@Getter
@Setter
@SqlResultSetMapping(
    //name = "TestQueryResultMapping"
    name = Constants.TESTQUERYRESULT_MAPPING_NAME
    ,entities = @EntityResult(
        entityClass = TestQueryResult.class
        ,fields = {
            @FieldResult(name = "rowId", column = "row_id")
            ,@FieldResult(name = "rowName", column = "row_name")
            ,@FieldResult(name = "value", column = "row_value")
        }
    )
)
@Entity
public class TestQueryResult {
    @Id
    private Integer rowId;

    private String rowName;

    private String value;

}

Then... somewhere in my Repository Implementation code:

然后......在我的存储库实现代码中的某处：

public class TestQueryRepository {
    //... some code here to get the entity manager here

    public TestQueryResult getTopMost(Integer rowName) {
        //... some code here

        String queryString = "... some query string here" + rowName;
        TestQueryResult testQueryResult = null;

        //this.entityManager.createNativeQuery(queryString ,"TestQueryResultMapping").getResultList();
        List<TestQueryResult> results = this.entityManager.createNativeQuery(queryString ,Constants.TESTQUERYRESULT_MAPPING_NAME).getResultList();
        if (results != null && !results.isEmpty()) {
            testQueryResult = results.get(0);
        }

        return testQueryResult;
    }

}

... then violah! I got some results :D!

……那么薇奥拉！我得到了一些结果：D！

Cheers,
Artanis Zeratul

干杯，
阿塔尼斯·泽拉图

回答by Vlad Palnik

The problem with adding @Entityto your DTO POJO is that it will create a table in your db that you don't need. Having to add @Idand a transient keyword to necessary fields is also a hassle. A simple solution is to move your @SqlResultSetMappingto an abstract class.

添加@Entity到您的 DTO POJO的问题在于它会在您的数据库中创建一个您不需要的表。必须在@Id必要的字段中添加一个临时关键字也很麻烦。一个简单的解决方案是将您移动@SqlResultSetMapping到抽象类。

@MappedSuperclass
@SqlResultSetMapping(name="foo",
    classes = {
        @ConstructorResult(
                targetClass = Bar.class,
                columns = {
                    @ColumnResult(name = "barId", type = Long.class),
                    @ColumnResult(name = "barName", type = String.class),
                    @ColumnResult(name = "barTotal", type = Long.class)
                })
    })

public abstract class sqlMappingCode {}

Don't forget to add @MappedSuperclass. This will ensure Hibernate auto-wires your mapping.

不要忘记添加@MappedSuperclass. 这将确保 Hibernate 自动连接您的映射。

回答by dinesh salve

This solution is independent of JPA implementation. Once you collect result of native query as

此解决方案独立于 JPA 实现。一旦您将本机查询的结果收集为

List<Object[]> = em.createNativeQueryBar(QUERY, "foo").getResultList();

and if you need to map each List element to a Person e.g.

如果您需要将每个 List 元素映射到一个 Person 例如

Class Person { String name; Int age; }

where List element[0] is name and element [ 1] is age.

其中列表元素[0] 是姓名，元素[1] 是年龄。

You can convert Object[] to Person using ObjectMapper. You need to add @JsonFormat annotation on the class.

您可以使用ObjectMapper将 Object[] 转换为 Person 。您需要在类上添加 @JsonFormat 注释。

@JsonFormat(shape = JsonFormat.Shape.ARRAY) 
Class Person { String name; Int age; }

and convert Object[] to Person as

并将 Object[] 转换为 Person 作为

ObjectMapper mapper = new ObjectMapper();
JSONArray jsonArray = new JSONArray(Arrays.asList(attributes)).toString();
Person person = mapper.readValue(jsonArray, Person.class);

回答by Vlad Mihalcea

This is a very common question, so this answer is based on this articleI wrote on my blog.

这是一个很常见的问题，所以这个答案是基于我在博客上写的这篇文章。

Domain Model

领域模型

Let's consider we have the following postand post_commenttables in our database:

让我们考虑一下我们的数据库中有以下内容post和post_comment表格：

JPA SqlResultSetMapping

日本特许经营协会 SqlResultSetMapping

The SqlResultSetMappingJPA annotation looks as follows:

在SqlResultSetMappingJPA注解如下所示：

@Repeatable(SqlResultSetMappings.class)
@Target({TYPE}) 
@Retention(RUNTIME)
public @interface SqlResultSetMapping { 

    String name(); 

    EntityResult[] entities() default {};

    ConstructorResult[] classes() default {};

    ColumnResult[] columns() default {};
}

The SqlResultSetMappingannotation is repeatable and is applied at the entity class level. Apart from taking a unique name, which is used by Hibernate to register the mapping, there are three mapping options:

该SqlResultSetMapping注释是可重复的，并在实体类一级应用。除了采用 Hibernate 用来注册映射的唯一名称之外，还有三种映射选项：

• EntityResult
• ConstructorResult
• ColumnResult

• EntityResult
• ConstructorResult
• ColumnResult

Next, we are going to see how al these three mapping options work, as well as the use cases where you will need to use them.

接下来，我们将了解这三个映射选项的工作原理，以及您需要使用它们的用例。

JPA SqlResultSetMapping - EntityResult

JPA SqlResultSetMapping - EntityResult

The EntityResultoption allows you to map the JDBC ResultSetcolumns to one or more JPA entities.

该EntityResult选项允许您将 JDBCResultSet列映射到一个或多个 JPA 实体。

Let's assume we want to fetch the first 5 Postentities along with all their associated PostCommententities that match a given titlepattern.

假设我们要获取前 5 个Post实体以及它们与PostComment给定title模式匹配的所有关联实体。

As I explained in this article, we can use the DENSE_RANKSQL Window Functionto know how to filter the postand post_commentjoined records, as illustrated by the following SQL query:

正如我在本文中解释的，我们可以使用DENSE_RANKSQL 窗口函数来了解如何过滤post和post_comment连接的记录，如下面的 SQL 查询所示：

SELECT *
FROM (
  SELECT
    *,
    DENSE_RANK() OVER (
    ORDER BY
      "p.created_on",
      "p.id"
    ) rank
  FROM (
    SELECT
      p.id AS "p.id", p.created_on AS "p.created_on",
      p.title AS "p.title", pc.post_id AS "pc.post_id",
      pc.id as "pc.id", pc.created_on AS "pc.created_on",
      pc.review AS "pc.review"
    FROM post p
    LEFT JOIN post_comment pc ON p.id = pc.post_id
    WHERE p.title LIKE :titlePattern
    ORDER BY p.created_on
  ) p_pc
) p_pc_r
WHERE p_pc_r.rank <= :rank

However, we don't want to return a list of scalar column values. We want to return JPA entities from this query, so we need to configure the entitiesattribute of the @SqlResultSetMappingannotation, like this:

但是，我们不想返回标量列值的列表。我们想从这个查询中返回 JPA 实体，所以我们需要配置注解的entities属性@SqlResultSetMapping，像这样：

@NamedNativeQuery(
    name = "PostWithCommentByRank",
    query = """
        SELECT *
        FROM (
          SELECT
            *,
            DENSE_RANK() OVER (
            ORDER BY
              "p.created_on",
              "p.id"
            ) rank
          FROM (
            SELECT
              p.id AS "p.id", p.created_on AS "p.created_on",
              p.title AS "p.title", pc.post_id AS "pc.post_id",
              pc.id as "pc.id", pc.created_on AS "pc.created_on",
              pc.review AS "pc.review"
            FROM post p
            LEFT JOIN post_comment pc ON p.id = pc.post_id
            WHERE p.title LIKE :titlePattern
            ORDER BY p.created_on
          ) p_pc
        ) p_pc_r
        WHERE p_pc_r.rank <= :rank
        """,
    resultSetMapping = "PostWithCommentByRankMapping"
)
@SqlResultSetMapping(
    name = "PostWithCommentByRankMapping",
    entities = {
        @EntityResult(
            entityClass = Post.class,
            fields = {
                @FieldResult(name = "id", column = "p.id"),
                @FieldResult(name = "createdOn", column = "p.created_on"),
                @FieldResult(name = "title", column = "p.title"),
            }
        ),
        @EntityResult(
            entityClass = PostComment.class,
            fields = {
                @FieldResult(name = "id", column = "pc.id"),
                @FieldResult(name = "createdOn", column = "pc.created_on"),
                @FieldResult(name = "review", column = "pc.review"),
                @FieldResult(name = "post", column = "pc.post_id"),
            }
        )
    }
)

With the SqlResultSetMappingin place, we can fetch the Postand PostCommententities like this:

有了SqlResultSetMapping就位，我们可以像这样获取Post和PostComment实体：

List<Object[]> postAndCommentList = entityManager
    .createNamedQuery("PostWithCommentByRank")
    .setParameter("titlePattern", "High-Performance Java Persistence %")
    .setParameter("rank", POST_RESULT_COUNT)
    .getResultList();

And, we can validate that the entities are properly fetched:

并且，我们可以验证实体是否被正确获取：

assertEquals(
    POST_RESULT_COUNT * COMMENT_COUNT, 
    postAndCommentList.size()
);

for (int i = 0; i < COMMENT_COUNT; i++) {
    Post post = (Post) postAndCommentList.get(i)[0];
    PostComment comment = (PostComment) postAndCommentList.get(i)[1];

    assertTrue(entityManager.contains(post));
    assertTrue(entityManager.contains(comment));

    assertEquals(
        "High-Performance Java Persistence - Chapter 1",
        post.getTitle()
    );

    assertEquals(
        String.format(
            "Comment nr. %d - A must read!",
            i + 1
        ),
        comment.getReview()
    );
}

The @EntityResultis also useful when fetching JPA entities via SQL stored procedures. Check out this articlefor more details.

这@EntityResult在通过 SQL 存储过程获取 JPA 实体时也很有用。查看这篇文章了解更多详情。

JPA SqlResultSetMapping - ConstructorResult

JPA SqlResultSetMapping - ConstructorResult

Let's assume we want to execute an aggregation query that counts the number of post_comentrecords for each postand returns the posttitlefor reporting purposes. We can use the following SQL query to achieve this goal:

假设我们要执行一个聚合查询，该查询计算post_coment每个记录的数量post并返回posttitle用于报告目的。我们可以使用以下 SQL 查询来实现这一目标：

SELECT
  p.id AS "p.id",
  p.title AS "p.title",
  COUNT(pc.*) AS "comment_count"
FROM post_comment pc
LEFT JOIN post p ON p.id = pc.post_id
GROUP BY p.id, p.title
ORDER BY p.id

We also want to encapsulate the post title and the comment count in the following DTO:

我们还想将帖子标题和评论数封装在以下 DTO 中：

public class PostTitleWithCommentCount {

    private final String postTitle;

    private final int commentCount;

    public PostTitleWithCommentCount(
            String postTitle,
            int commentCount) {
        this.postTitle = postTitle;
        this.commentCount = commentCount;
    }

    public String getPostTitle() {
        return postTitle;
    }

    public int getCommentCount() {
        return commentCount;
    }
}

To map the result set of the above SQL query to the PostTitleWithCommentCountDTO, we can use the classesattribute of the @SqlResultSetMappingannotation, like this:

要将上述 SQL 查询的结果集映射到PostTitleWithCommentCountDTO，我们可以使用注解的classes属性@SqlResultSetMapping，如下所示：

@NamedNativeQuery(
    name = "PostTitleWithCommentCount",
    query = """
        SELECT
          p.id AS "p.id",
          p.title AS "p.title",
          COUNT(pc.*) AS "comment_count"
        FROM post_comment pc
        LEFT JOIN post p ON p.id = pc.post_id
        GROUP BY p.id, p.title
        ORDER BY p.id
        """,
    resultSetMapping = "PostTitleWithCommentCountMapping"
)
@SqlResultSetMapping(
    name = "PostTitleWithCommentCountMapping",
    classes = {
        @ConstructorResult(
            columns = {
                @ColumnResult(name = "p.title"),
                @ColumnResult(name = "comment_count", type = int.class)
            },
            targetClass = PostTitleWithCommentCount.class
        )
    }
)

The ConstructorResultannotation allows us to instruct Hibernate what DTO class to use as well as which constructor to be called when instantiating the DTO objects.

该ConstructorResult注解允许我们指示休眠什么DTO类使用以及它的构造函数实例化对象DTO时调用。

Note that we used the typeattribute of the @ColumnResultannotation to specify that the comment_countshould be cast to a Java int. This is needed since some JDBC drivers use either Longor BigIntegerfor the SQL aggregation function results.

请注意，我们使用注释的type属性@ColumnResult来指定comment_count应该被转换为 Java int。这是必需的，因为某些 JDBC 驱动程序使用Long或BigInteger用于 SQL 聚合函数结果。

This is how you can call the PostTitleWithCommentCountnamed native query using JPA:

这是PostTitleWithCommentCount使用 JPA调用命名本机查询的方法：

List<PostTitleWithCommentCount> postTitleAndCommentCountList = entityManager
    .createNamedQuery("PostTitleWithCommentCount")
    .setMaxResults(POST_RESULT_COUNT)
    .getResultList();

And, we can see that the returned PostTitleWithCommentCountDTOs have been fetched properly:

而且，我们可以看到返回的PostTitleWithCommentCountDTO 已被正确获取：

assertEquals(POST_RESULT_COUNT, postTitleAndCommentCountList.size());

assertEquals(POST_RESULT_COUNT, postTitleAndCommentCountList.size());

for (int i = 0; i < POST_RESULT_COUNT; i++) {
    PostTitleWithCommentCount postTitleWithCommentCount = 
        postTitleAndCommentCountList.get(i);

    assertEquals(
        String.format(
            "High-Performance Java Persistence - Chapter %d",
            i + 1
        ),
        postTitleWithCommentCount.getPostTitle()
    );

    assertEquals(COMMENT_COUNT, postTitleWithCommentCount.getCommentCount());
}

For more details about the best way to fetch DTO projections with JPA and Hibernate, check out this article.

有关使用 JPA 和 Hibernate 获取 DTO 投影的最佳方法的更多详细信息，请查看这篇文章。

JPA SqlResultSetMapping - ColumnResult

JPA SqlResultSetMapping - ColumnResult

The previous example showed how we could map the SQL aggregation result set to a DTO. But, what if we want to return the JPA entity for which we are counting the comments?

前面的示例展示了我们如何将 SQL 聚合结果集映射到 DTO。但是，如果我们想返回正在计算评论的 JPA 实体呢？

To achieve this goal we can use the entitiesattribute to define the Postentity we are fetching, and the classesattribute of the @SqlResultSetMappingannotation to map the scalar value, which in our case is the number of associated post_commentrecords:

为了实现这个目标，我们可以使用entities属性来定义Post我们正在获取的实体，并classes使用@SqlResultSetMapping注释的属性来映射标量值，在我们的例子中是关联post_comment记录的数量：

@NamedNativeQuery(
    name = "PostWithCommentCount",
    query = """
        SELECT
          p.id AS "p.id",
          p.title AS "p.title",
          p.created_on AS "p.created_on",
          COUNT(pc.*) AS "comment_count"
        FROM post_comment pc
        LEFT JOIN post p ON p.id = pc.post_id
        GROUP BY p.id, p.title
        ORDER BY p.id
        """,
    resultSetMapping = "PostWithCommentCountMapping"
)
@SqlResultSetMapping(
    name = "PostWithCommentCountMapping",
    entities = @EntityResult(
        entityClass = Post.class,
        fields = {
            @FieldResult(name = "id", column = "p.id"),
            @FieldResult(name = "createdOn", column = "p.created_on"),
            @FieldResult(name = "title", column = "p.title"),
        }
    ),
    columns = @ColumnResult(
        name = "comment_count",
        type = int.class
    )
)

When executing the PostWithCommentCountnamed native query:

执行PostWithCommentCount命名本机查询时：

List<Object[]> postWithCommentCountList = entityManager
    .createNamedQuery("PostWithCommentCount")
    .setMaxResults(POST_RESULT_COUNT)
    .getResultList();

we will get both the Postentity and the commentCountscalar column value:

我们将同时获得Post实体和commentCount标量列值：

assertEquals(POST_RESULT_COUNT, postWithCommentCountList.size());

for (int i = 0; i < POST_RESULT_COUNT; i++) {
    Post post = (Post) postWithCommentCountList.get(i)[0];
    int commentCount = (int) postWithCommentCountList.get(i)[1];

    assertTrue(entityManager.contains(post));

    assertEquals(i + 1, post.getId().intValue());
    assertEquals(
        String.format(
            "High-Performance Java Persistence - Chapter %d",
            i + 1
        ),
        post.getTitle()
    );

    assertEquals(COMMENT_COUNT, commentCount);
}