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("====================="); }