Hibernate Native SQL Query Example Tutorial
We looked into Hibernate Query Language and Hibernate Criteria in earlier articles, today we will look into Hibernate Native SQL query with examples.
Hibernate SQL Query
Hibernate provides the option to execute native SQL queries through the use of the SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor-specific queries that are not supported by Hibernate API. For example, query hints or the CONNECT keyword in Oracle Database.
For normal scenarios, Hibernate SQL query is not the recommended approach because we lose benefits related to Hibernate association and Hibernate first-level cache. I will use MySQL database and the same tables and data setup as used in the HQL example, so you should check out that first to understand the tables and corresponding model classes mapping.
Example
For Hibernate Native SQL Query, we use Session.createSQLQuery(String query) to create the SQLQuery object and execute it. For example, if you want to read all the records from the Employee table, we can do it through the below 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 execute the above code for the data setup we have, it produces the following output:
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}
Notice that the list() method returns the List of Object array. We need to explicitly parse them to double, long, etc. Our Employee and Address classes have the following toString() method implementations:
@Override
public String toString() {
return "Id= " + id + ", Name= " + name + ", Salary= " + salary
+ ", {Address= " + address + "}";
}
@Override
public String toString() {
return "AddressLine1= " + addressLine1 + ", City=" + city
+ ", Zipcode=" + zipcode;
}
Notice that our query is not returning Address data, whereas if we use the HQL query "from Employee", it returns the associated table data too.
Hibernate SQL Query addScalar
Hibernate uses ResultSetMetadata to deduce the type of the columns returned by the query. From a performance point of view, we can use the addScalar() method to define the data type of the column. However, we would still get the data in the form of an Object array:
// 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);
}
The output generated will be the same, however, we will see a slight performance improvement when the data is huge.
Hibernate SQL Multiple Tables
If we would like to get data from both Employee and Address tables, we can simply write the SQL query for that and parse the result set.
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);
}
For the above code, the output produced will be like 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}
Hibernate Native SQL Entity and Join
We can also use the addEntity() and addJoin() methods to fetch data from associated tables using joins. For example, the above data can also be retrieved as below:
// 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 used to return all properties of an entity. When we use addEntity() and addJoin() with join queries like above it returns both the objects, as shown above. Output produced by above code is like below.
The output produced by the above code is 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}
Address Info::AddressLine1= Albany Dr, City=San Jose, Zipcode=95129
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
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
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 run both the queries in the mysql client and notice that the output produced is same.
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>
Hibernate SQL Query with Parameters
We can also pass parameters to the Hibernate SQL queries, just like JDBC PreparedStatement. The parameters can be set using the name as well as the index, as shown in the below example:
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);
}
Output produced by the above code would be:
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
Id= 2, Name= David, Salary= 200.0, {Address= null}
That’s all for a brief introduction of Hibernate SQL Query. You should avoid using it unless you want to execute any database-specific queries.
