Call Stored Procedure Hibernate

Call Stored Procedure Hibernate

Many approaches are there to call stored procedure in Hibernate and finally you get same results with any call. In terms of performance every approaches gives same performance and choice is yours which suits your needs. Only suggestion is: if you are using Hibernate its better to go with ORM approach.

• Create table script:

create table OTC.dbo.Test (
	test_id int identity not null,
	smID varchar (20) null,
	eventDate date null,
	createTime datetime default getdate() null,
	updateTime datetime null,
	primary key (test_id) 
)

• Insert data script:

INSERT INTO OTC.dbo.Test(
   smID
  ,eventDate
  ,updateTime
) VALUES (
   '987456'  -- smID - IN varchar(20)
   ,'20151226' -- eventDate - IN date
   ,'12/26/2015 12:00:00 AM' -- updateTime - IN datetime
)

• Create stored procedure script:

create procedure dbo.TestStoreProcedure @eventDate date
as
SELECT * FROM OTC.dbo.Test where eventDate = @eventDate
go

As you see stored procedure will take one eventDate as parameter to return the data:

• Execute script which can be use directly to call store procedure using any database utility:

execute OTC.dbo.TestStoreProcedure @eventDate = '12/26/2015 12:00:00 AM'
go
--OR
execute OTC.dbo.TestStoreProcedure @eventDate = '20151226' 
go

Hibernate to call stored procedure: I will show you five different approach to call stored procedure:

• By using Native SQL query:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataByNativeQuery(String eventDate) {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createSQLQuery(
			"{CALL OTC.dbo.TestStoreProcedure(:eventDate)}").addEntity(TestModelForProcedure.class);
	query.setParameter("eventDate", eventDate);
	List<T> dividendPayDate = query.list();
	return dividendPayDate;
}

• By using NamedNativeQuery: In your java model class please add @NamedNativeQuery annotation as below:

@NamedNativeQueries({
	@NamedNativeQuery(
	name = "callStoredProcedure",
	query = "{CALL OTC.dbo.TestStoreProcedure(:eventDate)}",
	resultClass = TestModelForProcedure.class
	)
})
@Entity
@Table(name = "OTC.dbo.TestStoreProcedure")
public class TestModelForProcedure {

//model attributes with getter and setters...

}

• Java Dao call:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataByNamedNativeQuery(String eventDate) {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.getNamedQuery("callStoredProcedure");
		query.setParameter("eventDate", eventDate);
		List<T> dividendPayDate = query.list();
		return dividendPayDate;			
}

• By using pure SQL query:

/* (non-Javadoc)
 * @see com.javahonk.dao.StoreProcedureDao#getTestTableDataByProcedureExecute(java.lang.String)
 * If you want to pass multiple parameter please use as below:
 * "SET CHAINED OFF execute OTC.dbo.TestStoreProcedure :eventDate, :eventDate2, :eventDate3"
 */
@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getDataByDirectPureQuery(String eventDate) {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createSQLQuery(
			"execute OTC.dbo.TestStoreProcedure :eventDate")
			.addEntity(TestModelForProcedure.class);
	query.setParameter("eventDate", eventDate);
	List<T> dividendPayDate = query.list();
	return dividendPayDate;
}

• By using scaler with types: If you are using Scaler then you don’t need to map your java model with hibernate any annotation and it should be just plain Java POJO class with getter and setter method shown as below for Test table:

import java.util.Date;

public class TestModelWithoutColumnMapping {
	
	private int test_id;	
	private String smID;	
	private java.sql.Date eventDate;	
	private Date createTime;
	private Date updateTime;
	
	//Getters and Setters...
	
}

• Java Dao call:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getTestTableDataByUsingScalerTypes(String eventDate) {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createSQLQuery(
			"execute OTC.dbo.TestStoreProcedure :eventDate")
			.addScalar("test_id",StandardBasicTypes.INTEGER)
			.addScalar("smID", StandardBasicTypes.STRING)
			.addScalar("eventDate", StandardBasicTypes.DATE)
			.addScalar("createTime", StandardBasicTypes.DATE)
			.addScalar("updateTime", StandardBasicTypes.DATE)
			.setResultTransformer(Transformers.aliasToBean(TestModelWithoutColumnMapping.class));
	query.setParameter("eventDate", eventDate);
	List<T> dividendPayDate = query.list();
	return dividendPayDate;
}

• By using scaler without types: Is same as above but here you don’t need to use any hard coded types as above and your Java POJO class should match variable types with database column types:

@Override
@SuppressWarnings("unchecked")
@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
public List<T> getTestTableDataByUsingScalerWithoutTypes(String eventDate) {
	
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createSQLQuery(
			"execute OTC.dbo.TestStoreProcedure :eventDate")
			.addScalar("test_id")
			.addScalar("smID")
			.addScalar("eventDate")
			.addScalar("createTime")
			.addScalar("updateTime")
			.setResultTransformer(Transformers.aliasToBean(TestModelWithoutColumnMapping.class));
	query.setParameter("eventDate", eventDate);
	List<T> dividendPayDate = query.list();
	return dividendPayDate;
}

That’s it. For reference I am including full maven project for download complete model and Dao class below:

• TestModelForProcedure.java:

package com.javahonk.model;

import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

@NamedNativeQueries({
	@NamedNativeQuery(
	name = "callStoredProcedure",
	query = "{CALL OTC.dbo.TestStoreProcedure(:eventDate)}",
	resultClass = TestModelForProcedure.class
	)
})
@Entity
@Table(name = "OTC.dbo.TestStoreProcedure")
public class TestModelForProcedure {
	
