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:
Post a Comment