如何从 Spring Data JPA GROUP BY 查询中返回自定义对象

JPQL 查询的解决方案

JPA specification 中的 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();
}

重要笔记

确保提供 bean 类的完全限定路径，包括包名。例如，如果 bean 类名为 MyBean，并且它位于包 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 ...") 或 @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 Data Projection 接口。

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

package com.path.to;

public interface SurveyAnswerStatistics {
  String getAnswer();

  int getCnt();
}

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

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

---

这个 SQL 查询返回 List< Object[] > 会。

你可以这样做：

 @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;
     }
 }

---

我知道这是一个老问题，并且已经得到解答，但这是另一种方法：

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

---

定义一个自定义 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();

---

我不喜欢查询字符串中的 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();

代码需要 Lombok 来解析 @Getter

---

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

}

上面的代码对我有用。

---

我使用自定义 DTO（接口）将本机查询映射到 - 最灵活且重构安全的方法。

我遇到的问题 - 令人惊讶的是，界面中的字段顺序和查询中的列很重要。我通过按字母顺序对接口 getter 进行排序，然后以相同的方式对查询中的列进行排序，从而使其工作。

---

使用 JDBC 获取具有列名及其值（在键值对中）的数据：

/*Template class with a basic set of JDBC operations, allowing the use
  of named parameters rather than traditional '?' placeholders.
 
  This class delegates to a wrapped {@link #getJdbcOperations() JdbcTemplate}
  once the substitution from named parameters to JDBC style '?' placeholders is
  done at execution time. It also allows for expanding a {@link java.util.List}
  of values to the appropriate number of placeholders.
 
  The underlying {@link org.springframework.jdbc.core.JdbcTemplate} is
  exposed to allow for convenient access to the traditional
  {@link org.springframework.jdbc.core.JdbcTemplate} methods.*/


@Autowired
protected  NamedParameterJdbcTemplate jdbc;


@GetMapping("/showDataUsingQuery/{Query}")
    public List<Map<String,Object>> ShowColumNameAndValue(@PathVariable("Query")String Query) throws SQLException {

      /* MapSqlParameterSource class is intended for passing in a simple Map of parameter values
        to the methods of the {@link NamedParameterJdbcTemplate} class*/

       MapSqlParameterSource msp = new MapSqlParameterSource();

       // this query used for show column name and columnvalues....
        List<Map<String,Object>> css = jdbc.queryForList(Query,msp);

        return css;
    }

---

我刚刚解决了这个问题：

基于类的投影不适用于查询 native(@Query(value = "SELECT ...", nativeQuery = true))，因此我建议使用 interface 定义自定义 DTO。

在使用 DTO 之前应该验证查询语法正确与否

---

    //in Service      
      `
                public List<DevicesPerCustomer> findDevicesPerCustomer() {
                    LOGGER.info(TAG_NAME + " :: inside findDevicesPerCustomer : ");
                    List<Object[]> list = iDeviceRegistrationRepo.findDevicesPerCustomer();
                    List<DevicesPerCustomer> out = new ArrayList<>();
                    if (list != null && !list.isEmpty()) {
                        DevicesPerCustomer mDevicesPerCustomer = null;
                        for (Object[] object : list) {
                            mDevicesPerCustomer = new DevicesPerCustomer();
mDevicesPerCustomer.setCustomerId(object[0].toString());
                            mDevicesPerCustomer.setCount(Integer.parseInt(object[1].toString()));
                            
                            out.add(mDevicesPerCustomer);
                        }
                    }
            
                    return out;
                }`
        
    //In Repo
        `   @Query(value = "SELECT d.customerId,count(*) FROM senseer.DEVICE_REGISTRATION d  where d.customerId is not null group by d.customerId", nativeQuery=true)
            List<Object[]> findDevicesPerCustomer();`

---