Course Module 4. Working with databases - Lecture: JOIN FETCH

Description of the problem

As we said above, the LazyCollectionOption.EXTRA annotation has a problem - it performs a separate request to the database for each object. We need to somehow explain to Hibernate that we want it to immediately load all the child objects for our parent objects.

The developers of Hibernate have come up with a solution to this problem, the join fetch operator in HQL.

HQL query example:

select distinct task from Task t left join fetch t.employee order by t.deadline

In this query, everything is simple and complex at the same time. Let's try to construct it piece by piece.

Option 1

We want to download all objectstask, sorted by deadline. Here's what that request would look like:

select task from Task t order by t.deadline

While everything is clear. But the fieldemployeeof the Task class will contain a collection of Employees annotated with the EXTRA annotation . And the objects of this collection will not be loaded.

Option 2

Force Hibernate to load child objects for an objecttask.

select task from Task t join fetch t.employee order by t.deadline

With help, we explicitly bind the Task and Employee entities in our query. Hibernate already knows this because we use the @ManyToMany annotations on these fields.

But we need a join statement to complete it with a fetch statement to get a join fetch . This is how we tell Hibernate that the objects in the Task.employee collections need to be loaded from the database when our request is executed.

Option 3

The previous solution has a few bugs. First, after using join, SQL will not return objects to ustask, which have no objects associated with them in the Employee table. This is exactly how inner join works .

So we need to augment our join with a left operator and turn it into a left join . Example:

select task from Task t left join fetch t.employee order by t.deadline

Option 4

But that's not all. If in your code the relationship between entities is many-to-may, then there will be duplicates in the query results. The same objecttaskcan be found on different employees (Employee objects).

So you need to add the distinct keyword after the select word to get rid of the duplicate Task object.

select distinct task from Task t left join fetch t.employee order by t.deadline

This is how in 4 steps we came to the request with which we started. Well, the Java code will look quite expected:

String hql = " select distinct task from Task t left join fetch t.employee order by t.deadline";
Query<Task> query = session.createQuery( hql, Task.class);
return query.list();

JOIN FETCH Limitations

Nobody is perfect. JOIN FETCH statement too. It has quite a few limitations. And the first one is using the setMaxResults() and setFirstResult() methods .

For the JOIN FETCH statement, our Hibernate will generate a very complex query in which we combine three tables into one: employee, task and employee_task. In fact, this is not a request for employees or tasks, but for all known employee-task pairs.

And SQL can apply its LIMIT and OFFSET statements to exactly that query of employee-task pairs. At the same time, it clearly follows from the HQL query that we want to get exactly tasks (Task), and if we redistribute our FirstResult and MaxResult parameters, then they should refer specifically to Task objects.

If you write code like this:

String hql = " select distinct task from Task t left join fetch t.employee order by t.deadline";
Query<Task> query = session.createQuery( hql, Task.class);
       	    query.setFirstResult(0);
        	   query.setMaxResults(1);
return query.list();

Then Hibernate will not be able to correctly convert FirstResult and MaxResult to the OFFSET and LIMIT parameters of the SQL query.

Instead, it will do three things:

SQL query will select generally all data from the table and return it to Hibernate
Hibernate will select the necessary records in its memory and return them to you
Hibernate will issue a warning

The warning will be something like this:

WARN [org.hibernate.hql.internal.ast.QueryTranslatorImpl] HHH000104: 
firstResult/maxResults specified with collection fetch; applying in memory!