使用Spring Data映射结果集与实体并进行计数的首选方法 [英] Prefered way to map a result set with entity and count using Spring Data
问题描述
经常会有业务问题来显示所有类别以及使用这些类别的频率.
There is often the business question to show all categories and how often these categories are used.
这个问题很容易通过查询来回答:
This question is easy to answer with an query:
SELECT c.*, count(*) FROM category_assignment ca LEFT JOIN category c on ca.c_id = c.id group by c.id
我要问的是您基于以下内容映射结果集的建议方法:
What i am asking for is your suggested way to map the result set based on the following:
@Entity
public class CategoryAssignment {
@Id
int id;
@ManyToOne(fetch = FetchType.EAGER)
private Category category;
@ManyToOne(fetch = FetchType.EAGER)
private Car car;
}
@Entity
public class Category {
@Id
String id;
TypeEnum type;
...
}
@Entity
public class Car {
@Id
int id;
String name;
...
}
从我的角度来看,映射的最佳结果是从存储库调用中获取一个自定义对象,该对象包含类别"作为实体,并包含计数作为附加变量:
From my point of view the best result of the mapping would be to get a custom object which contains the Category as entity and the count number as additional variable right out of the repository call:
MappingResult result = repository.getCategoriesAndTheirCountOfType(TypeEnum type);
public class MappingResult {
Category category;
BigInteger count;
}
直到现在,我唯一能够实现的方法是手动映射结果集.但是我希望有更简单的方法来映射它.
To only way i was able to achieve it until now was to map the result set manually. But i hope there are easier ways to map it.
推荐答案
您可以使用投影以获取它:
public interface CategoryAndCount {
Category getCategory();
Long getUsageCount();
}
public interface CategoryAssignmentRepository extends CrudRepository<CategoryAssignment, Integer> {
@Query("select c as category, count(*) as usageCount from CategoryAssignment ca join ca.category c where c.type = ?1 group by c")
CategoryAndCount getCategoriesAndTheirCountOfType(TypeEnum type);
}
不要忘记在查询的字段中添加别名(c为类别,count(*)为 usageCount ).
Don't forget to add alias to field in the query (c as category, count(*) as usageCount).
这篇关于使用Spring Data映射结果集与实体并进行计数的首选方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!