Hibernate HQL join fetch not recursively fetching

Question

I have the following query and method

private static final String FIND = "SELECT DISTINCT domain FROM Domain domain LEFT OUTER JOIN FETCH domain.operators LEFT OUTER JOIN FETCH domain.networkCodes WHERE domain.domainId = :domainId";

@Override
public Domain find(Long domainId) {
    Query query = getCurrentSession().createQuery(FIND);
    query.setLong("domainId", domainId);
    return (Domain) query.uniqueResult();
}

With Domain as

@Entity
@Table
public class Domain {
    @Id
    @GenericGenerator(name = "generator", strategy = "increment")
    @GeneratedValue(generator = "generator")
    @Column(name = "domain_id")
    private Long domainId;

    @Column(nullable = false, unique = true)
    @NotNull
    private String name;

    @Column(nullable = false)
    @NotNull
    @Enumerated(EnumType.STRING)
    private DomainType type;

    @OneToMany(cascade = {
            CascadeType.PERSIST,
            CascadeType.MERGE
    }, fetch = FetchType.EAGER)
    @JoinTable(joinColumns = {
            @JoinColumn(name = "domain_id")
    }, inverseJoinColumns = {
            @JoinColumn(name = "code")
    })
    @NotEmpty
    @Valid // needed to recur because we specify network codes when creating the domain
    private Set<NetworkCode> networkCodes = new HashSet<>();

    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(joinColumns = {
            @JoinColumn(name = "parent", referencedColumnName = "domain_id")
    }, inverseJoinColumns = {
            @JoinColumn(name = "child", referencedColumnName = "domain_id")
    })
    private Set<Domain> operators = new HashSet<>();
    // more
}

I would expect this single query to fetch the Set<NetworkCode> and Set<Domain> relations, but it doesn't. Say that the Domain I query has two operators, Hibernate would perform 1 + 2 * 2 = 5 queries

Hibernate: select distinct domain0_.domain_id as domain1_1_0_, domain2_.domain_id as domain1_1_1_, networkcod4_.code as code2_2_, domain0_.name as name1_0_, domain0_.type as type1_0_, domain2_.name as name1_1_, domain2_.type as type1_1_, operators1_.parent as parent1_0__, operators1_.child as child4_0__, networkcod3_.domain_id as domain1_1_1__, networkcod3_.code as code5_1__ from domain domain0_ left outer join domain_operators operators1_ on domain0_.domain_id=operators1_.parent left outer join domain domain2_ on operators1_.child=domain2_.domain_id inner join domain_network_codes networkcod3_ on domain0_.domain_id=networkcod3_.domain_id inner join network_code networkcod4_ on networkcod3_.code=networkcod4_.code where domain0_.domain_id=?
Hibernate: select operators0_.parent as parent1_1_, operators0_.child as child4_1_, domain1_.domain_id as domain1_1_0_, domain1_.name as name1_0_, domain1_.type as type1_0_ from domain_operators operators0_ inner join domain domain1_ on operators0_.child=domain1_.domain_id where operators0_.parent=?
Hibernate: select networkcod0_.domain_id as domain1_1_1_, networkcod0_.code as code5_1_, networkcod1_.code as code2_0_ from domain_network_codes networkcod0_ inner join network_code networkcod1_ on networkcod0_.code=networkcod1_.code where networkcod0_.domain_id=?
Hibernate: select operators0_.parent as parent1_1_, operators0_.child as child4_1_, domain1_.domain_id as domain1_1_0_, domain1_.name as name1_0_, domain1_.type as type1_0_ from domain_operators operators0_ inner join domain domain1_ on operators0_.child=domain1_.domain_id where operators0_.parent=?
Hibernate: select networkcod0_.domain_id as domain1_1_1_, networkcod0_.code as code5_1_, networkcod1_.code as code2_0_ from domain_network_codes networkcod0_ inner join network_code networkcod1_ on networkcod0_.code=networkcod1_.code where networkcod0_.domain_id=?

I'm guessing this is because I'm joining the operators Domain elements but they have to join themselves.

