Stored Procedures or functions that return POJO objects using hibernate

I have an application that needs to run some stored procedures that want to return List but I struggled with how to get this working.

To quote from the hibernate docs this is an area that they need to work on. It says:

27.5. Database functions

Warning

This is an area in Hibernate in need of improvement. In terms of portability concerns, this function handling currently works pretty well from HQL; however, it is quite lacking in all other aspects.

This is my eventual solution.

First off a Dao class

	@Repository("df.gas.metOfficeDao")
	public class GasMetOfficeDao extends MetOfficeDao<MetOfficeObservation>{
	}

view raw GasMetOfficeDao hosted with ❤ by GitHub

Here is the interface that the Dao will implement

	public interface MetOfficeDaoInterface<T>
	{
	public List<T> getMetOfficeObservations(String aGSP, DateTime aStartForecastDate, DateTime aEndForecastDate, DateTime aRequestDate, Class<T> aType);
	}

view raw MetOfficeDaoInterface hosted with ❤ by GitHub

Then the generic Dao MetOfficeDao
Note the following:

1. The use of ResultSetHandler from Apache is a basic ORM
2. JDBC parameters start from '1'
3. getSession().connection(); is deprecated

	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.util.ArrayList;
	import java.util.List;
	import org.apache.commons.dbutils.ResultSetHandler;
	import org.apache.commons.dbutils.handlers.BeanListHandler;
	import org.hibernate.Session;
	import org.hibernate.jdbc.Work;
	import org.joda.time.DateTime;
	import org.springframework.orm.hibernate3.HibernateCallback;
	public abstract class MetOfficeDao<T> extends GenericDao<T, Long> implements MetOfficeDaoInterface<T>{
	/**
	* Calls a stored procedure returning a ResultSet that is
	* concerted to a List<T> using org.apache.commons.dbutils.ResultSetHandler as a basic ORM
	*/
	@Override
	public List<T> getMetOfficeObservations(final String aGSP,
	final DateTime aStartForecastDate, final DateTime aEndForecastDate, final DateTime aRequestDate, final Class<T> aType) {
	return getHibernateTemplate().execute(new HibernateCallback<List<T>>()
	{
	List<T> result = new ArrayList<T>();
	public List<T> doInHibernate(Session aSession) {
	//The connection is not directly available using hibernate unl;ess you use a deprecated method
	getSession().doWork(new Work() {
	@Override
	public void execute(Connection conn) throws SQLException {
	// set up call to storedProcedure
	PreparedStatement ps = conn.prepareStatement("exec procGetObservationsForGSPAndDates ?,? ");
	ps.setEscapeProcessing(true);
	ps.setQueryTimeout(500);
	int i = 1; //jdbc params start at 1
	ps.setString(i++, aGSP);
	ps.setDate(i++, new java.sql.Date(aStartForecastDate.toDate().getTime()));
	ResultSetHandler<List<T>> resultSetHandler = new BeanListHandler<T>(aType);
	if(ps.execute()) //returns true when the procedure is done
	{
	ResultSet rs = ps.getResultSet();
	//convert the ResultSet to a List<T> using the ResultSetHandler
	List<T> results = (List<T>) resultSetHandler.handle(rs);
	result = results;
	}
	}
	});
	return result;
	}
	});
	}
	}

view raw MetOfficeObservationDao hosted with ❤ by GitHub

Just for completeness - here is an example stored procedure for SQLServer
Note the line:
SET NOCOUNT ON
This is needed so that the procedure just returns the data and not the number of rows returned

	rop procedure [procGetForecastsForGSPAndDates]
	GO
	create procedure [dbo].[procGetForecastsForGSPAndDates]
	(
	@GSP varchar(4),
	@forecastDate datetime,
	)
	as BEGIN
	SET NOCOUNT ON
	SELECT t1.* from (select DATE_FOR, TIME, GSP, MAX(time_stamp) as time_updated
	from [MY_TABLE]
	where
	time_stamp < @requestDate
	and DATE_FOR >= @forecastDate
	and GSP = @GSP
	group by DATE_FOR, TIME, GSP ) t2
	JOIN [MY_TABLE] t1
	on t2.DATE_FOR = t1.DATE_FOR
	AND t2.TIME = t1.TIME
	and t2.GSP = t1.GSP
	and t2.time_updated = t1.TIME_STAMP
	order by DATE_FOR, TIME, GSP
	END

view raw procGetForecastsForGSPAndDates hosted with ❤ by GitHub