Customizing the Result of JPA Queries with Aggregation Functions – 用聚合函数定制JPA查询的结果

最后修改: 2019年 10月 30日

中文/混合/英文(键盘快捷键：t)

1. Overview

1.概述

While Spring Data JPA can abstract the creation of queries to retrieve entities from the database in specific situations, we sometimes need to customize our queries, such as when we add aggregation functions.

虽然Spring Data JPA可以抽象地创建查询，以便在特定情况下从数据库中检索实体，但我们有时需要定制我们的查询，例如当我们添加聚合函数时。

In this tutorial, we’ll focus on how to convert the results of those queries into an object. We’ll explore two different solutions, one involving the JPA specification and a POJO, and another using Spring Data Projection.

在本教程中，我们将重点讨论如何将这些查询的结果转换为一个对象。我们将探索两种不同的解决方案，一种涉及JPA规范和POJO，另一种使用Spring数据投影。

2. JPA Queries and the Aggregation Problem

2.JPA查询和聚合问题

JPA queries typically produce their results as instances of a mapped entity. However, queries with aggregation functions normally return the result as Object[].

JPA查询通常以映射实体的实例形式产生其结果。然而，带有聚合函数的查询通常将结果作为Object[]返回。

To understand the problem, let’s define a domain model based on the relationship between posts and comments:

为了理解这个问题，让我们根据帖子和评论之间的关系来定义一个领域模型：。

@Entity
public class Post {
    @Id
    private Integer id;
    private String title;
    private String content;
    @OneToMany(mappedBy = "post")
    private List comments;

    // additional properties
    // standard constructors, getters, and setters
}

@Entity
public class Comment {
    @Id
    private Integer id;
    private Integer year;
    private boolean approved;
    private String content;
    @ManyToOne
    private Post post;

    // additional properties
    // standard constructors, getters, and setters
}

Our model defines that a post can have many comments, and each comment belongs to one post. Let’s use a Spring Data Repository with this model:

我们的模型定义了一个帖子可以有很多评论，而每个评论都属于一个帖子。让我们用一个Spring数据存储库与这个模型。

@Repository
public interface CommentRepository extends JpaRepository<Comment, Integer> {
    // query methods
}

Now let’s count the comments grouped by year:

现在我们来统计一下按年份分组的评论。

@Query("SELECT c.year, COUNT(c.year) FROM Comment AS c GROUP BY c.year ORDER BY c.year DESC")
List<Object[]> countTotalCommentsByYear();

The result of the previous JPA query can’t be loaded into an instance of Comment because the result is a different shape. The year and COUNT specified in the query don’t match our entity object.

之前JPA查询的结果不能被加载到Comment的实例中，因为结果是不同的形状。查询中指定的year和COUNT与我们的实体对象不匹配。

While we can still access the results in the general-purpose Object[] returned in the list, doing so will result in messy, error-prone code.

虽然我们仍然可以访问列表中返回的通用Object[]中的结果，但这样做将导致混乱的、容易出错的代码。

3. Customizing the Result With Class Constructors

3.用类构造函数定制结果

The JPA specification allows us to customize results in an object-oriented fashion. Therefore, we can use a JPQL constructor expression to set the result:

JPA规范允许我们以面向对象的方式定制结果。因此，我们可以使用JPQL构造函数表达式来设置结果。

@Query("SELECT new com.baeldung.aggregation.model.custom.CommentCount(c.year, COUNT(c.year)) "
  + "FROM Comment AS c GROUP BY c.year ORDER BY c.year DESC")
List<CommentCount> countTotalCommentsByYearClass();

This binds the output of the SELECT statement to a POJO. The class specified needs to have a constructor that matches the projected attributes exactly, but it’s not required to be annotated with @Entity.

这就把SELECT语句的输出绑定到一个POJO上。指定的类需要有一个与预测属性完全匹配的构造函数，但不需要用@Entity来注释。

We can also see that the constructor declared in the JPQL must have a fully qualified name:

我们还可以看到，在JPQL中声明的构造函数必须有一个完全合格的名称。

package com.baeldung.aggregation.model.custom;

public class CommentCount {
    private Integer year;
    private Long total;

    public CommentCount(Integer year, Long total) {
        this.year = year;
        this.total = total;
    }
    // getters and setters
}

4. Customizing the Result With Spring Data Projection

4.用Spring数据投影定制结果

Another possible solution is to customize the result of JPA queries with Spring Data Projection. This functionality allows us to project query results with considerably less code.

另一个可能的解决方案是使用Spring Data Projection定制JPA查询的结果。该功能允许我们用相当少的代码来预测查询结果。

4.1. Customizing the Result of JPA Queries

4.1.定制JPA查询的结果

To use interface-based projection, we must define a Java interface composed of getter methods that match the projected attribute names. Let’s define an interface for our query result:

为了使用基于接口的投影，我们必须定义一个由与投影属性名称相匹配的getter方法组成的Java接口。让我们为我们的查询结果定义一个接口。

public interface ICommentCount {
    Integer getYearComment();
    Long getTotalComment();
}

Now let’s express our query with the result returned as List<ICommentCount>:

现在让我们用返回的结果来表达我们的查询List<ICommentCount>。

@Query("SELECT c.year AS yearComment, COUNT(c.year) AS totalComment "
  + "FROM Comment AS c GROUP BY c.year ORDER BY c.year DESC")
List<ICommentCount> countTotalCommentsByYearInterface();

To allow Spring to bind the projected values to our interface, we need to give aliases to each projected attribute with the property name found in the interface.

为了让Spring将预测值绑定到我们的接口上，我们需要用接口中发现的属性名称给每个预测属性添加别名。

Spring Data will then construct the result on-the-fly, and return a proxy instance for each row of the result.

然后，Spring Data将即时构建结果，并为结果的每一行返回一个代理实例。

4.2. Customizing the Result of Native Queries

4.2.定制本地查询的结果

We can face situations where JPA queries aren’t as fast as native SQL, or can’t use specific features of our database engine. To solve this, we’ll use native queries.

我们可能会面临这样的情况：JPA查询不如本地SQL快，或者不能使用我们数据库引擎的特定功能。为了解决这个问题，我们将使用本地查询。

One advantage of interface-based projection is that we can use it for native queries. Let’s use ICommentCount again and bind it to a SQL query:

基于接口的投影的一个优点是我们可以将其用于本地查询。让我们再次使用ICommentCount并将其绑定到一个SQL查询。

@Query(value = "SELECT c.year AS yearComment, COUNT(c.*) AS totalComment "
  + "FROM comment AS c GROUP BY c.year ORDER BY c.year DESC", nativeQuery = true)
List<ICommentCount> countTotalCommentsByYearNative();

This works identically to JPQL queries.

这与JPQL查询的工作原理相同。

5. Conclusion

5.总结

In this article, we evaluated two different solutions to address mapping the results of JPA Queries with aggregation functions. First, we used the JPA standard involving a POJO class.

在本文中，我们评估了两种不同的解决方案，以解决带有聚合函数的JPA查询结果的映射问题。首先，我们使用涉及POJO类的JPA标准。

For the second solution, we used lightweight Spring Data projections with an interface. Spring Data projections allow us to write less code, both in Java and JPQL.

对于第二个解决方案，我们使用了带有接口的轻量级Spring Data投影。Spring Data投影让我们在Java和JPGL中都能写更少的代码。

As always, the example code for this article is available over on GitHub.

像往常一样，本文的示例代码可在GitHub上获得。