使用 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.

推荐答案

你可以使用Projections 得到它:

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 as category，count(*) as usageCount).

Don't forget to add alias to field in the query (c as category, count(*) as usageCount).

更多信息

这篇关于使用 Spring Data 将结果集映射到实体和计数的首选方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！