How to call Stored Procedure in Hibernate using Native SQL?

Calling Database stored procedure in Hibernate using Native SQL

Second approach to call DB stored procedure in hibernate

By Calling createSQLQuery() method:-

Steps for creating example to call a stored procedure using Native SQL in Hibernate:-

• At first, create a java project using eclipse and name it as StoredProcCallUsingHibernate.

• Inside src folder, create a package named com.gaurav.hibernate.beans.

• After that, create a java bean file named Student and place the below corresponding code in that file.

• Now, Inside src folder create hibernate.cfg.xml file and place the below corresponding source code.

• Now again in the src folder create Student.hbm.xml file and place the below corresponding source code.

•  Now, in src folder, create a package com.gaurav.hibernate.procedurecall and place the ProcCallByNativeSQL.java client program in that package which is available below.

• Create the Student table and insert the records in the database, then create the corresponding stored procedure which we are going to call using Hibernate

Table Structure and Insert queries are available below:-

/*Table structure for table student */

DROP TABLE IF EXISTS student;

CREATE TABLE student (
  STUDENT_ID decimal(20,0) DEFAULT NULL,
  FIRSTNAME varchar(50) NOT NULL,
  LASTNAME varchar(50) NOT NULL,
  BIRTHDATE date DEFAULT NULL,
  TOTAL_MARKS decimal(20,0) DEFAULT NULL,
  DIVISION varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table student */


insert into student (STUDENT_ID,FIRSTNAME,LASTNAME,BIRTHDATE,TOTAL_MARKS,DIVISION) values ('1','Kumar','Gaurav','1978-05-12','920','FIRST');

insert into student (STUDENT_ID,FIRSTNAME,LASTNAME,BIRTHDATE,TOTAL_MARKS,DIVISION) values ('2','Gaurav','Kumar','1980-11-19','880','FIRST');


insert into student (STUDENT_ID,FIRSTNAME,LASTNAME,BIRTHDATE,TOTAL_MARKS,DIVISION) values ('3','Shivam','Kumar','1989-07-11','670','SECOND');

getStudentDetails() stored procedure. Below is the syntax for MySQL DB

DELIMITER $$

DROP PROCEDURE IF EXISTS test.getStudentDetails $$
CREATE PROCEDURE test.getStudentDetails
   (IN STUD_ID INT)
BEGIN
   SELECT *
   FROM STUDENT
   WHERE STUDENT_ID = STUD_ID;
END $$

DELIMITER ;


Below is the initial table status, after the table creation and record insertion:-

Student.java

package com.gaurav.hibernate.beans;

import java.sql.Date;

public class Student {
    private int studentId;
    private String firstName;
    private String lastName;
    private Date birthDate;
    private int totalMarks;
    private String division;

    /**
     * @return the studentId
     */
    public int getStudentId() {
        return studentId;
    }

    /**
     * @param studentId
     *            the studentId to set
     */
    public void setStudentId(int studentId) {
        this.studentId = studentId;
    }

    /**
     * @return the firstName
     */
    public String getFirstName() {
        return firstName;
    }

    /**
     * @param firstName
     *            the firstName to set
     */
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    /**
     * @return the lastName
     */
    public String getLastName() {
        return lastName;
    }

    /**
     * @param lastName
     *            the lastName to set
     */
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    /**
     * @return the birthDate
     */
    public Date getBirthDate() {
        return birthDate;
    }

    /**
     * @param birthDate
     *            the birthDate to set
     */
    public void setBirthDate(Date birthDate) {
        this.birthDate = birthDate;
    }

    /**
     * @return the totalMarks
     */
    public int getTotalMarks() {
        return totalMarks;
    }

    /**
     * @param totalMarks
     *            the totalMarks to set
     */
    public void setTotalMarks(int totalMarks) {
        this.totalMarks = totalMarks;
    }

    /**
     * @return the division
     */
    public String getDivision() {
        return division;
    }

    /**
     * @param division
     *            the division to set
     */
    public void setDivision(String division) {
        this.division = division;
    }

}

Student.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
    <class name="com.gaurav.hibernate.beans.Student" table="Student">
        <id name="studentId" column="STUDENT_ID" type="int">
            <generator class="assigned" />
        </id>
        <property name="firstName" column="FIRSTNAME" />
        <property name="lastName" column="LASTNAME" />
        <property name="birthDate" column="BIRTHDATE" />
        <property name="totalMarks" column="TOTAL_MARKS" />
        <property name="division" column="DIVISION" />

    </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://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/test</property>
        <property name="hibernate.connection.username">root</property>
        <property name="connection.password">root</property>
        <property name="connection.pool_size">1</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">update</property>
        <mapping resource="Student.hbm.xml" />
    </session-factory>
</hibernate-configuration>

ProcCallByNativeSQL.java

package com.gaurav.hibernate.procedurecall;

import java.util.List;

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

import com.gaurav.hibernate.beans.Student;

public class ProcCallByNativeSQL {
    @SuppressWarnings("rawtypes")
    public static void main(String[] args) {

        Session session = null;

        try {

            SessionFactory sessionFactory = new Configuration().configure()
                    .buildSessionFactory();
            session = sessionFactory.openSession();

            /** This is the use of Native SQL – createSQLQuery, we can directly
             call a stored procedure using Native SQL. */
            Query query = session
                    .createSQLQuery("CALL getStudentDetails(:studId)")
                    .addEntity(Student.class).setParameter("studId", "2");

            System.out.print("Procedure Invocation completed :- ");
            List studentDataInList = query.list();

            for (int i = 0; i < studentDataInList.size(); i++) {
                Student student = (Student) studentDataInList.get(i);
                System.out.println("FIRSTNAME :- " + student.getFirstName());
                System.out.println("LASTNAME :- " + student.getLastName());
                System.out.println("BIRTHDATE :- " + student.getBirthDate());
                System.out.println("TOTAL MARKS :- " + student.getTotalMarks());
                System.out.println("DIVISION :- " + student.getDivision());
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.flush();
            session.close();

        }
    }
}


Below you can find the complete project structure in eclipse:-

Result:-

Required Jars for this application is as below:-

antlr-2.7.6.jar
asm-2.2.2.jar
asm-commons-2.2.2.jar
cglib-nodep-2.1_3.jar
commons-collections.jar
commons-logging.jar
dom4j-1.6.1.jar
ehcache-1.1.jar
hibernate3.jar
jta.jar
log4j-1.2.14.jar
mysql-connector-java-5.0.4-bin.jar