java - Hibernate n+1 issue with 3 entities

Question

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?

question from:https://stackoverflow.com/questions/65875745/hibernate-n1-issue-with-3-entities

Answer 1

Waitting for answers