Example of a Hibernate SQL Query in its native form.

2 years ago

Liam

8 minutes

In this tutorial, we will be exploring the Hibernate Native SQL Query with examples. Previously, we discussed Hibernate Query Language and Hibernate Criteria, but now it’s time to delve into Hibernate Native SQL queries.

SQL Query for Hibernate

Hibernate offers the ability to run native SQL queries using the SQLQuery object. This feature proves useful when executing database-specific queries that are not supported by Hibernate API, such as query hints or the CONNECT keyword in Oracle Database. However, it is not recommended to use Hibernate SQL queries in typical scenarios because it forfeits the advantages of Hibernate association and the first level cache. To better understand the tables and their corresponding model classes mapping, please refer to the HQL example which utilizes the MySQL database setup.

An illustration showcasing how to utilize Hibernate Native SQL.

To utilize Hibernate Native SQL Query, we employ Session.createSQLQuery(String query) to produce the SQLQuery object and execute it. As an illustration, if the aim is to retrieve all the entries from the Employee table, it can be accomplished using the subsequent code.

// Prep work
SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
Session session = sessionFactory.getCurrentSession();

// Get All Employees
Transaction tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee");
List<Object[]> rows = query.list();
for(Object[] row : rows){
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

When we run the code using the given data setup, it generates the output stated below.

Hibernate: select emp_id, emp_name, emp_salary from Employee
Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
Id= 2, Name= David, Salary= 200.0, {Address= null}
Id= 3, Name= Lisa, Salary= 300.0, {Address= null}
Id= 4, Name= Jack, Salary= 400.0, {Address= null}

Observe that the list() method produces a List of Object array. We must explicitly convert them to double, long, etc. The toString() methods of our Employee and Address classes are implemented as follows.

@Override
public String toString() {
	return "Id= " + id + ", Name= " + name + ", Salary= " + salary
			+ ", {Address= " + address + "}";
}

@Override
public String toString() {
	return "AddressLine1= " + addressLine1 + ", City=" + city
			+ ", Zipcode=" + zipcode;
}

Please observe that our query does not retrieve any Address information, whereas if we utilize the HQL query “from Employee”, it also retrieves the data from the related table.

The “addScalar” method in Hibernate SQL Query

From an efficiency standpoint, Hibernate employs ResultSetMetadata to infer the column types returned by the query. To specify the data type of the column, we can make use of the addScalar() method. Nonetheless, the data will still be received as an array of Object.

//Get All Employees - addScalar example
query = session.createSQLQuery("select emp_id, emp_name, emp_salary from Employee")
		.addScalar("emp_id", new LongType())
		.addScalar("emp_name", new StringType())
		.addScalar("emp_salary", new DoubleType());
rows = query.list();
for(Object[] row : rows){
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

When the data is large, we can expect a slight enhancement in performance, even though the end result will remain unchanged.

Multiple Tables with Hibernate Native SQL

If we want to retrieve information from both the Employee and Address tables, we can easily create an SQL query to accomplish this and then parse the resulting set of data.

query = session.createSQLQuery("select e.emp_id, emp_name, emp_salary,address_line1, city, 
	zipcode from Employee e, Address a where a.emp_id=e.emp_id");
rows = query.list();
for(Object[] row : rows){
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	Address address = new Address();
	address.setAddressLine1(row[3].toString());
	address.setCity(row[4].toString());
	address.setZipcode(row[5].toString());
	emp.setAddress(address);
	System.out.println(emp);
}

The output generated for the given code will be as shown below.

Hibernate: select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id
Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}

Using Hibernate’s native SQL entity and join feature.

We can retrieve the data from an associated table using table joins by utilizing the addEntity() and addJoin() methods. For instance, we can also obtain the aforementioned data in the following manner.

//Join example with addEntity and addJoin
query = session.createSQLQuery("select {e.*}, {a.*} from Employee e join Address a ON e.emp_id=a.emp_id")
		.addEntity("e",Employee.class)
		.addJoin("a","e.address");
rows = query.list();
for (Object[] row : rows) {
    for(Object obj : row) {
    	System.out.print(obj + "::");
    }
    System.out.println("\n");
}
//Above join returns both Employee and Address Objects in the array
for (Object[] row : rows) {
	Employee e = (Employee) row[0];
	System.out.println("Employee Info::"+e);
	Address a = (Address) row[1];
	System.out.println("Address Info::"+a);
}

{[aliasname].*} is utilized to retrieve all attributes of an entity. When we incorporate addEntity() and addJoin() in join queries as demonstrated, it returns both the objects. The output generated by the code mentioned above appears as follows.

Hibernate: select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id
Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129::

Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051::

Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100::

Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}::AddressLine1= City Centre, City=New Delhi, Zipcode=100100::

Employee Info::Id= 1, Name= Pankaj, Salary= 100.0, {Address= AddressLine1= Albany Dr, City=San Jose, Zipcode=95129}
Address Info::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129
Employee Info::Id= 2, Name= David, Salary= 200.0, {Address= AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051}
Address Info::AddressLine1= Arques Ave, City=Santa Clara, Zipcode=95051
Employee Info::Id= 3, Name= Lisa, Salary= 300.0, {Address= AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100}
Address Info::AddressLine1= BTM 1st Stage, City=Bangalore, Zipcode=560100
Employee Info::Id= 4, Name= Jack, Salary= 400.0, {Address= AddressLine1= City Centre, City=New Delhi, Zipcode=100100}
Address Info::AddressLine1= City Centre, City=New Delhi, Zipcode=100100

You can execute both queries in the mysql client and observe that the resulting output is identical.

mysql> select e.emp_id as emp_id1_1_0_, e.emp_name as emp_name2_1_0_, e.emp_salary as emp_sala3_1_0_, a.emp_id as emp_id1_0_1_, a.address_line1 as address_2_0_1_, a.city as city3_0_1_, a.zipcode as zipcode4_0_1_ from Employee e join Address a ON e.emp_id=a.emp_id;
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
| emp_id1_1_0_ | emp_name2_1_0_ | emp_sala3_1_0_ | emp_id1_0_1_ | address_2_0_1_ | city3_0_1_  | zipcode4_0_1_ |
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
|            1 | Pankaj         |            100 |            1 | Albany Dr      | San Jose    | 95129         |
|            2 | David          |            200 |            2 | Arques Ave     | Santa Clara | 95051         |
|            3 | Lisa           |            300 |            3 | BTM 1st Stage  | Bangalore   | 560100        |
|            4 | Jack           |            400 |            4 | City Centre    | New Delhi   | 100100        |
+--------------+----------------+----------------+--------------+----------------+-------------+---------------+
4 rows in set (0.00 sec)

mysql> select e.emp_id, emp_name, emp_salary,address_line1, city, zipcode from Employee e, Address a where a.emp_id=e.emp_id;
+--------+----------+------------+---------------+-------------+---------+
| emp_id | emp_name | emp_salary | address_line1 | city        | zipcode |
+--------+----------+------------+---------------+-------------+---------+
|      1 | Pankaj   |        100 | Albany Dr     | San Jose    | 95129   |
|      2 | David    |        200 | Arques Ave    | Santa Clara | 95051   |
|      3 | Lisa     |        300 | BTM 1st Stage | Bangalore   | 560100  |
|      4 | Jack     |        400 | City Centre   | New Delhi   | 100100  |
+--------+----------+------------+---------------+-------------+---------+
4 rows in set (0.00 sec)

mysql>

Native SQL Query in Hibernate with Parameters.

Similar to JDBC PreparedStatement, Hibernate SQL queries also allow us to pass parameters. We can set these parameters using either the name or the index, as demonstrated in the example below.

query = session
		.createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = ?");
List<Object[]> empData = query.setLong(0, 1L).list();
for (Object[] row : empData) {
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

query = session
		.createSQLQuery("select emp_id, emp_name, emp_salary from Employee where emp_id = :id");
empData = query.setLong("id", 2L).list();
for (Object[] row : empData) {
	Employee emp = new Employee();
	emp.setId(Long.parseLong(row[0].toString()));
	emp.setName(row[1].toString());
	emp.setSalary(Double.parseDouble(row[2].toString()));
	System.out.println(emp);
}

The code above will generate the output.

Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
Id= 1, Name= Pankaj, Salary= 100.0, {Address= null}
Hibernate: select emp_id, emp_name, emp_salary from Employee where emp_id = ?
Id= 2, Name= David, Salary= 200.0, {Address= null}

You should refrain from using Hibernate SQL Query unless you have a requirement to run queries specific to a particular database. That wraps up the brief introduction of Hibernate SQL Query.