Calling stored procedure in Hibernate using named query in mapping file
First approach to call DB stored procedure in hibernate
In hibernate we have few ways to call a database stored procedure. Which I am going to explain one after another.
Using Named Query in hibernate mapping file
Steps for creating example to call a stored procedure using 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 ProcCallByNamedNativeQueryFromXML.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:-
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>
<!-- This entry is mandatory for call a procedure using named query with mapping file -->
<sql-query name="call_GetStudentDetails_procedure_From_XML"> <return
alias="Student" class="com.gaurav.hibernate.beans.Student" /> <![CDATA[CALL
getStudentDetails(:studId)]]> </sql-query>
</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>
ProcCallByNamedNativeQueryFromXML.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 ProcCallByNamedNativeQueryFromXML {
private static Session session = null;
@SuppressWarnings("unchecked")
public static List<Student> retrieveStudent(int stud_id) {
/**
* This is the use of Named Native SQL Query calling from XML mapping file –
* using getNamedQuery() function, This is a way to call a stored procedure
* using Hibernate.
*/
Query query = session.getNamedQuery(
"call_GetStudentDetails_procedure_From_XML").setParameter(
"studId", stud_id);
List<Student> studentList = query.list();
return studentList;
}
public static void main(String[] args) {
try {
SessionFactory sessionFactory = new Configuration().configure()
.buildSessionFactory();
session = sessionFactory.openSession();
System.out.print("Procedure Invocation completed :- ");
List<Student> studentDataList = retrieveStudent(3);
if (studentDataList != null) {
for (Student studentBean : studentDataList) {
System.out.println("FIRSTNAME :- "
+ studentBean.getFirstName());
System.out.println("LASTNAME :- "
+ studentBean.getLastName());
System.out.println("BIRTHDATE :- "
+ studentBean.getBirthDate());
System.out.println("TOTAL MARKS :- "
+ studentBean.getTotalMarks());
System.out.println("DIVISION :- "
+ studentBean.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
No comments:
Post a Comment