We can call stored procedure in hibernate in three ways. following are the three approaches to call stored procedure in hibernate. Let´s Start with creating a store procedure in MySQL:
MySQL store procedure:
---------------------------------------------
Here´s a MySQL store procedure, which accept a student id as a parameter and return the related student data.
DELIMITER $$
CREATE PROCEDURE ´GetStudentRec´(int_student_id VARCHAR(20))
BEGIN
SELECT * FROM students WHERE id = int_student_id;
END $$
DELIMITER ;
In MySQL, you can simple call it with a call keyword :
CALL GetStudentRec(´2345´);
Hibernate call store procedure:
------------------------------------------------
1. Using Native SQL ? createSQLQuery:
----------------------------------------------------------------
You can use createSQLQuery() to call a store procedure directly.
Query query = session.createSQLQuery(
"CALL GetStudentRec(:studentId)")
.addEntity(Student.class)
.setParameter("studentId", "2345");
List result = query.list();
for(int i=0; i<result.size(); i++){
Student student = (Student)result.get(i);
System.out.println(student.getStudentId());
}
2. NamedNativeQuery in annotation:
-------------------------------------------------------------
Declare your store procedure inside the @NamedNativeQueries annotation.
//Student.java
...
@NamedNativeQueries({
@NamedNativeQuery(
name = "callStudentStoreProcedure",
query = "CALL GetStudentRec(:studentId)",
resultClass = Student.class
)
})
@Entity
@Table(name = "students")
public class Student implements java.io.Serializable {
...
Call it with getNamedQuery().
Query query = session.getNamedQuery("callStudentStoreProcedure")
.setParameter("studentId", "2345");
List result = query.list();
for(int i=0; i<result.size(); i++){
Student student = (Student)result.get(i);
System.out.println(student.getStudentId());
}
3. sql-query in XML mapping file:
--------------------------------------------------------
Declare your store procedure inside the ?sql-query? tag.
<!-- Student.hbm.xml -->
...
<hibernate-mapping>
<class name="in.anyforum.Student" table="students" ...>
<id name="studentId" type="java.lang.Integer">
<column name="STUDENT_ID" />
<generator class="identity" />
</id>
<property name="studentId" type="string">
<column name="ID" length="10" not-null="true" unique="true" />
</property>
...
</class>
<sql-query name="callStudentStoreProcedure">
<return alias="student" class="in.anyforum.Student"/>
<![CDATA[CALL GetStudentRec(:studentId)]]>
</sql-query>
</hibernate-mapping>
Call it with getNamedQuery().
Query query = session.getNamedQuery("callStudentStoreProcedure")
.setParameter("studentId", "2345");
List result = query.list();
for(int i=0; i<result.size(); i++){
Student student = (Student)result.get(i);
System.out.println(student.getStudentId());
}
