Hibernate native SQL

Native SQL refers to the real SQL for used database. Hibernate provides the facility to use native SQL. We can create a native SQL by createSQLQuery() method of Session interface.

Syntax of Hibernate native SQL which returns an object array:

Query query = session.createSQLQuery(“nativeSQL”);

Syntax of Hibernate native SQL which returns an entity:

Query query = session.createSQLQuery(“nativeSQL”).addEntity(entityName.class);

Example:

Student.java
/**
* This class represents a persistent class for Student.
* @author javawithease
*/

public class Student {
//data members
private int studentId;
private String firstName;
private String lastName;
private String className;
private String rollNo;
private int age;
 
//no-argument constructor
public Student(){
 
}
 
//getter and setter methods
public int getStudentId() {
return studentId;
}
public void setStudentId(int studentId) {
this.studentId = studentId;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getRollNo() {
return rollNo;
}
public void setRollNo(String rollNo) {
this.rollNo = rollNo;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
 
}
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
 
<hibernate-configuration>
 
<session-factory>
<property name="dialect">
org.hibernate.dialect.OracleDialect
</property>
<property name="connection.url">
jdbc:oracle:thin:@localhost:1521:XE
</property>
<property name="connection.username">
system
</property>
<property name="connection.password">
oracle
</property>
<property name="connection.driver_class">
oracle.jdbc.driver.OracleDriver
</property>
<property name="hbm2ddl.auto">
update
</property>
<property name="show_sql">
true
</property>
 
<mapping resource="student.hbm.xml"/>
 
</session-factory>
 
</hibernate-configuration>
student.hbm.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping SYSTEM
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>
 
<class name="com.javawithease.business.Student" table="Student">
<id name="studentId" type="int" column="Student_Id">
<generator class="native"></generator>
</id>
 
<property name="firstName" column="First_Name" type="string"/>
<property name="lastName" column="Last_Name" type="string"/>
<property name="className" column="Class" type="string"/>
<property name="rollNo" column="RollNo" type="string"/>
<property name="age" column="Age" type="int"/>
 
</class>
 
</hibernate-mapping>
HibernateUtil.java
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
 
/**
* This is a utility class for getting the hibernate session object.
* @author javawithease
*/

public class HibernateUtil {
private static final SessionFactory sessionFactory =
buildSessionFactory();
 
private static SessionFactory buildSessionFactory() {
SessionFactory sessionFactory = null;
try {
//Create the configuration object.
Configuration configuration = new Configuration();
//Initialize the configuration object
//with the configuration file data
configuration.configure("hibernate.cfg.xml");
// Get the SessionFactory object from configuration.
sessionFactory = configuration.buildSessionFactory();
}
catch (Exception e) {
e.printStackTrace();
}
return sessionFactory;
}
 
public static SessionFactory getSessionFactory() {
return sessionFactory;
}
 
}
HibernateTest.java
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import com.javawithease.persistence.HibernateUtil;
 
/**
* This class is used for the hibernate operations.
* @author javawithease
*/

public class HibernateTest {
public static void main(String args[]){
//Create the student object.
Student student = new Student();
 
//Setting the object properties.
student.setFirstName("Sunil");
student.setLastName("Kunar");
student.setClassName("MCA final");
student.setRollNo("MCA/07/15");
student.setAge(27);
 
//Get the session object.
Session session =
HibernateUtil.getSessionFactory().openSession();
 
//Start hibernate transaction.
session.beginTransaction();
 
//Persist the student object.
session.save(student);
 
//Commit hibernate transaction.
session.getTransaction().commit();
 
//select student records using native query
Query query1 = session.createSQLQuery(
"select * from Student where rollNo = :rollNo");
query1.setString("rollNo", "MCA/07/15");
 
List studentList1 = query1.list();
 
//select student records using native query
//with addEntity attribute
Query query2 = session.createSQLQuery(
"select * from Student where rollNo = :rollNo")
.addEntity(Student.class);
query2.setString("rollNo", "MCA/07/15");
 
List<Student> studentList2 = query2.list();
 
for(Student stu : studentList2){
System.out.println("First Name: "
+ stu.getFirstName());
System.out.println("Last Name: "
+ stu.getLastName());
System.out.println("Class: "
+ stu.getClassName());
System.out.println("RollNo: "
+ stu.getRollNo());
System.out.println("Age: " + stu.getAge());
}
 
//Close hibernate transaction.
session.close();
 
}
}

Output:

Hibernate: select hibernate_sequence.nextval from dual
Hibernate: insert into Student (First_Name, Last_Name, Class,
RollNo, Age, Student_Id) values (?, ?, ?, ?, ?, ?)
Hibernate: select student0_.Student_Id as Student1_0_,
student0_.First_Name as First2_0_, student0_.Last_Name as
Last3_0_, student0_.Class as Class0_, student0_.RollNo as
RollNo0_, student0_.Age as Age0_ from Student
student0_ where student0_.RollNo=?
First Name: Sunil
Last Name: Kunar
Class: MCA final
RollNo: MCA/07/15
Age: 27

No comments: