How to call stored procedure in Hibernate with parameter

Sponsored Links

Ad by Google

In our previous Hibernate post we have seen the example of Second Level Cache. Now here, I am going to show you the example of "How to call parameterized store procedure in Hibernate". Sometimes we required to create store procedure in database and nowadays Hibernate is very popular ORM framework and most of the application using Hibernate so you may need to call the store procedure using Hibernate.

In this post, I am going to show you a very simple example of calling store procedure in Hibernate. Lets see an example, Below is the ER diagram of our bug table, we are using the same table in our this project. You can download the complete source code from the downloadable link.

Here is our store procedure, created in MySql Database.
Syntax to Create Store Procedure in MySql:

DELIMITER $$
USE `hibernate_tutorial`$$
DROP PROCEDURE IF EXISTS `usp_get_bug_details`$$
CREATE  PROCEDURE `usp_get_bug_details`(bg_code VARCHAR(10),frmDate DATE, toDate DATE)
BEGIN
 SELECT * FROM bug WHERE bug_code = bg_code AND CAST(bug_locked_at AS DATE) BETWEEN frmDate AND toDate;
END$$
DELIMITER ;

Tools and Technologies we are using here:

• JDK 7
• Hibernate 4.3.7
• MySql 5.1.10
• Eclipse Juno 4.2
• Maven 3.2

Main Objects of this project are:

• pom.xml
• hibernate.cfg.xml
• annotated pojo
• database

Step 1. Create database script.

CREATE DATABASE `hibernate_tutorial`;
USE `hibernate_tutorial`;
/*Table structure for table `bug` */

DROP TABLE IF EXISTS `bug`;
CREATE TABLE `bug` (
  `bug_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bug_code` varchar(255) DEFAULT NULL,
  `bug_description` varchar(255) DEFAULT NULL,
  `bug_locked_at` datetime DEFAULT NULL,
  `build_release` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`bug_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

/*Store Procedure script */

DELIMITER $$
USE `hibernate_tutorial`$$
DROP PROCEDURE IF EXISTS `usp_get_bug_details`$$
CREATE  PROCEDURE `usp_get_bug_details`(bg_code VARCHAR(10),frmDate DATE, toDate DATE)
BEGIN
SELECT * FROM bug WHERE bug_code = bg_code AND CAST(bug_locked_at AS DATE) BETWEEN frmDate AND toDate;
END$$
DELIMITER ;

Step 2. Now Create Maven Project. If you are very new to maven you can follow any of our previous hibernate tutorial using maven like one-many-association example

How to Call Store Procedure In MySql:

CALL usp_get_bug_details('100','2015-01-101','2015-02-03');

How to call Store Procedure in Hibernate

package com.javamakeuse.poc;

import java.util.Calendar;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;

public class BugTest {
	public static void main(String[] args) {
		Session session = HibernateUtility.getSessionFactory().openSession();
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.MONTH, -3);

		Query query = session
				.createSQLQuery("CALL usp_get_bug_details(:bugCode,:frmDate,:toDate)");
		query.setParameter("bugCode", "100");
		query.setParameter("frmDate", cal.getTime());
		query.setParameter("toDate", Calendar.getInstance().getTime());

		List<Object[]> objList = query.list();

		for (Object row[] : objList) {
			System.out.println("bug_code - " + row[1]);
			System.out.println("bug_description - " + row[2]);
			System.out.println("bug_locked_at - " + row[3]);
		}
	}
}

Above will gives you the below output, I am not posting the whole code of this project, only focusing the main requirement of this project. Although you can download the complete source code of this project from the below given downloadable link.
OUT PUT:

Hibernate: CALL usp_get_bug_details(?,?,?)
bug_code - 100
bug_description - not able to login
bug_locked_at - 2015-01-13 23:49:40.0
bug_code - 100
bug_description - wrong message
bug_locked_at - 2015-02-03 23:50:43.0

Download the complete example from here Source Code

Sponsored Links