Native SQL

In Hibernate applications, by using Session we are able to perform database operations on only single record, but, if we want to perform database operations over multiple records then we have to use HQL, but, HQL is not providing environment for Database dependent native operations.

HQL is is able to provide support for DML[insert, update, delete, select] operations, but, it has not provided environment for DDL [create, alter and drop] queries.

HQL is not supporting stored procedures and functions kind of database dependent native operations.

In Hibernate applications, If we want to perform database operations over multiple records, database dependent native operations like preparing stored procedures, functions and accessing than and to perform DDL operations, etc. Hibernate has provided an alternative for HQL, that is, “Native SQL”.

If we want to use Native SQL in Hibernate applications then we have to use the following steps.

Create SqlQuery object
Execute the SQL query.

1. Create SqlQuery object

SqlQuery is an object provided by Hibernate in the form of org.hibernate. SqlQuery interface and it able to represent a native SQL query. To create SqlQuery object we have to use the following method.

public SqlQuery createSqlQuery(String query)throws HibernateException

E.g

SqlQuery query = session.createSqlQuery("select * from emp1");

2. Execute the SQL query

To execute Sql Query represent by SqlQuery object we have to use the following methods.

public List list()
pubhlic Iterator iterator()
public ScrollableResults scroll()
public Object uniqueResult()
public int executeUpdate()

In Native SQL, there are two types of SQL queries.

Entity SQL Queries
Scalar SQL Queries

1. Entity SQL Query

Entity SQLQueries are database dependent sql queries, it can be used to retrieve the complete records in the form of an Entity. It will include ‘*’ notation to get all columns data in a record in the form of Entity object.

E.g

SqlQuery query = session.createSqlQuery("select * from emp1");

Before executing the query we have to provide entity type to SqlQuery object in order to store results, for this, we have to use the following method.

public void addEntity(Class cl)

E.g

query.addEntity(com.ashok.hibernate.model.Employee.class);

In Native SQL, there are two types of parameters.

Positional Parameters
Named Parameters

Positional parameters are represented in the form of ‘?’ s , we are able to provide more than one positional parameter with in a single SQL query. To set values to the positional parameters we have to use the following method from SqlQuery.

public void setXXX(int paramIndex, XXX value)

Where xxx may be byte, short, int, etc.

Names parameters are represented in the form of ‘:name’ , we are able to provide more than one named parameter in native SQL query. To provide values to the named parameters we have to use the following method.

public void setXXX(String paramName, xxx value)

Where xxx may be byte, short, int, etc.

In a single native SQL query, we are able to provide both positional parameters and named parameters, but, first we must provide all positional parameters after that only we have to provide named parameters, we must not provide any positional parameter after named parameter.

E.g

SQLQuery query = session.createSQLQuery("select * from emp1 where esal>=? and esal<=:max_Sal");
query.setFloat(0, 6000);
query.setFloat("max_Sal", 8000);
query.addEntity(com.ashok.hibernate.model.Employee.class);
List<Employee> list = query.list();

In the above approach, we have declare sql query directly in client application, it is available up to the present client application only, it is not available to other client applications, this approach is called as “Programmatic Approach”.

If we want use the same SQL query in more than one client application then programmatic approach is not suggestible, at each and every client application we have to hard code the query, it is not suggestible, to overcome this problem we have to use “Declarative approach”.

Declarative approach, we will declare native SQL query in mapping file along with a particular logical name and we will get that query from mapping file on the basis of the name, this type of SQL queries are called as Named SQL Queries.

To declare SQL query in mapping file we have to use the following tags in mapping file.

<hibernate-mapping>
   -----
   <sql-query name="--">
      <return class="--">
         ---sql query------
   </sql-query>
</hibernate-mapping>

‘name’ attribute in tag will take logical name to the query. tag will take a POJO class name with ‘class’ attribute in order to get results in the form of
POJO objects.

To get named SQL query from mapping file to hibernate Client Application we have to use the following method.

public Query getNamedQuery(String logicalName)

In Declarative native SQL query we are able to provide both positional parameters and named parameters depending on the requirement.

Note

In Native SQL queries we are unable to use ‘<‘ symbol in mapping file, because, ‘<‘ symbol is treated as starting tag form XML tags, in place of ‘<‘ symbol we have to use ‘<‘ symbol in mapping file.

2. Scalar SQL Queries

It is a native SQL query, it able to retrieve records data from individual columns and it able to generate results in the form of Object[].
E.g

select eno, ename, esal, eaddr from emp1;

To represent scalar SQL queries we will use org.hibernate.SqlQuery, to get SqlQuery object we will use the following method.

public SqlQuery createSqlQuery(String query) throws HibernateException

E.g

SQLQuery query = session.createSQLQuery("select eno, ename, esal, eaddr from emp1");
List<Object[]> list = query.list();

In Scalar SQL Queries we are able to provide both Positional parameters and Named parameters ,but, first we must provide positional parameters after that only we must provide named parameters.

If we provide positional parameters and named parameters in SQL query then we must provide values to these parameters , for this, we must use the following methods.

public void setXXX(int index, xxx value)
public void setXXX(String paramName, xxx value)

E.g

SQLQuery query = session.createSQLQuery("select eno, ename, esal, eaddr from emp1 where esal >= ? and esal <= :maxSal");
query.setFloat(0, 6000);
query.setFloat("maxSal", 15000);
List<Object[]> list = query.list();

Stored Procedures and Functions in Native SQL

In Database related applications, first we will define database logic at JAVA applications and we will transfer that logic to databases in order to execute, If we have any requirement like to execute a particular database logic frequently then it is suggestible to use Stored Procedures and functions in database related applications.

