How to call stored procedure in Hibernate
There are different ways to call stored procedure from hibernate using
- NamedNativeQuery
- NamedQuery
- sql-query in XML file
In this article we are going to see how to call a stored procedure from hibernate using NamedNativeQuery .
Consider we have a stored procedure GetEmployee created in MySQL , which accepts one input parameter and return the row.
|
1 2 3 4 5 6 |
delimiter // CREATE PROCEDURE GetEmployee(emId VARCHAR(20)) BEGIN SELECT * FROM employee WHERE emp_id = emId; END// |
Now lets see how to call this Stored procedure, using NamedNativeQuery.
Employee.java
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
package com.j2eereference.hibernate.storedProcedure; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; import org.hibernate.annotations.NamedNativeQueries; import org.hibernate.annotations.NamedNativeQuery; @NamedNativeQueries({ @NamedNativeQuery( name = "callEmployeeSP", query = "call GetEmployee(:empID)", resultClass = Employee.class ) }) @Entity @Table(name = "employee") public class Employee { private int emp_id; private String emp_name; @Id @GeneratedValue @Column(name = "emp_id") public int getEmp_id() { return emp_id; } public void setEmp_id(int emp_id) { this.emp_id = emp_id; } @Column(name = "emp_name") public String getEmp_name() { return emp_name; } public void setEmp_name(String emp_name) { this.emp_name = emp_name; } } |
Using the below class , we can call the stored procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
package com.j2eereference.hibernate.storedProcedure; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.AnnotationConfiguration; public class SPCallDemo{ public static void main(String[] args) { AnnotationConfiguration config = new AnnotationConfiguration(); config.addAnnotatedClass(Employee.class); SessionFactory sessionFactory = config.configure().buildSessionFactory(); Session session = sessionFactory.openSession(); Query query = session.getNamedQuery("callEmployeeSP"); query.setString("empID", "1"); List employees=query.list(); System.out.println("Employee Name is : "+employees.get(0).getEmp_name()); session.close(); } } |
Output:
Hibernate: CALL GetEmployee(?)
Employee Name is : John
Leave a Reply