Calling stored procedure in Hibernate using Annotation
Third approach to call DB stored procedure in hibernate
By Calling getNamedQuery() method with the use of Annotation:-
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 StudentAnnotatedBean 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, in src folder, create a package com.gaurav.hibernate.procedurecall and place the ProcCallByNamedNativeQuery.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
Note: If we are using annotations then in this case hibernate mapping file is not required.
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:-
StudentAnnotatedBean.java
package com.gaurav.hibernate.beans;
import java.sql.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;
@Entity
@Table(name="STUDENT")
@NamedNativeQuery(
name = "call_GetStudentDetails_procedure",
query = "CALL getStudentDetails(:studId)",
resultClass = StudentAnnotatedBean.class
)
public class StudentAnnotatedBean implements java.io.Serializable{
/**
*
*/
private static final long serialVersionUID = 5282962375876751766L;
private int studentId;
private String firstName;
private String lastName;
private Date birthDate;
private int totalMarks;
private String division;
/**
* @return the studentId
*/
@Id
@Column(name="STUDENT_ID")
public int getStudentId() {
return studentId;
}
/**
* @param studentId
* the studentId to set
*/
public void setStudentId(int studentId) {
this.studentId = studentId;
}
/**
* @return the firstName
*/
@Column(name="FIRSTNAME")
public String getFirstName() {
return firstName;
}
/**
* @param firstName
* the firstName to set
*/
public void setFirstName(String firstName) {
this.firstName = firstName;
}
/**
* @return the lastName
*/
@Column(name="LASTNAME")
public String getLastName() {
return lastName;
}
/**
* @param lastName
* the lastName to set
*/
public void setLastName(String lastName) {
this.lastName = lastName;
}
/**
* @return the birthDate
*/
@Column(name="BIRTHDATE")
public Date getBirthDate() {
return birthDate;
}
/**
* @param birthDate
* the birthDate to set
*/
public void setBirthDate(Date birthDate) {
this.birthDate = birthDate;
}
/**
* @return the totalMarks
*/
@Column(name="TOTAL_MARKS")
public int getTotalMarks() {
return totalMarks;
}
/**
* @param totalMarks
* the totalMarks to set
*/
public void setTotalMarks(int totalMarks) {
this.totalMarks = totalMarks;
}
/**
* @return the division
*/
@Column(name="DIVISION")
public String getDivision() {
return division;
}
/**
* @param division
* the division to set
*/
public void setDivision(String division) {
this.division = division;
}
}
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 class="com.gaurav.hibernate.beans.StudentAnnotatedBean"/> -->
</session-factory>
</hibernate-configuration>
ProcCallByNamedNativeQuery.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.AnnotationConfiguration;
import com.gaurav.hibernate.beans.StudentAnnotatedBean;
public class ProcCallByNamedNativeQuery {
private static Session session = null;
@SuppressWarnings("unchecked")
public static List<StudentAnnotatedBean> retrieveStudentAnnotatedBean(
int stud_id) {
/**
* This is the use of Named Native SQL Query – getNamedQuery, another
* way to call a stored procedure using Native SQL.
*/
Query query = session.getNamedQuery("call_GetStudentDetails_procedure")
.setParameter("studId", stud_id);
List<StudentAnnotatedBean> studentAnnotatedBeanList = query.list();
return studentAnnotatedBeanList;
}
public static void main(String[] args) {
try {
/**
* If we are using Annotated classes then we have two way to get SessionFactory object
First Approach Using Annotation:-
1. We can create sessionFactory object like below :-
SessionFactory sessionFactory = new AnnotationConfiguration().configure() .buildSessionFactory();
2. We have to add below entry in hibernate configuration file for annotated class mapping
<mapping class="package_name.annotated_classname"/>
Second approach Using Annotation:-
1. We need to create an instance of AnnotationConfiguration class like below :-
AnnotationConfiguration annotationConfiguration = new AnnotationConfiguration();
2. We have to call addAnnotatedClass() method on AnnotationConfiguration object and we need to pass
the annotated class name.
3. Then using AnnotationConfiguration class object we need to call buildSessionFactory()
for getting SessionFactory object.
annotationConfiguration.configure().buildSessionFactory();
Below I am using second approach. First approach i have commented and even commented entry is also available in configuration file in order to follow first approach.
*/
/*
* SessionFactory sessionFactory = new
* AnnotationConfiguration().configure().buildSessionFactory();
*/
AnnotationConfiguration annotationConfiguration = new AnnotationConfiguration();
annotationConfiguration
.addAnnotatedClass(StudentAnnotatedBean.class);
SessionFactory sessionFactory = annotationConfiguration.configure()
.buildSessionFactory();
session = sessionFactory.openSession();
System.out.print("Procedure Invocation completed :- ");
List<StudentAnnotatedBean> studentDataInList = retrieveStudentAnnotatedBean(2);
if (studentDataInList != null) {
for (StudentAnnotatedBean studentAnnotatedBean : studentDataInList) {
System.out.println("FIRSTNAME :- "
+ studentAnnotatedBean.getFirstName());
System.out.println("LASTNAME :- "
+ studentAnnotatedBean.getLastName());
System.out.println("BIRTHDATE :- "
+ studentAnnotatedBean.getBirthDate());
System.out.println("TOTAL MARKS :- "
+ studentAnnotatedBean.getTotalMarks());
System.out.println("DIVISION :- "
+ studentAnnotatedBean.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
ejb3-persistence.jar
hibernate-annotations.jar
hibernate-commons-annotations.jar
hibernate3.jar
jta.jar
log4j-1.2.14.jar
mysql-connector-java-5.0.4-bin.jar
java.sql.SQLException: Cannot perform fetch on a PLSQL statement: next. Getting this error when i calling SP from Hibernate
ReplyDelete