JpaRepository - Inner Join - Subsequent selects

Question

I'm facing a problem when I try to get an list of ServiceCup with its ServiceLanguage. When I try to manipulate the list of ServiceCup in my service layer hibernate is executing a second query and populate my ServiceCup with all ServiceLanguage again.

ServiceCup x ServiceLanguage x LanguageCup

ServiceCup:

@Data
@Entity
@Table(name = "csm_service")
public class ServiceCup extends BaseEntity implements Serializable {
    private static final long serialVersionUID = 1L;

    private String context;

    // bi-directional many-to-one association to CsmServiceLanguage
    @OneToMany(mappedBy = "service")
    private List<ServiceLanguage> serviceLanguages;

}

ServiceLanguage:

@Data
@Entity
@Table(name = "csm_service_language")
public class ServiceLanguage extends BaseEntity implements Serializable {
    private static final long serialVersionUID = 1L;

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

    // bi-directional many-to-one association to CsmLanguage
    @ManyToOne
    @JoinColumn(name = "csm_language_id_fk")
    private LanguageCup language;

    // bi-directional many-to-one association to CsmService
    @ManyToOne
    @JoinColumn(name = "csm_service_id_fk")
    private ServiceCup service;

}

JpaRepository:

    @Query(value = "select s, sl from ServiceCup s \n" + "INNER JOIN FETCH ServiceLanguage sl on s.id = sl.service \n"
            + "where sl.language.id = :languageId")
    List<ServiceCup> findAllServicesByLanguageId(@Param("languageId") String languageId);

Query in repository layer:

select *all_fields* from csm_service servicecup0_ inner join csm_service_language servicelan1_ on (servicecup0_.id=servicelan1_.csm_service_id_fk) where servicelan1_.csm_language_id_fk=?

But in service layer execute a lot of queries to bring all the relations of ServiceCup. I want the ServiceCup objects populate but only with the results that are in the query.

How can I get a ServiceCup object with only the results of the query?

PS: In my method in service layer I have @Transactional(readOnly = true) but if I remove I can't get the objects related to ServiceCup.

Answer 1

I needed to use projection and not the entity to execute the query only once.

ServiceCupProj

public interface ServiceCupProj {

    public Long getId();

    public String getDescription();

    public String getInternalname();
......

Repository

@Repository
public interface CupServiceRepository extends JpaRepository<ServiceCup, Long> {

@Query(value = "select servicecup0_.id as id, servicelan1_.translated_description as description, servicelan1_.translated_name as internalname \n"
            + "from csm_service servicecup0_ inner join csm_service_language servicelan1_ on (servicecup0_.id=servicelan1_.csm_service_id_fk) \n"
            + "where servicelan1_.csm_language_id_fk = :languageId ", nativeQuery = true)
    List<ServiceCupProj> findAllServicesByLanguageId(@Param("languageId") String languageId);
......

And in the service layer I transform this ServiceCupProj in the entity that I need.