In the above context, define the frequently executed database logic at the database side in the form of Stored Procedures and functions, not at java side and prepare stored procedure call and function call at java application and send that procedure or function call to Database when we want to perform that respective database action.

Note

If we want to execute select SQL query and if we want to represent records of data then we have to use CURSOR type variable, Oracle database has provided a predefined CURSOR in the form of SYS_REFCURSOR to represent the result of a particular SQL Query.

If we want to use Stored Procedures and Functions in Hibernate applications then we have to use the following steps.

Define Stored Procedure or Function at Database side.
Configure the respective Stored procedure/Function call in hibernate mapping file.
In Client Application, create Query object with the procedure call or Function call logical name.
Execute Procedure or Function call.

E.g

Procedure at Database

create or replace PROCEDURE getEmps(emps OUT SYS_REFCURSOR , sal IN float)
AS
BEGIN
open emps for
   select * from emp where esal<sal;
END getEmps;

package com.ashok.hibernate.model;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Employee {
	private String empId;
	private String empName;
	private String address;
	private double salary;
	
	public Employee() {
		super();
	}
	
	public String getEmpId() {
		return empId;
	}

	public void setEmpId(String empId) {
		this.empId = empId;
	}
	
	public String getEmpName() {
		return empName;
	}

	public void setEmpName(String empName) {
		this.empName = empName;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}
}

employee.hbm.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.ashok.hibernate.insert.model.Employee" table="emp">
		<id name="empId" column="emp_id" type="string" />
		<property name="empName" column="emp_name" type="string" />
		<property name="address" column="address" type="string" />
		<property name="salary" column="salary" type="double" />
	</class>
	<sql-query name="getSalProc" callable="true">
	    <return class="com.ashok.hibernate.model.Employee"/>
            {call getEmps(?, :sal)}
    </sql-query>
</hibernate-mapping>

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
   <session-factory>
      <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
      <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
      <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
      <property name="hibernate.connection.username">system</property>
      <property name="hibernate.connection.password">ashok</property>

      <mapping resource="com/ashok/hibernate/employee.hbm.xml"/>
   </session-factory>
</hibernate-configuration>

ClientApp.java

package com.ashok.hibernate.insert;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.ashok.hibernate.insert.model.Employee;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class ClientApp {
	public static void main(String[] args) throws Exception {
		Configuration cfg = new Configuration();
        cfg.configure();
        StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
        builder = builder.applySettings(cfg.getProperties());
        StandardServiceRegistry registry = builder.build();
        SessionFactory sessionFactory = cfg.buildSessionFactory(registry);
        Session session = sessionFactory.openSession();
        Query query = session.getNamedQuery("getSalProc");
        query.setFloat("sal", 10000);
        List<Employee> list = query.list();
        for(Employee e: list) {
            System.out.println("Employee Details");
			System.out.println("---------------------------");
			System.out.println("Employee ID :" + emp.getEmpId());
			System.out.println("Employee Name :" + emp.getEmpName());
			System.out.println("Employee Salary :" + emp.getSalary());
			System.out.println("Employee Address :" + emp.getAddress());
        }
        session.close();
        sessionFactory.close();
	}
}

Example on Store Functions

Function at Database

create or replace FUNCTION getEmployees return SYS_REFCURSOR
AS
employees SYS_REFCURSOR;
BEGIN
open employees for
   select * from emp;
return employees;
END getEmployees;
/

package com.ashok.hibernate.model;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Employee {
	private String empId;
	private String empName;
	private String address;
	private double salary;
	
	public Employee() {
		super();
	}
	
	public String getEmpId() {
		return empId;
	}

	public void setEmpId(String empId) {
		this.empId = empId;
	}
	
	public String getEmpName() {
		return empName;
	}

	public void setEmpName(String empName) {
		this.empName = empName;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public double getSalary() {
		return salary;
	}

	public void setSalary(double salary) {
		this.salary = salary;
	}
}

employee.hbm.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.ashok.hibernate.insert.model.Employee" table="emp">
		<id name="empId" column="emp_id" type="string" />
		<property name="empName" column="emp_name" type="string" />
		<property name="address" column="address" type="string" />
		<property name="salary" column="salary" type="double" />
	</class>
	<sql-query name="getEmployeesFun" callable="true">
	    <return class="com.ashok.hibernate.model.Employee"/>
            {? = call getEmployees}
    </sql-query>
</hibernate-mapping>

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
   <session-factory>
      <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
      <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
      <property name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
      <property name="hibernate.connection.username">system</property>
      <property name="hibernate.connection.password">ashok</property>

      <mapping resource="com/ashok/hibernate/employee.hbm.xml"/>
   </session-factory>
</hibernate-configuration>

ClientApp.java

package com.ashok.hibernate.insert;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

import com.ashok.hibernate.insert.model.Employee;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class ClientApp {
	public static void main(String[] args) throws Exception {
		Configuration cfg = new Configuration();
        cfg.configure();
        StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder();
        builder = builder.applySettings(cfg.getProperties());
        StandardServiceRegistry registry = builder.build();
        SessionFactory sessionFactory = cfg.buildSessionFactory(registry);
        Session session = sessionFactory.openSession();
        Query query = session.getNamedQuery("getEmployeesFun");
        List<Employee> list = query.list();
        for(Employee e: list) {
            System.out.println("Employee Details");
			System.out.println("---------------------------");
			System.out.println("Employee ID :" + emp.getEmpId());
			System.out.println("Employee Name :" + emp.getEmpName());
			System.out.println("Employee Salary :" + emp.getSalary());
			System.out.println("Employee Address :" + emp.getAddress());
        }
        session.close();
        sessionFactory.close();
	}
}

Native SQL