Course Module 4. Working with databases - Lecture: Join in HQL

3.1 Mapping dependent entities

In SQL, you can write queries using JOIN. Is it possible to do the same in HQL? The short answer is yes. But the full answer will be more interesting.

First, when we write a JOIN in SQL, it most often means that one table refers to another table. For example, the task table contains an employee_id column that refers to the id column of the employee table.

This dependency can be described using annotations in Hibernate. First, let's just create Entities for our tables. First, let's describe the employee table:

@Entity
@Table(name="employee")
class Employee {
   @Column(name="id")
   public Integer id;

   @Column(name="name")
   public String name;

   @Column(name="salary")
   public Integer salary;

   @Column(name="join_date")
   public Date joinDate;
}

And the EmployeeTask class for the task table :

@Entity
@Table(name="task")
class EmployeeTask {
   @Column(name="id")
   public Integer id;

   @Column(name="name")
   public String name;

   @Column(name="employee_id")
   public Integer employeeId;

   @Column(name="deadline")
   public Date deadline;
}

Everything is fine, but there is one suggestion. Let's look at the employeeId field in the last example:

@Column(name="employee_id")
public Integer employeeId;

Do you notice anything strange? If not, then this means that you have already formed a way of thinking in the SQL language.

The thing is that in the Java language, we usually describe such a dependency a little differently:

public Employee employee;

We don't need to specify an id , we usually just specify a variable that holds a reference to the Employee object . Or stores null if there is no such object.

And Hibernate allows us to describe such a situation using annotations:

@ManyToOne
@JoinColumn(name="employee_id", nullable=true)
public Employee employee;

The annotation @ManyToOnetells Hibernate that many EmployeeTask entities can refer to one Employee entity .

And the annotation @JoinColumnspecifies the name of the column from which the id will be taken . All other necessary information will be taken from the annotations of the Employee class.

The final result will look like this:

@Entity
@Table(name="task")
class EmployeeTask
{
   @Column(name="id")
   public Integer id;

   @Column(name="name")
   public String name;

   @ManyToOne
   @JoinColumn(name="employee_id", nullable=true)
   public Employee employee;

   @Column(name="deadline")
   public Date deadline;
}

3.2 Using join in HQL

And now let's look at how to write queries to related entities in HQL.

First situation.

We have an employee (Employee) and we want to get a list of his tasks. Here's what that query would look like in SQL:

SELECT task.* FROM task JOIN employee ON task.employee_id = employee.id
WHERE employee.name = "Ivan Ivanovich";

And now let's write the same query in HQL:

from EmployeeTask where employee.name = "Ivan Ivanovich"

The EmployeeTask class has an employee field , and it has a name field , so this query will work.

Situation two.

Return a list of employees who have overdue tasks. Here's what that query would look like in SQL:

SELECT DISTINCT employee.*
FROM task JOIN employee ON task.employee_id = employee.id
WHERE task.deadline < CURDATE();

DISTINCTis used because there can be many tasks assigned to one user.

And now let's write the same query in HQL:

select distinct employee from EmployeeTask where deadline < CURDATE();

employee in this query is a field of the EmployeeTask class

Situation three.

Assign all unassigned tasks to the director. The SQL query will look like this:

UPDATE task SET employee_id = 4 WHERE employee_id IS NULL

And now let's write the same query in HQL:

update EmployeeTask set employee = :user where employee is null

The last query is the hardest one. We need to pass the ID, director, but the EmployeeTask class does not contain a field where you can write id, instead it contains an Employee field where you need to assign a reference to an object of type Employee.

In Hibernate, this problem is solved with the help of query parameters that are passed to the Query object. And in HQL itself, such parameters are written through a colon: :user. But we will talk about this a little later.