Join multiple entities using where clause and get result from that

Question

I have muliple entities configured and mapped successfully and everything work as expected.

Now I want to use a custom query where I join multiple entities and define some where clauses on those.

public interface GiataItemRepository extends JpaRepository<GiataItem, String>{      
    @Query(value="select g from GiataItem g "
            + "join g.service s "
            + "join s.section se "
            + "join se.secPic sp "
            + "where g.giataId = :giataId "
            + "and se.secScope = 'MAINGALLERY' "
            + "and sp.category = 'MAINGALLERY' "
            + "and sp.seqOrder = 0")
    GiataItem findPicture(@Param("giataId") String giataId);
}

The SQL gives me the correct result for my GiataItem. But I dont have the restrictions from my where clauses for all the other mapped entities like service, section and so on.

I am using lazy loading and its clear when I use giataIetem.getService, JPA does a new select and my where clauses are gone.

So how can I achive that I get all my joined entities build on the where clauses and its restrictions.

score 3 · Accepted Answer · answered Aug 23 '17 at 08:50

You can achieve that by using JOIN FETCH:

@Query(value="SELECT g FROM GiataItem g "
        + "JOIN FETCH g.service as s "
        + "JOIN FETCH s.section as se "
        + "JOIN FETCH se.secPic as sp "
        + "WHERE g.giataId = :giataId "
        + "AND se.secScope = 'MAINGALLERY' "
        + "AND sp.category = 'MAINGALLERY' "
        + "AND sp.seqOrder = 0")

Also, take a look at this answer from Vlad Mihalcea:

How does the FetchMode work in Spring Data JPA

Join multiple entities using where clause and get result from that

1 Answers1