Nested fetch join with JPQL and Hibernate

Question

I am writing a JPQL query (with Hibernate as my JPA provider) to fetch an entity Company and several of its associations. This works fine with my "simple" ManyToMany associations, like so:

@Entity
@Table(name = "company")
@NamedQueries({
        @NamedQuery(
                name = "Company.profile.view.byId",
                query = "SELECT c " +
                        "FROM Company AS c " +
                        "INNER JOIN FETCH c.city AS city " + <-- @ManyToOne
                        "LEFT JOIN FETCH c.acknowledgements " + <-- @ManyToMany
                        "LEFT JOIN FETCH c.industries " + <-- @ManyToMany
                        "WHERE c.id = :companyId"
        )
})
public class Company { ... }

Hibernate creates a single query to fetch the above, which is good. However, my Company entity also has a many-to-many association with data stored in the intermediate table, hence why this is mapped as @OneToMany and @ManyToOne associations between three entities.

Company <-- CompanyService --> Service

These are the three entities that I have in my code. So a Company instance has a collection of CompanyService entities, which each has a relation to a Service instance. I hope that makes sense - otherwise please check the source code at the end of the question.

Now I would like to fetch the services for a given company by modifying the above query. I read in advance that JPA doesn't allow nested fetch joins or even aliases for joins, but that some JPA providers do support it, and so I tried my luck with Hibernate. I tried to modify the query as such:

@Entity
@Table(name = "company")
@NamedQueries({
        @NamedQuery(
                name = "Company.profile.view.byId",
                query = "SELECT c " +
                        "FROM Company AS c " +
                        "INNER JOIN FETCH c.city AS city " +
                        "LEFT JOIN FETCH c.acknowledgements " +
                        "LEFT JOIN FETCH c.industries " +
                        "LEFT JOIN FETCH c.companyServices AS companyService " +
                        "LEFT JOIN FETCH companyService.service AS service " +
                        "WHERE c.id = :companyId"
        )
})
public class Company { ... }

Now, instead of creating a single query, Hibernate creates the following queries:

#1
select ...
from company company0_
inner join City city1_ on company0_.postal_code = city1_.postal_code
[...]
left outer join company_service companyser6_ on company0_.id = companyser6_.company_id
left outer join service service7_ on companyser6_.service_id = service7_.id
where company0_.id = ?

#2
select ...
from company company0_
inner join City city1_ on company0_.postal_code = city1_.postal_code
where company0_.id = ?

#3
select service0_.id as id1_14_0_, service0_.default_description as default_2_14_0_, service0_.name as name3_14_0_
from service service0_
where service0_.id = ?

#4
select service0_.id as id1_14_0_, service0_.default_description as default_2_14_0_, service0_.name as name3_14_0_
from service service0_
where service0_.id = ?

Query #1 I left out the irrelevant joins as these are OK. It appears to select all of the data that I need, including the services and the intermediate entity data (CompanyService).

Query #2 This query simply fetches the company from the database and its City. The city association is eagerly fetched, but the query is still generated even if I change it to lazy fetching. So honestly I don't know what this query is for.

Query #3 + Query #4 These queries are looking up Service instances based on ID, presumably based on service IDs fetched in Query #1. I don't see the need for this query, because this data was already fetched in Query #1 (just as the data from Query #2 was already fetched in Query #1). Also, this approach obviously does not scale well if a company has many services.

The strange thing is that it seems like query #1 does what I want, or at least it fetches the data that I need. I just don't know why Hibernate creates query #2, #3 and #4. So I have the following questions:

• Why does Hibernate create query #2, #3 and #4? And can I avoid it?
• Does Hibernate support nested association fetching even though JPA doesn't? If so, how would I go about it in my case?
• Is this behavior normal, or is it because what I am trying to do is just not supported, and therefore I get weird results? This would seem odd, because query #1 looks perfectly fine

Any pointers of mistakes or alternative solutions to accomplish what I want would be much appreciated. Below is my code (getters and setters excluded). Thanks a lot in advance!

Company entity

@Entity
@Table(name = "company")
@NamedQueries({
        @NamedQuery(
                name = "Company.profile.view.byId",
                query = "SELECT c " +
                        "FROM Company AS c " +
                        "INNER JOIN FETCH c.city AS city " +
                        "LEFT JOIN FETCH c.acknowledgements " +
                        "LEFT JOIN FETCH c.industries " +
                        "LEFT JOIN FETCH c.companyServices AS companyService " +
                        "LEFT JOIN FETCH companyService.service AS service " +
                        "WHERE c.id = :companyId"
        )
})
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private int id;

    // ...

    @ManyToOne(fetch = FetchType.EAGER, targetEntity = City.class, optional = false)
    @JoinColumn(name = "postal_code")
    private City city;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "company_acknowledgement", joinColumns = @JoinColumn(name = "company_id"), inverseJoinColumns = @JoinColumn(name = "acknowledgement_id"))
    private Set<Acknowledgement> acknowledgements;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "company_industry", joinColumns = @JoinColumn(name = "company_id"), inverseJoinColumns = @JoinColumn(name = "industry_id"))
    private Set<Industry> industries;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "company")
    private Set<CompanyService> companyServices;
}

CompanyService entity

@Entity
@Table(name = "company_service")
@IdClass(CompanyServicePK.class)
public class CompanyService implements Serializable {
    @Id
    @ManyToOne(targetEntity = Company.class)
    @JoinColumn(name = "company_id")
    private Company company;

    @Id
    @ManyToOne(targetEntity = Service.class)
    @JoinColumn(name = "service_id")
    private Service service;

    @Column
    private String description;
}

Service entity

@Entity
@Table(name = "service")
public class Service {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private int id;

    @Column(length = 50, nullable = false)
    private String name;

    @Column(name = "default_description", nullable = false)
    private String defaultDescription;
}

Fetching data

public Company fetchTestCompany() {
    TypedQuery<Company> query = this.getEntityManager().createNamedQuery("Company.profile.view.byId", Company.class);
    query.setParameter("companyId", 123);

    return query.getSingleResult();
}

Answer 1

Okay, it seems like I figured it out. By setting the fetch type to FetchType.LAZY in CompanyService, Hibernate stopped generating all of the redundant queries that were basically fetching the same data again. Here is the new version of the entity:

@Entity
@Table(name = "company_service")
@IdClass(CompanyServicePK.class)
public class CompanyService implements Serializable {
    @Id
    @ManyToOne(fetch = FetchType.LAZY, targetEntity = Company.class)
    @JoinColumn(name = "company_id")
    private Company company;

    @Id
    @ManyToOne(fetch = FetchType.LAZY, targetEntity = Service.class)
    @JoinColumn(name = "service_id")
    private Service service;

    @Column
    private String description;
}

The JPQL query remains the same.

However, in my particular case with the number of associations my Company entity has, I was getting a lot of duplicated data back, and so it was more efficient to let Hibernate execute an additional query. I accomplished this by removing the two join fetches from my JPQL query and changing my query code to the below.

@Transactional
public Company fetchTestCompany() {
    TypedQuery<Company> query = this.getEntityManager().createNamedQuery("Company.profile.view.byId", Company.class);
    query.setParameter("companyId", 123);

    try {
        Company company = query.getSingleResult();
        Hibernate.initialize(company.getCompanyServices());

        return company;
    } catch (NoResultException nre) {
        return null;
    }
}

By initializing the companyServices association, Hibernate executes another query to fetch the services. In my particular use case, this is better than fetching a ton of redundant data with one query.

I hope this helps someone. If anyone has any better solutions/improvements, then I would of course be happy to hear them.