CODEWITHSUNDEEP
jpaspring-datahql
Jochen Marsaille
Jochen Marsaille

Reputation: 108

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.

Upvotes: 2

Views: 4754

Answers (2)

Alan Hay
Alan Hay

Reputation: 23246

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;
}
  1. 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;
}

Upvotes: 0

Cepr0
Cepr0

Reputation: 30389

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

Upvotes: 8

Related Questions