如何从Spring Data JPA GROUP BY查询返回自定义对象 [英] How to return a custom object from a Spring Data JPA GROUP BY query

问题描述

我正在使用Spring Data JPA开发Spring Boot应用程序。我正在使用自定义JPQL查询按某个字段进行分组并获取计数。以下是我的存储库方法。

I'm developing a Spring Boot application with Spring Data JPA. I'm using a custom JPQL query to group by some field and get the count. Following is my repository method.

@Query(value = "select count(v) as cnt, v.answer from Survey v group by v.answer")
public List<?> findSurveyCount();

它正常运行，结果如下：

It's working and result is obtained as follows:

[
  [1, "a1"],
  [2, "a2"]
]

我想得到这样的结果：

[
  { "cnt":1, "answer":"a1" },
  { "cnt":2, "answer":"a2" }
]

我如何实现这一目标？

推荐答案

JPQL查询的解决方案

这支持 JPA规范。

第1步：声明一个简单的bean类

Step 1: Declare a simple bean class

package com.path.to;

public class SurveyAnswerStatistics {
  private String answer;
  private Long   cnt;

  public SurveyAnswerStatistics(String answer, Long cnt) {
    this.answer = answer;
    this.count  = cnt;
  }
}

Step 2 ：从存储库方法返回bean实例

Step 2: Return bean instances from the repository method

public interface SurveyRepository extends CrudRepository<Survey, Long> {
    @Query("SELECT " +
           "    new com.path.to.SurveyAnswerStatistics(v.answer, COUNT(v)) " +
           "FROM " +
           "    Survey v " +
           "GROUP BY " +
           "    v.answer")
    List<SurveyAnswerStatistics> findSurveyCount();
}

重要提示

Important notes

1. 确保提供bean类的完全限定路径，包括包名。例如，如果bean类被称为 MyBean ，并且它在包 com.path.to 中，则完全 - bean的限定路径是 com.path.to.MyBean 。简单地提供 MyBean 将不起作用（除非bean类在默认包中）。


2. 确保调用bean类构造函数使用 new 关键字。 SELECT new com.path.to.MyBean（...）将起作用，而 SELECT com.path.to.MyBean（...） 不会。


3. 确保以与bean构造函数中预期的顺序完全相同的顺序传递属性。尝试以不同的顺序传递属性将导致异常。


4. 确保查询是有效的JPA查询，即它不是本机查询。 @Query（SELECT ...），或 @Query（value =SELECT ...） ，或 @Query（value =SELECT ...，nativeQuery = false）将起作用，而 @Query（value =SELECT .. 。，nativeQuery = true）将无效。这是因为本机查询在没有修改的情况下传递给JPA提供程序，因此会针对底层RDBMS执行。由于 new 和 com.path.to.MyBean 不是有效的SQL关键字，因此RDBMS会抛出异常。

1. Make sure to provide the fully-qualified path to the bean class, including the package name. For example, if the bean class is called MyBean and it is in package com.path.to, the fully-qualified path to the bean will be com.path.to.MyBean. Simply providing MyBean will not work (unless the bean class is in the default package).
2. Make sure to call the bean class constructor using the new keyword. SELECT new com.path.to.MyBean(...) will work, whereas SELECT com.path.to.MyBean(...) will not.
3. Make sure to pass attributes in exactly the same order as that expected in the bean constructor. Attempting to pass attributes in a different order will lead to an exception.
4. Make sure the query is a valid JPA query, that is, it is not a native query. @Query("SELECT ..."), or @Query(value = "SELECT ..."), or @Query(value = "SELECT ...", nativeQuery = false) will work, whereas @Query(value = "SELECT ...", nativeQuery = true) will not work. This is because native queries are passed without modifications to the JPA provider, and are executed against the underlying RDBMS as such. Since new and com.path.to.MyBean are not valid SQL keywords, the RDBMS then throws an exception.

---

原生查询的解决方案

如上所述， new ... 语法是JPA支持的机制，适用于所有JPA提供程序。但是，如果查询本身不是JPA查询，也就是说，它是本机查询，则 new ... 语法将无效，因为查询直接传递到底层的RDBMS，它不理解 new 关键字，因为它不是SQL标准的一部分。

---

Solution for native queries

As noted above, the new ... syntax is a JPA-supported mechanism and works with all JPA providers. However, if the query itself is not a JPA query, that is, it is a native query, the new ... syntax will not work as the query is passed on directly to the underlying RDBMS, which does not understand the new keyword since it is not part of the SQL standard.

在这种情况下，需要用替换bean类。 Spring Data Projection 界面。

In situations like these, bean classes need to be replaced with Spring Data Projection interfaces.

第1步：声明投影界面

package com.path.to;

public interface SurveyAnswerStatistics {
  String getAnswer();

  int getCnt();
}

第2步 ：从查询返回投影属性

Step 2: Return projected properties from the query

public interface SurveyRepository extends CrudRepository<Survey, Long> {
    @Query(nativeQuery = true, value =
           "SELECT " +
           "    v.answer AS answer, COUNT(v) AS cnt " +
           "FROM " +
           "    Survey v " +
           "GROUP BY " +
           "    v.answer")
    List<SurveyAnswerStatistics> findSurveyCount();
}

使用SQL AS 关键字将结果字段映射到投影属性以进行明确映射。

Use the SQL AS keyword to map result fields to projection properties for unambiguous mapping.

这篇关于如何从Spring Data JPA GROUP BY查询返回自定义对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！