17 - Native SQL and Named Queries in Hebernate

17.1 Overview

In chapter 10, we discussed Hibernate Query Language (HQL) to interact with the database in database independent way. However there might be some scenarios where we would want to use database specific functions or data types etc. To support such scenarios, Hibernate provides the support for native SQL as well.

For HQL we use createQuery(SQL) method for HQL where as for native SQL we have to use createSQLQuery(SQL) method of session API to get the SQLQuery instance.

Note: Native SQL uses table names not class names in query.

17.2 Native SQL API

a) Auto ResultSet Handing

While using native SQL with hibernate, we need not to map the result set with Entity, instead hibernate does it automatically out of the box for us. To do this, we need to use addEntity(Entity) method on SQL query object.

Hibernate automatically maps the returned values with the object properties based on the configuration done in hbm xml file. For example below query will return the list of Books object.

List result = session.createSQLQuery("select * from Books ").addEntity(Book.class).list();

b) Fetching Scalar Values

If we do not map entity using addEntity() method, query will return the result as scalar values. In such case the result list will be list of Object[] (array of Object) where each field of object array will be scalar type like String, Integer.

In case we are fetching some of the properties in SQL (not using * ) then we need to explicitly tell Hibernate about it using addScalar() method like below

session.createSQLQuery("select b.NAME as name from BOOKS b").addScalar("name");

c) Working with Joins

Suppose a entity has a collection of another entity then to load the contained collection at the same time we can use addJoin().

17.3 Example

Let's consider a relationship of Employee and Job. Employee object will have an instance of Job. Have a look at below mapping.xml

<?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>

    <class name="com.tutorial.hibernate.Employee" table="Employee">
       <id name="id" type="int" column="id">
          <generator class="native" />
       </id>

       <property name="name" column="name" type="string" />
       <property name="emailAddress" column="emailAddress" type="string" />
       <many-to-one class="com.tutorial.hibernate.Job" name="job" column="job_id" unique="true"/>
    </class>

    <class table="Job" name="com.tutorial.hibernate.Job" >
       <id name="id" type="int" column="id">
          <generator class="native" />
       </id>

       <property name="designation" column="designation" type="string"/>
       <property name="salary" column="salary" type="int"/>
       <property name="jobDescription" column="job_description" type="string"/>
    </class> 

</hibernate-mapping>

Existing Sample Data in tables

Employee Table –

Job Table

Case 1- Get All properties of Employee table

Session session = factory.openSession();

SQLQuery query = session.createSQLQuery("select * from Employee ");

List<Object[]> result = query.list();

for(int i=0;i<result.size();i++)
{
    System.out.println("Details of Employee " + (i+1));
    
    Object[] data = result.get(i);
    System.out.println("Employee ID " + data[0]);
    System.out.println("Employee Name " + data[1]);
    System.out.println("Email Address " + data[2]);
    System.out.println("=====================");        
}

Result

Case 2- Get All properties of Employee table as an Entity

Session session = factory.openSession();

SQLQuery query = session.createSQLQuery("select * from Employee ");
query.addEntity(Employee.class);
List<Employee> result = query.list();

for(int i=0;i<result.size();i++)
{
    System.out.println("Details of Employee " + (i+1));

    Employee emp  = result.get(i);
    System.out.println("Employee ID " + emp.getId());
    System.out.println("Employee Name " + emp.getName());
    System.out.println("Email Address " + emp.getEmailAddress());
    System.out.println("=====================");            
}

Result

Case 3- Get Data from multiple tables

Session session = factory.openSession();
SQLQuery query = session.createSQLQuery("select e.id, e.name, e.emailAddress,"
                 + " j.designation,j.salary , j.job_description  from Employee e  , Job j where e.job_id= j.id ");
List<Object[]> result = query.list(); 

for(int i=0;i<result.size();i++)
{
    System.out.println("Details of Employee " + (i+1));

    Object[] data = result.get(i);
    System.out.println("Employee ID " + data[0]);
    System.out.println("Employee Name " + data[1]);
    System.out.println("Email Address " + data[2]);
    System.out.println("Designation " + data[3]);
    System.out.println("Salary " + data[4]);
    System.out.println("Job Description " + data[5]);

    System.out.println("=====================");
}

Result

Case 4- Get Data from multiple tables using Join and as Entity

Returned Result set will be List an array of Object (one for each row ). For each row it will be an array of size 2. One for Employee and one for Job. Setting join is like mapping the alias name with the property name of parent class.

Session session = factory.openSession();

SQLQuery query = session.createSQLQuery("select {emp.*}, {j.*} from Employee emp join Job j ON emp.job_id=j.id");
query.addEntity("emp",Employee.class);
query.addJoin("j","emp.job" );

List<Object[]> result= query.list();

for(int i=0;i<result.size();i++)
{
    System.out.println("Details of Employee " + (i+1));

    Object[] data = result.get(i);

    Employee emp = (Employee)data[0];
    Job job = (Job) data[1];
 
    System.out.println("Employee ID " + emp.getId());
    System.out.println("Employee Name " + emp.getName());
    System.out.println("Email Address " + emp.getEmailAddress());

    System.out.println("Designation " + job.getDesignation());
    System.out.println("Job Description " + job.getJobDescription());
    System.out.println("Salary " + job.getSalary());

          System.out.println("=====================");
}

Result

17.4- Named Queries

