MultipleBagFetchException: cannot simultaneously fetch multiple bags

Question

I have the following entities

RegisteredProgram

@Data
@NoArgsConstructor
@Entity
@EntityListeners(RegisteredProgramAuditListener.class)
public class RegisteredProgram extends Auditable<String> {

    @OneToMany(mappedBy = "registeredProgram", cascade = CascadeType.ALL)
    @JsonBackReference
    private List<Trainer> trainerList;

    @OneToMany(mappedBy = "registeredProgram", cascade = CascadeType.ALL)
    @JsonBackReference
    private List<Official> officialList;
}

Trainer

@Data
@NoArgsConstructor
@EntityListeners(TrainerAuditListener.class)
@Entity
public class Trainer extends Auditable<String> {

    @ManyToOne
    @JoinColumn(name = "REGISTERED_PROGRAM_ID", nullable = false)
    @JsonManagedReference
    private RegisteredProgram registeredProgram;

    @Type(type = "yes_no")
    private Boolean isDeleted = false;
}

Official

@Data
@NoArgsConstructor
@EntityListeners(OfficialAuditListener.class)
@Entity
public class Official extends Auditable<String> {

    @ManyToOne
    @JoinColumn(name = "REGISTERED_PROGRAM_ID", nullable = false)
    @JsonManagedReference
    private RegisteredProgram registeredProgram;

    @Type(type = "yes_no")
    private Boolean isDeleted = false;
}

Basically I have entities with many to one relationship with RegisteredProgram, (Trainer-RegisteredProgram, Official-RegisteredProgram). Now I have a service which already achieves my requirement, to fetch a registered program by id and I should only include all the Trainer and Official with isDeleted false. See the service below:

Service

@Override
public RegisteredProgramRequestDto getRegisteredProgramDto(Long id) {
    RegisteredProgram registeredProgram = registeredProgramRepository.getOne(id);
    RegisteredProgramRequestDto registeredProgramRequestDto = programRegistrationMapper
            .registeredProgramToRequestDto(registeredProgram);
    registeredProgramRequestDto.setOfficialDtoList(
            registeredProgramRequestDto.getOfficialDtoList()
                    .stream()
                    .filter(officialDto -> !officialDto.getIsDeleted())
                    .collect(Collectors.toList())
    );
    registeredProgramRequestDto.setTrainerDtoList(
            registeredProgramRequestDto.getTrainerDtoList()
                    .stream()
                    .filter(trainerDto -> !trainerDto.getIsDeleted())
                    .collect(Collectors.toList())
    );
    return registeredProgramRequestDto;
}

Now, I tried to use @Query and @EntityGraph so I can be able to get the desired output using only a single query.

Repository

@Repository
public interface RegisteredProgramRepository extends JpaRepository<RegisteredProgram, Long>, QuerydslPredicateExecutor<RegisteredProgram> {

    @Query("select rp from RegisteredProgram rp join rp.officialList rpos join rp.trainerList rpts where rp.id = :id and rpos.isDeleted = false and rpts.isDeleted = false")
    @EntityGraph(attributePaths = {"officialList", "trainerList"}, type = EntityGraph.EntityGraphType.LOAD)
    RegisteredProgram getByIdNotDeleted(@Param("id") Long id);
}

Updated Service

@Override
public RegisteredProgramRequestDto getRegisteredProgramDto(Long id) {
    RegisteredProgram registeredProgram = registeredProgramRepository.getByIdNotDeleted(id);
    return programRegistrationMapper
            .registeredProgramToRequestDto(registeredProgram);
}

But after implementing it, i am encountering the error below:

org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.tesda8.region8.program.registration.model.entities.RegisteredProgram.officialList, com.tesda8.region8.program.registration.model.entities.RegisteredProgram.trainerList]

I already searched through stackoverflow and bumped into this but I still can't get my query to execute properly. Any ideas on how should I approach this?

score 3 · Accepted Answer · answered Jan 19 '21 at 23:27

The regular fix for solving MultipleBagFetchException is change List typed fields on Set typed, like this:

...
public class RegisteredProgram extends Auditable<String> {

    @OneToMany(mappedBy = "registeredProgram", cascade = CascadeType.ALL)
    @JsonBackReference
    private Set<Trainer> trainerList = new HashSet<>();

    @OneToMany(mappedBy = "registeredProgram", cascade = CascadeType.ALL)
    @JsonBackReference
    private Set<Official> officialList = new HashSet<>();
    ...
}

For more details see: https://thorben-janssen.com/hibernate-tips-how-to-avoid-hibernates-multiplebagfetchexception/

Note: Remember about equals and hashcode for Set data structure and avoiding Lombok & Hibernate pitfalls(https://thorben-janssen.com/lombok-hibernate-how-to-avoid-common-pitfalls/). Please pay attention for 'Avoid @Data' topic, because I see you are using that combination, that combination can produce unexpected behavior!

After changing from `List` to `Set` and removing `@Data` from the entities and replaced it instead with `@Getter` and `@Setter` solved everything. Thank you man! — Donato Amasa, Jan 20 '21 at 01:23
This doesn't solve cartesian product. See here: https://allaroundjava.com/hibernate-cartesian-product-problem/ what you can do is to add this to fetch selects independently @org.hibernate.annotations.Fetch(FetchMode.SELECT) — Orbita, Jul 09 '21 at 06:08

MultipleBagFetchException: cannot simultaneously fetch multiple bags

1 Answers1

Linked

Related