MultipleBagFetchException whent I try load entity with 2 collections use JPA EntityGraph

Question

I have user entity:

@ToString
@Data
@Entity
@Table(name = "users")
@NamedEntityGraph(name = "UserWithItems",
        attributeNodes = {
                @NamedAttributeNode("items"),
                @NamedAttributeNode("roles")
        })
public class User {

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

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<Item> items;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private List<Role> roles;
}

item:

@ToString(exclude = "user")
@Data
@Entity
@Table(name = "items")
public class Item {

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

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false)
    private User user;

}

role:

@ToString
@Data
@Entity
@Table(name = "roles")
public class Role {

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

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false)
    private User user;
}

I want load user with items and roles. I use @NamedEntityGraph. It is my repository:

@EntityGraph(value = "UserWithItems", type = EntityGraph.EntityGraphType.LOAD)
@Query("select u from User u where u.id = ?1 and u.name =?2")
User getOneById(Long id, String name);

But I get an error:

Caused by: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.example.egerload.entity.User.roles, com.example.egerload.entity.User.items]
    at org.hibernate.loader.BasicLoader.postInstantiate(BasicLoader.java:75) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.loader.hql.QueryLoader.<init>(QueryLoader.java:108) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:212) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:85) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.makeQueryParametersForExecution(AbstractProducedQuery.java:1350) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1539) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) ~[hibernate-core-5.3.7.Final.jar:5.3.7.Final]
    ... 41 common frames omitted

Possible duplicate of [Hibernate cannot simultaneously fetch multiple bags](https://stackoverflow.com/questions/4334970/hibernate-cannot-simultaneously-fetch-multiple-bags) — Alan Hay, Feb 26 '19 at 14:08

score 3 · Answer 1 · answered Feb 19 '21 at 09:43

You can split your "UserWithItems" @NamedEntityGraph into two @NamedEntityGraphs, resulting in two queries, as described in Hibernate throws MultipleBagFetchException - cannot simultaneously fetch multiple bags - answer of Vlad Mihalcea.

User

@ToString
@Data
@Entity
@Table(name = "users")
@NamedEntityGraphs(
    {
            @NamedEntityGraph(
                    name = "UserWithItems",
                    attributeNodes = {
                            @NamedAttributeNode("items")
                    }
            ),
            @NamedEntityGraph(
                    name = "UserWithRoles",
                    attributeNodes = {
                            @NamedAttributeNode("roles")
                    }
            ),
    }
)
public class User {
    ...
}

I assume you have a repository class. For example with extends JpaRepository. Use each NamedEntityGraph on an extra method. (I have omitted the name condition and @Query("..."). The id condition should be sufficient, since it is the user's identifier. @Query("...") is not needed.)

UserRepository

public interface UserRepository extends JpaRepository<User, Long> {

    @EntityGraph(value = "UserWithItems", type = EntityGraph.EntityGraphType.LOAD)
    Optional<User> getOneWithItemsById(Long id);

    @EntityGraph(value = "UserWithRoles", type = EntityGraph.EntityGraphType.LOAD)
    Optional<User> getOneWithRolesById(Long id);

    ....
}

Finally, you can call both methods in a service.

UserService

public interface UserService {
    Optional<User> readById(Long id);
}

UserServiceImpl

@Service
public class UserServiceImpl implements UserService {

    private final UserRepository userRepository;

    public UserServiceImpl(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Override
    @Transactional
    public Optional<User> readById(Long id) {
        // Load user with items into persistence contex
        userRepository.getOneWithItemsById(id);
        // Load user with roles into persistence context 
        // (There is only one user instance by id within the persistence context)
        return userRepository.getOneWithRolesById(id);
    }

}

MultipleBagFetchException whent I try load entity with 2 collections use JPA EntityGraph

1 Answers1