Hibernate Query Language Inner Join Example and Syntax

Joins are used to combine two or more relations. In SQL we specify a join condition in the ON clause. Suppose we have two tables employee_inner_join_ex and experience_inner_join_ex. The data in these tables is shown below:


mysql> select * from employee_inner_join_ex;
+-------------+---------------+-------------------------+-----------------------
| employee_id | employee_name | employee_hobby          | employee_date_of_birth
+-------------+---------------+-------------------------+----------------------
|           1 | Saurabh       | reading technical books | 14/07
|           2 | Pushpi        | programming             | 22/06
|           3 | Pooja         | sleeping                | 18/08
|           4 | Anu           | Partying                | 23/01
+-------------+---------------+-------------------------+-----------------------
4 rows in set (0.00 sec)

mysql> select * from experience_inner_join_ex;
+---------------+-------------------+-------------+-------------+
| experience_id | organization_name | no_of_years | employee_id |
+---------------+-------------------+-------------+-------------+
|             1 | qwerty org.       |           1 |           1 |
|             2 | abc org.          |           1 |           2 |
|             3 | cde org.          |           2 |           2 |
|             4 | Engg. College     |           2 |           3 |
+---------------+-------------------+-------------+-------------+
4 rows in set (0.00 sec)

An example of SQL query that specify inner join on above tables is given below:

SELECT employee.employee_id, employee.employee_name, employee.employee_hobby,
employee.employee_date_of_birth, experience.organization_name,
experience.no_of_years FROM employee_inner_join_ex employee INNER JOIN
experience_inner_join_ex experience ON employee.employee_id=experience.employee_id;

The above query joins table employee_inner_join_ex and experience_inner_join_ex ON condition employee.employee_id=experience.employee_id

In Hibernate HQL, we donot need to explicitly specify join condition. Only the name of mapped java class association is specified. For ex. If we have an Employee class that have one-to-many association with the Experience class, the join query is specified as:

"from Employee employee JOIN employee.experience"

The above HQL query works as the same way as the SQL query specified above, but is a lot simpler. We just specify the name of mapped java class association(employee.experience here), and hibernate use the mapping document to generate join query. This will be more clear when we specify classes along with mappings later in this tutorial.

The result of above query is not a List but a Object[], On index 1 of this array is Employee instance, and an Experience instance on index 1.

We may also specify restrictions in query to limit results of query.

"from Employee employee JOIN employee.experience experience where
employee.hobby like 'P%' and experience.noOfYears>1"

The above query selects all results of join where hobby of employee starts with ‘P’ and experience is greater than 1 year.