我正在开发一个使用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" }
]
这适用于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;
}
}
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();
}
如上所述，new ... 语法是一个受 JPA 支持的机制，适用于所有的 JPA 提供者。然而，如果查询本身不是一个 JPA 查询，也就是说，它是一个本地查询，new ... 语法将无法工作，因为查询会直接传递给底层的关系数据库管理系统（RDBMS），而 RDBMS 不理解 new 关键字，因为它不是 SQL 标准的一部分。
在这种情况下，需要使用Spring Data Projection接口来替代 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();
}
AS关键字，将结果字段映射到投影属性，以实现明确的映射。 @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类，比如SurveyQueryAnalytics，并将查询返回值存储在您的自定义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。
@Query 中的 new 的基于类的投影才有效。如果基于类而不使用 new（使用此构造函数 HashMap<String, Object>）将会很好。但是我得到了 org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [package.MyClass]。 - mkczyk@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();
}
/*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;
}
我刚刚解决了这个问题：
@Query(value = "SELECT ...", nativeQuery = true))一起使用，因此我建议使用接口定义自定义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();`
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate class [SurveyAnswerReport] [select new SurveyAnswerReport(v.answer,count(v.id)) from com.furniturepool.domain.Survey v group by v.answer] at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) at org.hibernate.jpa.spi.AbstractEnti..........- Pranav C Balancom.domain.dto.SurveyAnswerReport。 - manishJpaRepository返回自定义类型时，出现了java.lang.IllegalArgumentException: PersistentEntity must not be null!的错误提示。这是我漏掉了某些配置吗？ - marioosh