Prefered way to map a result set with entity and c

问题:

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.

回答1:

You can use Projections to get 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);
}

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

More info

回答2:

You can have it as a property of a Catgeory by following one of 2 approaches. The benefit is that the property is always available and you do not need to call any specific query.

1. Create a database view, say category_summary_data and then map this to Category using either a secondary table or as a @OneToOne

--

@Entity
@Table(name = "categories")
@SecondaryTable(name = "category_summary_data", pkJoinColumns = {...})
public class Category {
    @Id
    String id;
    TypeEnum type;

    @Column(name = "usage_count", table = "category_summary_data", insertable = false, updateable = false)
    // do not use int https://stackoverflow.com/questions/43407889/prefered-way-to-map-a-result-set-with-entity-and-count-using-spring-data/43411008#43411008
    Integer usageCount;
}

2. Alternatively you can use some provider extension. Hibernate offers the @Formula annotation which you can use to set computed values.

--

@Entity
@Table(name = "categories")
public class Category {
    @Id
    String id;
    TypeEnum type;

    @Formula("some sql to count the records")
    int usageCount;
}