I have several tables in my DB:
The games table: has such columns as team1, team2. both columns references another table The Team table: has one to many relationship with players table.
Players table: has one to one reference with skills table.
As Entities in the Team entity I have collection of players with FetchType set to LAZY.
I am making select query to get many games. Whatever is set to fetch type, LAZY or EAGER, it takes quite a long time to get all entities of games, because it loads both teams, and for all teams it loads many players one by one as seen from DB log. And finally for each player it loads it's skills.
I want to make it as one query (not one by one as its done now). But to do it, I need to make join fetch queries correctly in JPQL.
I don't know how to make it.
The analogue query in pure sql would look like:
select ... from games
inner join teams as t1 on team_id1 = t1.id
inner join players as ps1 on ps1.team_id = team_id1
inner join skills as sk1 on sk1.players_id = ps1.id
inner join teams as t2 on team_id2 = t2.id
inner join players as ps2 on ps2.team_id = team_id2
inner join skills as sk2 on sk2.players_id = ps2.id
where ...
I can make several Inner joins in JQPL, but can't understand how to tell hibernate to load each collection's object's skills.
Something like this should work
select g from Game g
join fetch g.teams t
join fetch t.players p
join fetch p.skills s
where ...