如何从 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 查询按某个字段分组并获取计数.以下是我的存储库方法.

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

它的工作和结果如下:

<预><代码>[[1, "a1"],[2, "a2"]]

我想得到这样的东西:

<预><代码>[{ "cnt":1, "answer":"a1" },{ "cnt":2, "answer":"a2" }]

我怎样才能做到这一点?

解决方案

JPQL 查询的解决方案

JPA 规范中的 JPQL 查询支持此功能.

<块引用>

第一步:声明一个简单的bean类

package com.path.to;公共类 SurveyAnswerStatistics {私人字符串答案；私人长cnt;公共SurveyAnswerStatistics(字符串答案，长cnt){this.answer = 答案;this.count = cnt;}}

<块引用>

步骤 2:从存储库方法返回 bean 实例

公共接口 SurveyRepository 扩展了 CrudRepository{@Query("选择" +" 新的 com.path.to.SurveyAnswerStatistics(v.answer, COUNT(v)) " +从" +" 调查 v " +分组依据" +" v.answer")列表<SurveyAnswerStatistics>findSurveyCount();}

重要说明

1. 确保提供 bean 类的完全限定路径，包括包名称.例如，如果 bean 类名为 MyBean 并且它在包 com.path.to 中，则 bean 的完全限定路径将是 com.path.to.MyBean.简单地提供 MyBean 是行不通的(除非 bean 类在默认包中).
2. 确保使用 new 关键字调用 bean 类构造函数.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 会抛出异常.

<小时>

原生查询的解决方案

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

在这种情况下，bean 类需要替换为 Spring 数据投影 接口.

<块引用>

第一步:声明一个投影接口

package com.path.to;公共接口 SurveyAnswerStatistics {字符串 getAnswer();int getCnt();}

<块引用>

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

公共接口 SurveyRepository 扩展了 CrudRepository{@Query(nativeQuery = true, value =选择" +" v.answer AS answer, COUNT(v) AS cnt " +从" +" 调查 v " +分组依据" +" v.answer")列表<SurveyAnswerStatistics>findSurveyCount();}

使用 SQL AS 关键字将结果字段映射到投影属性，以便明确映射.

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"]
]

I would like to get something like this:

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

How can I achieve this?

解决方案

Solution for JPQL queries

This is supported for JPQL queries within the JPA specification.

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: 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. 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.

---

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.

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

Step 1: Declare a projection interface

package com.path.to;

public interface SurveyAnswerStatistics {
  String getAnswer();

  int getCnt();
}

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

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

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