AF
HomeTagSubmit NotesAsk AnythingLoginSubscribe Us
AF
1. Feel Free to ask and submit anything on Anyforum.in and get satisfactory answer
2. Registration is not compulsory, you can directly login via google or facebook
3. Our Experts are looking for yours ?.



hibernate-sql: How to call stored procedures in the hibernate?

Please explain with source code step by step process to call stored procedure in hibernate.

hibernate x 23
sql x 47
Posted On : 2014-08-02 00:01:59.0
profile Garima Gupta - anyforum.in Garima Gupta
596129554551
up-rate
4
down-rate

Answers


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());
}

Posted On : 2014-08-02 00:40:18
Satisfied : 1 Yes  0 No
profile Rishi Kumar - anyforum.in Rishi Kumar
523188245674
Reply This Thread
up-rate
5
down-rate



Post Answer
Please Login First to Post Answer: Login login with facebook - anyforum.in