Is there an HQL query I can execute that would do both?

Answer 1

If you know that you have only two levels in your tree, have you thought of joining deeper one level. Something like below?

SELECT DISTINCT domain FROM Domain domain 
  LEFT OUTER JOIN FETCH domain.operators operators1 
  LEFT OUTER JOIN FETCH domain.networkCodes 
  LEFT OUTER JOIN FETCH operators1.operators operators2 
  LEFT OUTER JOIN FETCH operators1.networkCodes
WHERE domain.domainId = :domainId

Answer 2

The Hibernate Relations Works with different Fetch Strategies..!!

Hibernate provides 4 strategies for retrieving data:

SELECT

@OneToMany(mappedBy="tableName", cascade=CascadeType.ALL)
@Column(name="id") 
@Fetch(FetchMode.SELECT)

In this Method there are Multiple SQLs fired. This first one is fired for retrieving all the records in the Parent table. The remaining are fired for retrieving records for each Parent Record. This is basically the N+1 problem. The first query retrieves N records from database, in this case N Parent records. For each Parent a new query retrieves Child. Therefore for N Parent, N queries retrieve information from Child table.

JOIN

@OneToMany(mappedBy="tableName", cascade=CascadeType.ALL)
@Column(name="id")
@Fetch(FetchMode.JOIN) 

This is similar to the SELECT fetch strategy except that fact that all database retrieval take place upfront in JOIN fetch unlike in SELECT where it happens on a need basis. This can become an important performance consideration.

SUBSELECT

 @OneToMany(mappedBy="tableName", cascade=CascadeType.ALL)
 @Column(name="id")
 @Fetch(FetchMode.SUBSELECT)

Two SQLs are fired. One to retrieve all Parent and the second uses a SUBSELECT query in the WHERE clause to retrieve all child that has matching parent ids.

BATCH

@OneToMany(mappedBy="tableName", cascade=CascadeType.ALL)
@Column(name="id")
@@BatchSize(size=2)

The batch size maps to the number of Parent whose child are retrieved. So we can specify the number of records to be fetched at a time.But Multiple queries will be executed.!!

one-to-many & many-to-many allows - join, Select and SubSelect

many-to-one & one-to-one allows - Join and Select

---

Hibernate also distinguishes between (when is the associations are fetched)

1.Immediate fetching -

an association, collection or attribute is fetched immediately, when the Parent is loaded. (lazy=“false”)

2.Lazy collection fetching -

