Hibernate – native SQL query example

hibernate_logo
Native SQL quey in Hibernate is powerful way of writing your own native sql query, in spite of relationship , Query Criteria, and HQL (Hibernate Query Language). If your relationship is well defined, then you can use HQL else you have to use your own database specific native sql query.

1. Overview of using native SQL query

Hibernate provide you to write your native SQL to execute native by using its own class i.e. SQLQuery. Hibernate SQL Query is very simple to write, when we have to execute different database vendor ( mysql, oracle etc ) specific queries that are not supported by Hibernate API.

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 Employee table, we can do it through below code.

1
2
3
4
5
6
7
8
9
10
11
12
// open session from sessionFactory
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 
// Get all Employees list without condition
Transaction tx = session.beginTransaction();
SQLQuery query = session.createSQLQuery("select * from Employee");
List<Object[]> rows = query.list();
for(Object[] row : rows){
   Employee employee = new Employee();
   employee.setId(Integer.parseInt(String.valueOf(row[0]));
   employee.setName(String.valueOf(row[1]));
}

2. pros and cons of using native SQL query

  • this is most powerful to pull the data when your relationship is not defined well
  • data base specific keyword can be used to pull data.
  • Usually you should use native SQL query in hibernate when relationship between entities not defined in well manner.
  • Avoid to use of native SQL query when you use Hibernate or any ORM
  • why should you avoid because you are leveraging power of ORM tool so they generate efficient query on behalf of yours
  • when you switech from one database to another then you don’t have to do anything if you are not using native SQL query.

3. mapping (.hbm.xml) files and POJO (domain model) classes

File: Employee.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
package com.mysoftkey.hibernate;
 
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
 
public class Employee implements Serializable {
  private int id;
  private String firstName;
  private String lastName;
  private int salary;
  private Date dateOfBirth;
  private BigDecimal basicComponent;
 
 public Employee() {
  }
 
  public Employee(String fname, String lname, int salary, Date dob) {
    this.firstName = fname;
    this.lastName = lname;
    this.salary = salary;
    this.dateOfBirth = dob;
  }
// generate getter/setter method, removed in this example to clean simple and shorter 
}

File:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 
 
<hibernate-mapping auto-import="false" package="com.mysoftkey.hibernate">
   <class name="Employee" table="EMPLOYEE">
      <meta attribute="class-description">
         This class contains the employee detail. 
      </meta>
      <id name="id" type="int" column="id">
         <generator class="increment"/>
      </id>
      <property name="firstName" column="first_name" type="string"/>
      <property name="lastName" column="last_name" type="string"/>
      <property name="salary" column="salary" type="int"/>
      <property name="dateOfBirth" column="date_of_birth" type="date"/>
<!--       <property name="basicComponent" type="big_decimal" formula="salary*12/100"></property> -->
   </class>
 
   <!--  <query name="findEmployeeByFirstNameHql">
       <![CDATA[from Employee e where e.firstName=:firstName]]>
   </query>  -->
 
   <sql-query name="findEmployeeByFirstNameSql">
       <return alias="employee" class="com.mysoftkey.hibernate.Employee"/>
       <![CDATA[select *  from employee_misc e where e.first_name=:firstname]]>
   </sql-query>
 
</hibernate-mapping>

4. Run example of native SQL query

First of all, I run example without native sql query just to save few data in database. so run following codebase.

File: EmployeeClient.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.mysoftkey.misc.client;
 
import java.util.Date;
 
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
 
import com.delhiguru.util.HibernateUtil;
import com.mysoftkey.hibernate.Employee;
 
public class EmployeeClient {
 
  public static void main(String[] args) {
    Employee employee = new Employee("Rakesh", "Mishra", 50000, new Date());
    Employee employee2 = new Employee("Anila", "jha", 10000, new Date());
    Employee employee3 = new Employee("Mansi", "jain", 20000, new Date());
 
    EmployeeClient client = new EmployeeClient();
    int empId1 = client.saveEmployee(employee);
    empId1 = client.saveEmployee(employee2);
    empId1 = client.saveEmployee(employee3);
  }
 
  public int saveEmployee(Employee employee) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction transaction = null;
    int empId = 0;
    try {
      transaction = session.beginTransaction();
      empId = (Integer) session.save(employee);
      transaction.commit();
    } catch (HibernateException e) {
      transaction.rollback();
      e.printStackTrace();
    } finally {
      session.close();
    }
    return empId;
  }
}

example of native sql query as:

File: NativeSQLClient.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
package com.mysoftkey.misc.client;
 
import java.util.Iterator;
import java.util.List;
 
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
 
import com.delhiguru.nativequery.MainClient;
import com.delhiguru.util.HibernateUtil;
import com.mysoftkey.hibernate.Employee;
 
public class NativeSQLClient {
 
  /**
   * before run uncomment Employee and Department hbm.xml in hibernate.cfg.xml
   * 
   * @param args
   */
  public static void main(String[] args) {
 
    NativeSQLClient client = new NativeSQLClient();
    int empid = 1;
 
    // Get Employee by id.
    Employee employee = client.getEmployeeByNativeQuery(empid);
    if (employee != null) {
      System.out.println(" Name: " + employee.getFirstName() + " , Last Name:" + employee.getLastName());
      System.out.print(" ,salary: -" + employee.getSalary());
    }
 
  }
 
  /**
   * Get Employee by empid using native SQL Query
   * 
   * @param empId
   * @return
   */
  public Employee getEmployeeByNativeQuery(int empId) {
    Session session = HibernateUtil.getSessionFactory().openSession();
    Transaction transaction = null;
    Employee employee = null;
    try {
      transaction = session.beginTransaction();
      String sql = "SELECT * FROM employee WHERE id = :employee_id";
      SQLQuery query = session.createSQLQuery(sql);
      query.addEntity(Employee.class);
      query.setParameter("employee_id", empId);
      List employees = query.list();
      if (employees != null && employees.size() > 0) {
        employee = (Employee) employees.get(0);
      }
      transaction.commit();
    } catch (HibernateException e) {
      if (transaction != null) {
        transaction.commit();
      }
      e.printStackTrace();
    } finally {
      session.close();
    }
    return employee;
 
  }
 
}

5. Tables used in native SQL query example

table used in this example as follows.

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS employee (
  id INT(11) NOT NULL AUTO_INCREMENT,
  firstName VARCHAR(50) DEFAULT NULL,
  lastName VARCHAR(50) DEFAULT NULL,
  salary INT DEFAULT NULL,
  dateOfBirth DATE DEFAULT NULL,
  PRIMARY KEY (id)
)

6. multiple tables join in native sql query

1
2
3
query = session.createSQLQuery("select e.id, e.name, e.salary,a.city, a.state, 
	a.zipcode from Employee e, Address a where a.emp_id=e.id");
List<Object[]> rows = query.list();

7. AddScalar in native SQL query

you can use addScalar() method while using native SQL query to say type of column to hibernate framework.

1
2
3
4
5
SQLQuery query = session.createSQLQuery("select id, name, salary from Employee")
	.addScalar("id", new LongType())
	.addScalar("name", new StringType())
	.addScalar("salary", new DoubleType());
List<Object[]> rows = query.list();

8. Output of many to one hibernate relationship

Eclipse console output:

1
2
3
Hibernate: SELECT * FROM employee WHERE id = ?
 Name: Rakesh , Last Name:Mishra
 ,salary: 50000

Your comments are welcome to improve this Hibernate Query Language example in Hibernate. Happy Learning :)

Please follow and like us:
RSS
Follow by Email
Facebook
LinkedIn
Google+

Leave a Reply

Your email address will not be published. Required fields are marked *