Hibernate Native SQL Query


Hibernate Native SQL Query

Hibernate Native SQL Query is used to execute database specific SQL queries directly so that performance can be optimized.

We can perform select and non-select SQL operations y using Native SQL Query.

We can execute SQL queries by using  session.createSQLQuery() method.

Syntax

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

Here session.createSQLQuery() method will return object[] array.

If we to retrieve POJO class directly then we have use addEntity(Class) method

SQLQuery qry = session.createSQLQuery("select * from EMPLOYEE").addEntity(Employee.class);

Advantage of Hibernate Native SQL Query

  1. The main advantage of Native SQL Query is to increase the performance of SQL queries.

Disadvantage of Native SQL Query

Native SQL Query of Hibernate Framework makes database dependent.

Native SQL Query Example

  1. Employee table in oracle DB
  2. Employee.java
  3. Employee.hbm.xml
  4. hibernate.cfg.xml
  5. Run.java

Employee table in Oracle DB

Create Employee table using below query.

>create table employee(id number, name varchar2(20), salary number));

Insert some record in employee table

>insert into employee values(101, ‘Vikas’, 10000);

>insert into employee values(102, ‘Vicky’, 15000);

insert into employee values(103, ‘Rahul’, 20000);

>insert into employee values(104, ‘Yogesh’, 25000);

Employee.java

package com.javagf.bean;
public class Employee { 
 private int id;
 private String name;
 private Integer salary;
 public Integer getSalary() {
   return salary; 
 }
 public void setSalary(Integer salary) {
   this.salary = salary; 
 }
 public int getId() {
   return id;
 }
 public void setId(int id) { 
   this.id = id;
 }
 public String getName() {
   return name; 
}
 public void setName(String name) {
   this.name = name;
 }
 @Override public String toString() { return "Employee [id=" + id + ", name=" + name + ", salary=" + salary + "]"; }
}

Employee.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated 18 Jun, 2017 2:16:54 PM by Hibernate Tools 3.5.0.Final -->
<hibernate-mapping>
 <class name="com.javagf.bean.Employee" table="EMPLOYEE">
 <id name="id" type="int"> 
  <column name="ID" /> 
  <generator class="assigned" />
 </id> 
<property name="name" type="java.lang.String">
   <column name="NAME" />
 </property>
 <property name="salary" type="int"> 
   <column name="SALARY" /> 
 </property>
 </class></hibernate-mapping>

hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>
   <property name="hbm2ddl.auto">update</property>
   <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
   <property name="connection.url">jdbc:oracle:thin:@localhost:1521:xe</property>
   <property name="connection.username">system</property> 
   <property name="connection.password">manager</property>
   <property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
  <mapping resource="com/javagf/bean/Employee.hbm.xml" />
 </session-factory>
</hibernate-configuration>

Run.java

package com.javagf.test;
import java.util.Iterator;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import com.javagf.bean.Employee;
public class Run {
 public static void main(String[] args) {
   Configuration cfg = new Configuration();
   cfg.configure("com/javagf/configuration/hibernate.cfg.xml");
   SessionFactory factory = cfg.buildSessionFactory();
   Session session = factory.openSession();
   SQLQuery qry = session.createSQLQuery("select * from EMPLOYEE").addEntity(Employee.class);
   List employees = qry.list();
   for (Iterator iterator = employees.iterator(); iterator.hasNext();) {
     Employee employee = (Employee) iterator.next();
     System.out.println(employee); 
   } 
    session.close();
  }
}

Output

Employee [id=101, name=Vikas, salary=null]
Employee [id=102, name=Vicky, salary=15000]
Employee [id=103, name=Rahul, salary=20000]
Employee [id=104, name=Yogesh, salary=25000]

Related Posts.

  1. Why hibernate is better than JDBC
  2. Hibernate Architecture
  3. Steps to create hibernate application in eclipse
  4. Hibernate Mapping and Configuration file
  5. Hibernate SQL Dialects List
  6. Hibernate Query Language
  7. Hibernate ID generator
  8. insert data into table using hibernate example
  9. update record database using hibernate
  10. Delete record database using hibernate
  11. hibernate select query example
  12. how to get all record from table in hibernate
  13. difference between wrapper and primitive types in hibernate

 

 






Full-stack web and mobile app development company Contact Us : Skype: indore.webcoder Email: info@w3sparks.com Sales: +91 8085506229