	@Id
	@Column(name="test_id")
	private String test_id;
	
	@Column(name="smID")
	private String smID;
	
	@Column(name="eventDate")
	private java.sql.Date eventDate;
	
	@Column(name="createTime")
	private Date createTime;
	
	@Column(name="updateTime")
	@Temporal(TemporalType.TIMESTAMP)
	private Date updateTime;

	public String getTest_id() {
		return test_id;
	}

	public void setTest_id(String test_id) {
		this.test_id = test_id;
	}

	public String getSmID() {
		return smID;
	}

	public void setSmID(String smID) {
		this.smID = smID;
	}

	public java.sql.Date getEventDate() {
		return eventDate;
	}

	public void setEventDate(java.sql.Date eventDate) {
		this.eventDate = eventDate;
	}

	public Date getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}

	public Date getUpdateTime() {
		return updateTime;
	}

	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}

	@Override
	public String toString() {
		return "EquitySwapTestModelForProcedure [test_id=" + test_id
				+ ", smID=" + smID + ", eventDate=" + eventDate
				+ ", createTime=" + createTime + ", updateTime=" + updateTime
				+ "]";
	}

	
	
}

• TestModelWithoutColumnMapping.java:

package com.javahonk.model;

import java.util.Date;

public class TestModelWithoutColumnMapping {
	
	private int test_id;	
	private String smID;	
	private java.sql.Date eventDate;	
	private Date createTime;
	private Date updateTime;
	public int getTest_id() {
		return test_id;
	}
	public void setTest_id(int test_id) {
		this.test_id = test_id;
	}
	public String getSmID() {
		return smID;
	}
	public void setSmID(String smID) {
		this.smID = smID;
	}
	public java.sql.Date getEventDate() {
		return eventDate;
	}
	public void setEventDate(java.sql.Date eventDate) {
		this.eventDate = eventDate;
	}
	public Date getCreateTime() {
		return createTime;
	}
	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
	public Date getUpdateTime() {
		return updateTime;
	}
	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}
	@Override
	public String toString() {
		return "TestModelWithoutColumnMapping [test_id=" + test_id + ", smID="
				+ smID + ", eventDate=" + eventDate + ", createTime="
				+ createTime + ", updateTime=" + updateTime + "]";
	}

	
	
}

• StoredProcedureDaoHandler.java:

package com.javahonk.dao;

import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.javahonk.model.TestModelForProcedure;
import com.javahonk.model.TestModelWithoutColumnMapping;

@Repository
@Transactional
public class StoredProcedureDaoHandler<T> implements StoredProcedureDao<T>{
	
	@Autowired
	private SessionFactory sessionFactory;
	
	@Override
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<T> getDataByNativeQuery(String eventDate) {
		
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery(
				"{CALL OTC.dbo.TestStoreProcedure(:eventDate)}").addEntity(TestModelForProcedure.class);
		query.setParameter("eventDate", eventDate);
		List<T> dividendPayDate = query.list();
		return dividendPayDate;
	}
	
	@Override
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<T> getDataByNamedNativeQuery(String eventDate) {
		
		Session session = sessionFactory.getCurrentSession();
		Query query = session.getNamedQuery("callStoredProcedure");
			query.setParameter("eventDate", eventDate);
			List<T> dividendPayDate = query.list();
			return dividendPayDate;			
	}
	
	
	/* (non-Javadoc)
	 * @see com.javahonk.dao.StoreProcedureDao#getTestTableDataByProcedureExecute(java.lang.String)
	 * If you want to pass multiple parameter please use as below:
	 * "SET CHAINED OFF execute OTC.dbo.TestStoreProcedure :eventDate, :eventDate2, :eventDate3"
	 */
	@Override
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<T> getDataByDirectPureQuery(String eventDate) {
		
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery(
				"execute OTC.dbo.TestStoreProcedure :eventDate")
				.addEntity(TestModelForProcedure.class);
		query.setParameter("eventDate", eventDate);
		List<T> dividendPayDate = query.list();
		return dividendPayDate;
	}
	
	@Override
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<T> getTestTableDataByUsingScalerTypes(String eventDate) {
		
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery(
				"execute OTC.dbo.TestStoreProcedure :eventDate")
				.addScalar("test_id",StandardBasicTypes.INTEGER)
				.addScalar("smID", StandardBasicTypes.STRING)
				.addScalar("eventDate", StandardBasicTypes.DATE)
				.addScalar("createTime", StandardBasicTypes.DATE)
				.addScalar("updateTime", StandardBasicTypes.DATE)
				.setResultTransformer(Transformers.aliasToBean(TestModelWithoutColumnMapping.class));
		query.setParameter("eventDate", eventDate);
		List<T> dividendPayDate = query.list();
		return dividendPayDate;
	}

	@Override
	@SuppressWarnings("unchecked")
	@Transactional(readOnly = true, propagation=Propagation.NOT_SUPPORTED)
	public List<T> getTestTableDataByUsingScalerWithoutTypes(String eventDate) {
		
		Session session = sessionFactory.getCurrentSession();
		Query query = session.createSQLQuery(
				"execute OTC.dbo.TestStoreProcedure :eventDate")
				.addScalar("test_id")
				.addScalar("smID")
				.addScalar("eventDate")
				.addScalar("createTime")
				.addScalar("updateTime")
				.setResultTransformer(Transformers.aliasToBean(TestModelWithoutColumnMapping.class));
		query.setParameter("eventDate", eventDate);
		List<T> dividendPayDate = query.list();
		return dividendPayDate;
	}
	

}

 Download Project: HibernateStoredProcedureCall

Reference:

• Hibernate Getting Started Guide