Fetch an entity including child entities with filter/condition

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 fetches a registered program by id and I should only include all the Trainer and Official with isDeleted false. I have 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;
}

My question is, is there any way that I can improve my service more efficiently?

Now what did you do for the `id` for the `RegisteredProgram` entity? — K.Nicholas, Jan 15 '21 at 01:38
what do you mean what did i do? `id` field for `RegisteredProgram` is inherited from the parent class that's why its not indicated from my example — Donato Amasa, Jan 15 '21 at 01:39
Hi, i have not tried it yet, do you have any references that i can look up to? — Donato Amasa, Jan 15 '21 at 01:52

score 1 · Accepted Answer · answered Jan 15 '21 at 02:37

1

Yes: The query to select only trainers and officials isDeleted is false is part of JPA. A @EntityGraph is also part of JPA but can be done a little easier through spring-data-jpa.

public interface RegisteredProgramRepository extends JpaRepository<RegisteredProgram, Long>{
    @Query("select rp from RegisteredProgram rp join rp.officials rpos join rp.trainers rpts where rp.id = :id and rpos.isDeleted = false and rpts.isDeleted = false")
    @EntityGraph(attributePaths = {"officials", "trainers"}, type = EntityGraphType.LOAD)
    RegisteredProgram getByIdNotDeleted(@Param("id") Long id);
}

This does everything through JPA with a single query.

answered Jan 15 '21 at 02:37

K.Nicholas

10,956
4
46
66

Hi, thanks for your input. I tried your suggested solution but ended up with this error: `org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags` – Donato Amasa Jan 15 '21 at 05:33
I researched and saw this solution https://stackoverflow.com/questions/4334970/hibernate-throws-multiplebagfetchexception-cannot-simultaneously-fetch-multipl but I still can't get the query to execute properly – Donato Amasa Jan 15 '21 at 05:33
It's because I changed the `List` to `Set` in the `RegisteredProgram` entity. JPA cannot issue queries with two `List` relationships at the same.I also changed the names from officialList to officials and trainerLIst to trainers.Finally I added lombok excludes to the foreign key fields for both equals and toString. You need to be careful with that stuff. – K.Nicholas Jan 15 '21 at 09:20
1

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:21

score 0 · Answer 2 · answered Aug 22 '23 at 11:31

0

Try to use @Where(clause = "deleted=false") on the child entity

answered Aug 22 '23 at 11:31

Alexander Miroshnichenko

1

Fetch an entity including child entities with filter/condition

2 Answers2