How to avoid MultipleBagFetchException

Question

Good day to all!

In my Spring project, I am trying to get rid of a fairly popular error in my opinion - org.hibernate.loader.MultipleBagFetchException. I want to implement the methods getOneClothesById(Long id) and getAllClothes().

In @ Service-layer, I want to take data fromClothes (size,category, color) and display them on the screen and/or modify them before sending somewhere else.

I read a couple of books and even went to the 4-5th page in Google, but I suppose that I still haven't found the best solution.

People suggest the following approaches to solve the problem :

1. Use Set<...> instead of List<...>
   Links:

   • SO_Answer(2011)
   • Hibernate Tips: How to avoid Hibernate’s MultipleBagFetchException (OLD_POST)

   My comment: I believe that this is not a good approach. Here is the link with explanation:

   • The best way to fix the Hibernate MultipleBagFetchException
2. Use Fetch.Eager instead of Fetch.Lazy, or add @LazyCollection(LazyCollectionOption.FALSE)
   Links:
   • SO_Answer(2011)
   • SO_Answer(2015) My comment: That's not the best solution, as it doesn't deal with the problem in the long-term.

3. Use @OrderColumn (before - @IndexColumn)
   Links:

   • SO_Answer(2019)
   • SO_Answer(2015)

   My comment: That deasls (partly) with the problem, but according to documentation - that would exclude option orderBy

4. Use Vlad Mihalcea's approach: Links:

   • Vlad's SO_answer(2014)
   • Vlad's SO_answer(2018)
   • Vlad's post on his own website

   My comment: I suppose that this is the best solution that deals with org.hibernate.LazyInitializationException is his. However, I would like to adopt his approach to the reality of the Spring Framework (2.2.6.RELEASE).

What I want:

I want to find a better approach to solving the problem within the framework of the Spring Framework. The answer that is after the picture with the Database is essentially a solution to the problem. However, I have the following questions:

1. I do not like the fact that I use @Transactional to get rid of the problemorg.hibernate.LazyInitializationException: failed to lazily initialize ...
2. How to properly take this approach into a separate class, to reuse it? It would be the right solution if I create the class ClothesRepositoryFetch with the annotation @Repository and then call this repository in the @Service-layer.
3. Is there any better solution to this problem using only JPA?

---

My Database looks like:

---

ClothesService.java

    @Transactional
    public ResultDTO getOneClothesById(Long id){

        ResultDTO resultDTO = new ResultDTO();

        //Getting clothes with Category
        Clothes clothes = entityManager
                .createQuery(
                        "select distinct clothes " +
                                "from Clothes clothes " +
                                "left join fetch clothes.categories " +
                                "where clothes.id = :id", Clothes.class)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .setParameter("id", id)
                .getSingleResult();

        //Getting clothes with Color
        clothes = entityManager
                .createQuery(
                        "select distinct clothes " +
                                "from Clothes clothes " +
                                "left join fetch clothes.colors color " +
                                "where clothes = :clothes", Clothes.class)
                .setParameter("clothes", clothes)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getSingleResult();

        //Getting clothes with Size
        clothes = entityManager
                .createQuery(
                        "select distinct clothes " +
                                "from Clothes clothes " +
                                "left join fetch clothes.sizes size " +
                                "where clothes = :clothes", Clothes.class)
                .setParameter("clothes", clothes)
                .setHint(QueryHints.PASS_DISTINCT_THROUGH, false)
                .getSingleResult();

        System.out.println(clothes.getCategories().size());
        System.out.println(clothes.getColors().size());
        System.out.println(clothes.getSizes().size());

        if (clothes != null){
            resultDTO.setResult(ResultForDTO.SUCCESS);
            resultDTO.addClothesDTO(converterClothesToDTO(clothes));
        } else {
            resultDTO.setResult(ResultForDTO.ERROR);
            resultDTO.setMessage("Невозможно найти одежду по такому ID");
        }

        return resultDTO;
    }

---

Code

Clothes.java:

@Entity
@Table
@NoArgsConstructor
@Getter
@Setter
@ToString
public class Clothes {

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

    @Column
    private String name;

    @Column
    private Double price;



    /*
    Color: ManyToMany
     */
    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "clother_color",
            joinColumns = { @JoinColumn(name = "clother_id") },
            inverseJoinColumns = { @JoinColumn(name = "color_id") })
    private List<Color> colors = new ArrayList<>();

    public void addColor(Color color) {
        colors.add(color);
        color.getClothes().add(this);
    }

    public void removeColor(Color color) {
        colors.remove(color);
        color.getClothes().remove(this);
    }



    /*
    Size: ManyToMany
     */
    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "clother_size",
            joinColumns = { @JoinColumn(name = "clother_id") },
            inverseJoinColumns = { @JoinColumn(name = "size_id") })
    private List<Size> sizes = new ArrayList<>();

    public void addSize(Size size) {
        sizes.add(size);
        size.getClothes().add(this);
    }

    public void removeSize(Size size) {
        sizes.remove(size);
        size.getClothes().remove(this);
    }



    /*
    Category: ManyToMany
     */
    @ManyToMany(fetch = FetchType.LAZY,
            cascade = {
                    CascadeType.PERSIST,
                    CascadeType.MERGE
            })
    @JoinTable(name = "clother_category",
            joinColumns = { @JoinColumn(name = "clother_id") },
            inverseJoinColumns = { @JoinColumn(name = "category_id") })
    private List<Category> categories = new ArrayList<>();

    public void addCategory(Category category) {
        categories.add(category);
        category.getClothes().add(this);
    }

    public void removeCategory(Category category) {
        categories.remove(category);
        category.getClothes().remove(this);
    }


    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Clothes clothes = (Clothes) o;
        return Objects.equals(id, clothes.id);
    }
}

Category.java:

Category, Size, Color have a common structure, so I will present only one of them

@Entity
@Table
@NoArgsConstructor
@Getter
@Setter
public class Category {

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

    @Column
    private String name;

    @ManyToMany(mappedBy="categories")
    private List<Clothes> clothes = new ArrayList<>();

    public void addClothes(Clothes clothes) {
        this.clothes.add(clothes);
        clothes.getCategories().add(this);
    }

    public void removeClothes(Clothes clothes) {
        this.clothes.remove(clothes);
        clothes.getCategories().remove(this);
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Category category = (Category) o;
        return Objects.equals(id, category.id);
    }
}

---

Answer 1

This seems to be a fundamental problem of Hibernate: to avoid cartesian products, they forbid multiple Lists. Quick fix is to use a Set instead, but that does not solve the efficiency problem. What would solve it is to use Entity Graphs, as e.g. described by https://thorben-janssen.com/jpa-21-entity-graph-part-1-named-entity/ . That way, you can specify exactly what gets loaded eagerly for each query.