Both HQL and native SQL can be externalized and can be defined in mapping file instead of writing it in a code. There are certain advantages of using named queries like

Syntax of the query is checked at the time of building session factory which helps in knowing about the error earlier.
The queries become more like configurations and can be managed without changing the code.
All queries can be kept at a central location.

We need to use getNamedQuery(“Query Name “) API of session.

17.4.1 HQL Named Query

HQL named query can be define with <query> tag. Name attribute has to be used to assign a name to the Query and it has to be unique. Since this is a HQL, queries will written with reference to Java entity . Refer below sample

<query name="getEmployeeDetails">

<![CDATA[from Employee]]>

</query>

Example

Let’s create a new mapping file or add below queries in existing mapping file. Make sure that mapping file has been added in hibernate.cfg.xml file.

<?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 package="com.tutorial.hibernate">

    <query name="GET_ALL_EMPLOYEES" >
          <![CDATA[from Employee]]>
   </query>

   <query name="GET_EMPLOYEE_BY_ID" >
          <![CDATA[from Employee where id = :id]]>
   </query>

</hibernate-mapping>

Create a Test Program to execute the queries

Session session = factory.openSession();

Query query = session.getNamedQuery("GET_ALL_EMPLOYEES");

List<Employee> employees = query.list();

for(int i=0;i<employees.size();i++)
{
    System.out.println("Details of Employee " + (i+1));            
    Employee emp = employees.get(i);

    Job job = emp.getJob();
    System.out.println("Employee ID " + emp.getId());
    System.out.println("Employee Name " + emp.getName());
    System.out.println("Email Address " + emp.getEmailAddress());

    System.out.println("Designation " + job.getDesignation());
    System.out.println("Job Description " + job.getJobDescription());
    System.out.println("Salary " + job.getSalary());
    System.out.println("=====================");
}

query = session.getNamedQuery("GET_EMPLOYEE_BY_ID");
query.setInteger("id", 1);
Employee emp = (Employee)query.uniqueResult();

Job job = emp.getJob();
System.out.println("Employee ID " + emp.getId());
System.out.println("Employee Name " + emp.getName());
System.out.println("Email Address " + emp.getEmailAddress());
System.out.println("Designation " + job.getDesignation());
System.out.println("Job Description " + job.getJobDescription());
System.out.println("Salary " + job.getSalary());

System.out.println("=====================");

17.4.2 Native SQL Named Query

Native SQL named query can be defined with <sql-query> tag. Name attribute has to be used to assign a name to the Query and it has to be unique. Since this is a native SQL, queries will be written with reference to database table. Refer below sample

<sql-query name="getEmployeeDetails">

<![CDATA[select * from Employee]]>

</sql-query>

Example

Let’s create a new mapping file or add below queries in an existing mapping file. Make sure that mapping file has been added in hibernate.cfg.xml file.

<?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 package="com.tutorial.hibernate">

   <sql-query name="SQL_GET_ALL_EMPLOYEES">
        <![CDATA[select * from Employee]]>
   </sql-query>

   <sql-query name="SQL_GET_ALL_EMPLOYEES_JOIN">
        <![CDATA[select {emp.*}, {j.*} from Employee emp join Job j ON emp.job_id=j.id ]]>
        <return alias="emp" class="Employee" />
        <return-join alias="j" property="emp.job"></return-join>
    </sql-query>
   
</hibernate-mapping>

Create a Test Program to execute the queries

Session session = factory.openSession();

Query query = session.getNamedQuery("SQL_GET_ALL_EMPLOYEES_JOIN");

List<Object[]> result= query.list();

for(int i=0;i<result.size();i++)
{
    System.out.println("Details of Employee " + (i+1));

    Object[] data = result.get(i);

    Employee emp = (Employee)data[0];
    Job job = (Job) data[1];

    System.out.println("Employee ID " + emp.getId());
    System.out.println("Employee Name " + emp.getName());
    System.out.println("Email Address " + emp.getEmailAddress());

    System.out.println("Designation " + job.getDesignation());
    System.out.println("Job Description " + job.getJobDescription());
    System.out.println("Salary " + job.getSalary());
    System.out.println("=====================");
}

query = session.getNamedQuery("SQL_GET_ALL_EMPLOYEES");

result = query.list();
for(int i=0;i<result.size();i++)
{
    System.out.println("Details of Employee " + (i+1));
    Object[] data = result.get(i);
    System.out.println("Employee ID " + data[0]);
    System.out.println("Employee Name " + data[1]);
    System.out.println("Email Address " + data[2]);
    System.out.println("=====================");
}

Like us on Facebook

Wideskills

Wideskills

C
H
A
P
T
E
R
S Hibernate Tutorial

O
T
H
E
R

S
U
B
J
E
C
T
S Subjects

17 - Native SQL and Named Queries in Hebernate

17.1 Overview

17.2 Native SQL API

a) Auto ResultSet Handing

b) Fetching Scalar Values

c) Working with Joins

17.3 Example

17.4- Named Queries

17.4.1 HQL Named Query

17.4.2 Native SQL Named Query

Like us on Facebook

Search form

17 - Native SQL and Named Queries in Hebernate

17.1 Overview

17.2 Native SQL API

a) Auto ResultSet Handing

b) Fetching Scalar Values

c) Working with Joins

17.3 Example

17.4- Named Queries

17.4.1 HQL Named Query

17.4.2 Native SQL Named Query

Like us on Facebook