Search code examples
javadatabasehibernatejoinhql

HQL Join with three tables

I'm having some issues with HQL since I'm a newbie with it. Even though I don't have issues with "simple queries", I am currently stuck with a query involving three tables.

I have already gone through some tutorials, but I haven't been able to find a valid example for my needs. I have tried my best to explain my problem:

I have three different tables, let's name them HOUSES, OWNERS, and OWNERINFOS.

Given a townId, I need to list all houses from that town, including name and surname of that house-owner.

I made a really simple graph to show the connections between tables:

enter image description here

I'm also not sure which join-strategy I should use. Any kind of help would be highly appreciated, since solving this is a priority for me.

Thanks in advance!

Solution

  • It is just a template

    class House {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fk_town")
    private Town town;

    @OneToMany(mappedBy = "house")
    private List<Owner> owners;

}

class Owner {

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "fk_house")
    private House house;

    @OneToOne
    @JoinColumn(name = "fk_owner_info")
    private OwnerInfo ownerInfo;

}

class OwnerInfo {

    @OneToOne(mappedBy = "ownerInfo", fetch = FetchType.LAZY)
    private Owner owner;

}

    The simplest case with fetch all owners with owner info

    from House h inner join fetch h.owners where h.town.id = :townId