1

I have 3 entities. Category, Subcategory and Product

@Entity
@JsonInclude(value = JsonInclude.Include.NON_EMPTY)
public class Category {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String title;

    private String picture;

    @OneToMany(mappedBy = "category", cascade = CascadeType.ALL)
    @JsonIgnore
    private List<Subcategory> subcategories;

    //getters and setters...

    @JsonProperty("productCount")
    private int getProductCount() {
        //Counting products
        int productCount = 0;

         //!!!!!
         //My problem starts here!

        for (final Subcategory subcategory : subcategories) {
            productCount += subcategory.getProducts().size();
        }
        return productCount;
    }
}
@Entity
@JsonInclude(JsonInclude.Include.NON_EMPTY)
public class Subcategory {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String title;

    @ManyToOne
    @JoinColumn(name = "parent_category_id", nullable = false)
    @JsonIgnore
    private Category category;

    @OneToMany(mappedBy = "subcategory", cascade = CascadeType.ALL)
    private List<Product> products;

    //getters and setters

}
@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String title;
    private String unit;
    private String icon;

    @ManyToOne
    @JoinColumn(name = "subcategory_id", nullable = false)
    @JsonIgnore
    private Subcategory subcategory;

    //getters and setters

}

They are related to each other, but when I want to count the number of products in each category, about 120 queries are executed (depending on the number of subcategories and products)

I was able to reduce it to around 60 queries by adding @EntityGraph to my category repository:

@EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"subcategories"})

However 60 query is still too much. I can't add subcategories.products to this entity graph annotation because that causes org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags

I can suppress this exception by changing the data type of products to Set from List but that creates a Cartesian product and makes the performance worse (it returns around 18,000 rows).

How can I fix this issue without creating a Cartesian product?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Amirhossein
  • 179
  • 4
  • 18
  • See this [answer](https://stackoverflow.com/a/51055523/6277104) – SternK Jan 24 '21 at 20:47
  • 1
    Does this answer your question? [Hibernate throws MultipleBagFetchException - cannot simultaneously fetch multiple bags](https://stackoverflow.com/questions/4334970/hibernate-throws-multiplebagfetchexception-cannot-simultaneously-fetch-multipl) – SternK Jan 24 '21 at 20:51
  • If counting is your problem, I would go with a single JPA query (https://www.baeldung.com/spring-data-jpa-query), this way you do a single query something like this https://stackoverflow.com/questions/15768429/jpa-query-select-many-to-one-query-with-a-count – AndD Jan 24 '21 at 21:01

0 Answers0