如何通过查询从Spring数据JPA组返回自定义对象

Question

我正在用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();

实际运行结果如下：

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

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

Answer 1

JPA规范中的JPQL查询支持这一点。

步骤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;
  }
}

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

null

如上所述，new...语法是一种支持JPA的机制，可与所有JPA提供程序一起使用。但是，如果查询本身不是JPA查询，即它是本机查询，则new...语法将不起作用，因为查询直接传递给基础RDBMS，而基础RDBMS不理解new关键字，因为它不是SQL标准的一部分。

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

步骤1：声明投影接口

package com.path.to;

public interface SurveyAnswerStatistics {
  String getAnswer();

  int getCnt();
}

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

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