Why is Hibernate using OUTER joins

Question

I am using Hibernate 4.3.11.Final with H2 (disk based) database

Key Hibernate Classes are

public class Song
{
    @Id
    private Integer recNo;

    ....
    @OneToMany(fetch=FetchType.EAGER, cascade={CascadeType.ALL})
    private List<CoverArt> coverArts;
}

public class CoverArt
{
    @Id
    @GeneratedValue
    private Integer id;

    .......
    @OneToOne
    private CoverImage coverImage;

}

public class CoverImage
{
    @Id
    @Column(length = 1000)
    private String dataKey;
}

I have been examining the Hibernate Queries generated to see if I can improve performance by reducing database calls, I was suprised to find that

Joins were LEFT OUTER JOINS, I assumed they would be LEFT INNER JOINS since CoverArt only exists as part of a Song. CoverImage does exist independently since this stores large image data so want shared between songs, but when I am retrieving a song Im only interested in CoverImages linked via CoverArt.

Are there some adjustments I can make to improve this ?

 ....
 from Song this_ 
 left outer join Song_CoverArt coverarts2_ on this_.recNo=coverarts2_.Song_recNo 
 left outer join CoverArt coverart3_ on coverarts2_.coverArts_id=coverart3_.id
 left outer join CoverImage coverimage4_ on coverart3_.coverImage_dataKey=coverimage4_.dataKey 
 where this_.recNo in (?)

score 1 · Accepted Answer · answered Oct 03 '18 at 20:08

1

If you use inner joins then you will automatically only get Songs that have non-null Song_CoverArt, CoverArt, and CoverImage. If any of them do not exist that entire entry will not be returned. By using outer joins you get all of the requested songs and the missing values are null.

answered Oct 03 '18 at 20:08

Malcolm

461
2
10

No, not if I use LEFT INNER JOINS – Paul Taylor Oct 04 '18 at 10:46
Actually maybe Im getting confused since when i look at my own SQL it sjust says 'LEFT JOIN' rather than 'LEFT INNER' or 'LEFT OUTER'. I have a follow up if HIbernate does return a Song with CoverArt but no CoverImage this would cause me problem, can i protect against this happening to make the final join a regular INNER JOIN ? – Paul Taylor Oct 04 '18 at 10:54
The OUTER in LEFT OUTER JOIN is optional https://stackoverflow.com/questions/6625964/left-join-vs-left-outer-join. – Malcolm Oct 05 '18 at 19:51
As for forcing the inner join in hibernate, there are a couple of answers to that question on stackoverflow, I am not sure which pertains best to your situation. So search for "hibernate inner join" and hopefully one of them will click. – Malcolm Oct 05 '18 at 20:09

Why is Hibernate using OUTER joins

1 Answers1