How to execute a sql stored procedure from Java Hibernate using Session Factory?

Question

I am unable to call my stored procedure from java hibernate using session factory

I have written a sql procedure which takes 5 parameters and return a result set which works fine in MS SQL studio

EXEC SlaGrid @appID=245,@fromYear=2012,@toYear=2013,@fromMon=1,@toMon=12   --- sql

EXEC SlaGrid @applID=:applID,@fromYear=:fromYear,@toYear=:toYear,@fromMon=:fromMon,@toMon=:toMon  --hibernate

i am setting the parameters for the above query

String queryString = "EXEC SlaGrid @applID=:applID,@fromYear=:fromYear,@toYear=:toYear,@fromMon=:fromMon,@toMon=:toMon"

Query query = sessionFactory.getCurrentSession().createSQLQuery(queryString);

//set query parameters here

query.list()    --- giving sql grammer exception

Answer 1

You can use callable statement on hibernate session.

Connection con = getSessionFactory().getCurrentSession().connection();  

/**
* Amend to include your parameters and proc
*/          
CallableStatement cs = con.prepareCall( "{ call myprocedure }");

cs.execute();

Answer 2

create a SessionFactory and Open a session then

CallableStatement callableStatement = session.connection().prepareCall("call GetMarketDataCDS(?,?)");
callableStatement.setString(1,"JPM");
callableStatement.registerOutParameter(1, OracleTypes.CURSOR);
callableStatement.execute();
ResultSet resultSet=(ResultSet) callableStatement.getObject(1);

here i am using oracle and my first param is IN Parameter and second is OUT which is nothing but a resultset returning multiple rows. Then in last line we get the ResultSet with all row and then you can iterate through the rows.