a collection is fetched when the application invokes an operation upon that collection. (This is the default for collections.(lazy=“true”)

3."Extra-lazy" collection fetching -

individual elements of the collection are accessed from the database as needed. Hibernate tries not to fetch the whole collection into memory unless absolutely needed (suitable for very large collections) (lazy=“extra”)

4.Proxy fetching -

a single-valued association is fetched when a method other than the identifier getter is invoked upon the associated object. (lazy=“proxy”)

5."No-proxy" fetching -

a single-valued association is fetched when the instance variable is accessed. Compared to proxy fetching, this approach is less lazy.(lazy=“no-proxy”)

6.Lazy attribute fetching -

an attribute or single valued association is fetched when the instance variable is accessed. (lazy=“true”)

one-to-many & many-to-many allows Immediate, Layzy, Extra Lazy

many-to-one & one-to-one allows Immediate Proxy, No Proxy

Answer 3

You marked your associations EAGER. So, whatever you do in your query, Hibernate will load all the associated domains and network codes of the loaded domains. And it will load the domains and network codes of the additional domains, etc. etc. until all collection loads return empty collections or entities that have already been loaded.

To avoid that, make your collections lazy (as they are by default). Then loading a domain with its operators and its network codes will load just that.

Answer 4

Your EAGER mapping will only be considered automatically by Hibernate if you use the Criteria API for the query.

If you use HQL, you will need to manually add the FETCH keyword to your JOINs to force Hibernate to include the relations in the first query and avoid subsequent queries.

This is Hibernate-specific and may work differently on other ORMs.

See this question/answer for a slightly different angle.

Answer 5

It's not documented that good, but did you try setting the FetchMode? You can do so by either using the Criteria API: domainCriteria.setFetchMode("operators", JOIN) or use @Fetch(JOIN) at the relation definition.

The annotation (and only the annotation as it seems) also allows to set a fetch mode SUBSELECT, which should at least restrain Hibernate to execute 3 queries max. Not knowing your dataset, I assume this should be the way to go for you, as a big fat join over those tables does not seem too healthy. Best to figure it out for yourself, I guess...

Answer 6

Since you have already specified FetchType.EAGER for both networkCodes and operators, whenever you will query domain, hibernate will load both networkCodes and operators. That's the whole idea of EAGER fetching mode

So you could change your query simple to following:

private static final String FIND
    = "SELECT DISTINCT domain"
    + " FROM Domain domain"
    + " WHERE domain.domainId = :domainId";

API details here

Cheers !!

Answer 7

My first observation is that you do not need to write an HQL query containing joins if your mappings say that they must be eagerly loaded.

You can however tell the Hibernate to use fetching strategy as sub select if you don't want to use joins.

Hibernate generates the SQL query for loading the objects during startup based on the specified mappings and caches it. However in your case, you have one to many nested relation with self and arbitrary depth, so looks like it won't be possible for hibernate to decide before hand the sql to correctly eager fetch. So it would need to send multiple joins queries depending upon the depth of the parent Domain you are querying at runtime.

To me it looks like you are thinking that HQL and the resulting SQL/('s) in your case can have one to one correpondence which is not true. With HQL you query for objects and the orm decides how to load that object and its relations (eager/lazy) based on the mappings or you can specify them at runtime too ( for e.g, a lazy association in mapping can be overridden by Query api but not vice versa). You can tell the orm what to load ( my marking eager or lazy ) and how to load eagerly ( either using join / sub select).

UPDATE

When I run the following query on your domain model

SELECT DISTINCT domain FROM Domain domain LEFT OUTER JOIN FETCH domain.operators LEFT OUTER JOIN FETCH domain.networkCodes WHERE domain.domainId = :domainId";

I can see that the networkCode and operator collections are of instance PersistentSet ( this is Hibernate wrapper) and both have initialized property set to be true. Also in the underlying session context I can see the domains with the domain and the operators listed. So what is making you think that they are not eagerly loaded ?

This is how my domain looks like

@Entity
@Table
public class Domain {
    @Id
    @GenericGenerator(name = "generator", strategy = "increment")
    @GeneratedValue(generator = "generator")
    @Column(name = "domain_id")
    private Long domainId;

    @Column(nullable = false, unique = true)   
    private String name;

    @Column(nullable = false)    
    @Enumerated(EnumType.STRING)
    private DomainType type;

    @OneToMany(mappedBy = "domain",cascade = {
            CascadeType.PERSIST,
            CascadeType.MERGE
    }, fetch = FetchType.EAGER)   
    private Set<NetworkCode> networkCodes = new HashSet<NetworkCode>();

    @ManyToMany(mappedBy="parent",fetch = FetchType.EAGER, cascade=CascadeType.ALL)
    private Set<Domain> operators = new HashSet<Domain>();
    // more

    @ManyToOne  
    private Domain parent;

    public String getName() {
        return name;
    }


    public void setName(String name) {
        this.name = name;
    }


public DomainType getType() {
        return type;
    }

    public void setType(DomainType type) {
        this.type = type;
    }


    public Set<Domain> getOperators() {
        return operators;
    }


    public Long getDomainId() {
        return domainId;
    }


    public void setDomainId(Long domainId) {
        this.domainId = domainId;
    }


    public void setOperators(Set<Domain> operators) {
        this.operators = operators;
    }

    public void addDomain(Domain domain){
        getOperators().add(domain);
        domain.setParent(this);
    }


    public Domain getParent() {
        return parent;
    }


    public void setParent(Domain parent) {
        this.parent = parent;
    }

    public void addNetworkCode(NetworkCode netWorkCode){
        getNetworkCodes().add(netWorkCode);
        netWorkCode.setDomain(this);
    }