Java 如何从 Spring Data JPA GROUP BY 查询返回自定义对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36328063/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return a custom object from a Spring Data JPA GROUP BY query
提问by Pranav C Balan
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.
我正在使用 Spring Data JPA 开发 Spring Boot 应用程序。我正在使用自定义 JPQL 查询按某个字段分组并获取计数。以下是我的存储库方法。
@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?
我怎样才能做到这一点？
采纳答案by manish
Solution for JPQL queries
JPQL查询解决方案
This is supported for JPQL queries within the JPA specification.
JPA 规范中的JPQL 查询支持此功能。
Step 1: Declare a simple bean class
第 1 步：声明一个简单的 bean 类
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
第 2 步：从存储库方法返回 bean 实例
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
重要笔记
- Make sure to provide the fully-qualified path to the bean class, including the package name. For example, if the bean class is called
MyBeanand it is in packagecom.path.to, the fully-qualified path to the bean will becom.path.to.MyBean. Simply providingMyBeanwill not work (unless the bean class is in the default package). - Make sure to call the bean class constructor using the
newkeyword.SELECT new com.path.to.MyBean(...)will work, whereasSELECT com.path.to.MyBean(...)will not. - 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.
- 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. Sincenewandcom.path.to.MyBeanare not valid SQL keywords, the RDBMS then throws an exception.
- 确保提供 bean 类的完全限定路径，包括包名称。例如，如果 bean 类被调用
MyBean并且它在 package 中com.path.to，则 bean 的完全限定路径将为com.path.to.MyBean. 简单地提供是MyBean行不通的（除非 bean 类在默认包中）。 - 确保使用
new关键字调用 bean 类构造函数。SELECT new com.path.to.MyBean(...)会起作用，而SELECT com.path.to.MyBean(...)不会。 - 确保以与 bean 构造函数中预期的完全相同的顺序传递属性。尝试以不同的顺序传递属性将导致异常。
- 确保查询是有效的 JPA 查询，即它不是本机查询。
@Query("SELECT ..."), or@Query(value = "SELECT ..."), or@Query(value = "SELECT ...", nativeQuery = false)会起作用，而@Query(value = "SELECT ...", nativeQuery = true)不会起作用。这是因为本机查询在没有修改的情况下传递给 JPA 提供程序，并且是针对底层 RDBMS 执行的。由于new和com.path.to.MyBean不是有效的 SQL 关键字，因此 RDBMS 会引发异常。
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 newkeyword since it is not part of the SQL standard.
如上所述，new ...语法是 JPA 支持的机制，适用于所有 JPA 提供程序。但是，如果查询本身不是 JPA 查询，即它是本机查询，则new ...语法将不起作用，因为查询直接传递到底层 RDBMS，它不理解new关键字，因为它不是JPA 查询的一部分SQL 标准。
In situations like these, bean classes need to be replaced with Spring Data Projectioninterfaces.
在这种情况下，需要用Spring Data Projection接口替换 bean 类。
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
第 2 步：从查询中返回投影属性
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 ASkeyword to map result fields to projection properties for unambiguous mapping.
使用 SQLAS关键字将结果字段映射到投影属性以进行明确映射。
回答by ozgur
This SQL query return List< Object[] > would.
此 SQL 查询将返回 List<Object[]>。
You can do it this way:
你可以这样做：
@RestController
@RequestMapping("/survey")
public class SurveyController {
@Autowired
private SurveyRepository surveyRepository;
@RequestMapping(value = "/find", method = RequestMethod.GET)
public Map<Long,String> findSurvey(){
List<Object[]> result = surveyRepository.findSurveyCount();
Map<Long,String> map = null;
if(result != null && !result.isEmpty()){
map = new HashMap<Long,String>();
for (Object[] object : result) {
map.put(((Long)object[0]),object[1]);
}
}
return map;
}
}
回答by TanvirChowdhury
define a custom pojo class say sureveyQueryAnalytics and store the query returned value in your custom pojo class
定义一个自定义 pojo 类，比如 sureveyQueryAnalytics 并将查询返回值存储在您的自定义 pojo 类中
@Query(value = "select new com.xxx.xxx.class.SureveyQueryAnalytics(s.answer, count(sv)) from Survey s group by s.answer")
List<SureveyQueryAnalytics> calculateSurveyCount();
回答by rena
I know this is an old question and it has already been answered, but here's another approach:
我知道这是一个老问题，已经有人回答了，但这是另一种方法：
@Query("select new map(count(v) as cnt, v.answer) from Survey v group by v.answer")
public List<?> findSurveyCount();
回答by dwe
I do not like java type names in query strings and handle it with a specific constructor. Spring JPA implicitly calls constructor with query result in HashMap parameter:
我不喜欢查询字符串中的 java 类型名称并使用特定的构造函数处理它。Spring JPA 使用 HashMap 参数中的查询结果隐式调用构造函数：
@Getter
public class SurveyAnswerStatistics {
public static final String PROP_ANSWER = "answer";
public static final String PROP_CNT = "cnt";
private String answer;
private Long cnt;
public SurveyAnswerStatistics(HashMap<String, Object> values) {
this.answer = (String) values.get(PROP_ANSWER);
this.count = (Long) values.get(PROP_CNT);
}
}
@Query("SELECT v.answer as "+PROP_ANSWER+", count(v) as "+PROP_CNT+" FROM Survey v GROUP BY v.answer")
List<SurveyAnswerStatistics> findSurveyCount();
Code needs Lombok for resolving @Getter
代码需要 Lombok 来解析 @Getter
回答by Nick Savenia
Using interfaces you can get simpler code. No need to create and manually call constructors
使用接口可以获得更简单的代码。无需创建和手动调用构造函数
Step 1: Declare intefrace with the required fields:
第 1 步：使用必填字段声明接口：
public interface SurveyAnswerStatistics {
String getAnswer();
Long getCnt();
}
Step 2: Select columns with same name as getter in interface and return intefrace from repository method:
第 2 步：在接口中选择与 getter 同名的列并从存储库方法返回接口：
public interface SurveyRepository extends CrudRepository<Survey, Long> {
@Query("select v.answer as answer, count(v) as cnt " +
"from Survey v " +
"group by v.answer")
List<SurveyAnswerStatistics> findSurveyCount();
}
回答by Yosra ADDALI
I just solved this problem :
我刚刚解决了这个问题：
- Class-based Projections doesn't work with query native(
@Query(value = "SELECT ...", nativeQuery = true)) so I recommend to define custom DTO using interface . - Before using DTO should verify the query syntatically correct or not
- 基于类的投影不适用于查询 native(
@Query(value = "SELECT ...", nativeQuery = true))，因此我建议使用 interface 定义自定义 DTO。 - 在使用 DTO 之前应该验证查询的语法是否正确
回答by adlerer
I used custom DTO (interface) to map a native query to - the most flexible approach and refactoring-safe.
我使用自定义 DTO（接口）将本机查询映射到 - 最灵活的方法和重构安全。
The problem I had with this - that surprisingly, the order of fields in the interface and the columns in the query matters. I got it working by ordering interface getters alphabetically and then ordering the columns in the query the same way.
我遇到的问题 - 令人惊讶的是，界面中的字段顺序和查询中的列很重要。我通过按字母顺序对接口 getter 进行排序，然后以相同的方式对查询中的列进行排序来使其工作。
回答by Senthuran
@Repository
public interface ExpenseRepo extends JpaRepository<Expense,Long> {
List<Expense> findByCategoryId(Long categoryId);
@Query(value = "select category.name,SUM(expense.amount) from expense JOIN category ON expense.category_id=category.id GROUP BY expense.category_id",nativeQuery = true)
List<?> getAmountByCategory();
}
The above code worked for me.
上面的代码对我有用。
