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

maggu · Accepted Answer · 2013-04-15 06:36:19Z

7

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();

answered Apr 15, 2013 at 6:36

maggu

1,1999 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alekhya Over a year ago

Do we need to close the callablestatement? Or will session takes care of closing the callable statement.?

Azuu · Accepted Answer · 2013-04-15 06:54:56Z

6

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.

answered Apr 15, 2013 at 6:54

Azuu

8562 gold badges16 silver badges32 bronze badges

2 Comments

Tony Over a year ago

connection() method is deprecated, isn't it?

Andremoniy Over a year ago

@Tony, use session.doWork(...)

Kabilan S · Accepted Answer · 2013-04-20 11:28:35Z

3

I solved it simply by the following code...Just pass the parameters in CSV.Thanks for the help guys..

String queryString = "SlaGrid 245,2012,2013,1,12"

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

query.list();

Works Perfect :)

answered Apr 20, 2013 at 11:28

Kabilan S

1,0944 gold badges16 silver badges31 bronze badges

2 Comments

Metalhead89 Over a year ago

'query.list();' did not work for me in Hibernate 4, instead I used 'query.executeUpdate()'

bcampolo Over a year ago

Does this work independently of database type or is this MySQL specific?

Bhushan Bhangale · Accepted Answer · 2013-04-15 06:39:04Z

0

Try this

Query query = sessionFactory.getCurrentSession().createSQLQuery(
    "CALL SlaGrid(:appID, :fromYear, :toYear, :fromMon, :toMon)")
    .setParameter("appID", 245)
    .setParameter("fromYear", 2012)
    .setParameter("toYear", 2013)
    .setParameter("fromMon", 1)
    .setParameter("toMon", 12);

answered Apr 15, 2013 at 6:39

Bhushan Bhangale

11k5 gold badges46 silver badges72 bronze badges

1 Comment

Kabilan S Over a year ago

i have tried this already ..this syntax is for mysql not mssql

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

2 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related