It's not a secret anymore!
The moose likes Object Relational Mapping and the fly likes Calling stored procedure from hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Calling stored procedure from hibernate" Watch "Calling stored procedure from hibernate" New topic

Calling stored procedure from hibernate

lrjainjain brotherjain

Joined: Aug 16, 2007
Posts: 1
I am trying to call a stored procedure from hibernate but I keep getting errors. Here's what I am doing..

My table
table emp
ssn varchar(9),
firstname varchar(10)

my Employee class is

public class Employee
public String ssn ;
public String firstname ;
...getter and setter methods.

My test procedure in SQL SERVER is

create procedure test_proc_hibernate
select ssn,firstname from emp;

My hibernate mapping file the following entry

<sql-query name="testingProc" callable="true">
<return alias="empexample" class="Employee">
<return-property name="ssn" column="ssn"/>
<return-property name="firstname" column="firstname"/>
{ ? = call test_proc_hibernate() }

My DAO is making the following call

session = openSession() ;
List mylist = session.getNamedQuery("testingProc").list();

I get the following error
org.hibernate.HibernateException: Errors in named queries: testingProc

If I change it to

<sql-query name="testingProc" callable="true">
{ ? = call test_proc_hibernate() }

I get
org.hibernate.exception.SQLGrammarException: could not execute query

Only this one works. But it returns a list of object type java.lang.Object.

<sql-query name="testingProc" callable="true">
{ call test_proc_hibernate() }

What am I doing wrong, How can I seperate the ssn, firstname from the returned list in case (C)

Please HELP !!
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

There are rules to calling Stored Procedures. They are

1. Only one return value allowed
2. It must be the first out value.
3. The out value must be a reference cursor.

This last one is the difference between what you are seeing in version C. Since you do not have an out parameter Hibernate will have no idea what type of object to create.


Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
a ramazany

Joined: Apr 08, 2008
Posts: 4
you should define also a class tag in your hbm.xml file like this :
<class name="">
<id name="id" type="java.lang.Integer">
<column name="ID" />
<property name="caption" type="java.lang.String">
<column name="Caption" length="60" not-null="true" />
<loader query-ref="testingProc" />
<sql-query name="testingProc" callable="true" >
<return alias="SpHntest" class="">
<return-property name="id" column="ID"/>
<return-property name="caption" column="Caption"/>
{call sphntest()}

Ingoba Ningthoujam
Ranch Hand

Joined: Dec 04, 2006
Posts: 90
Hi brotherjain ,

Write a POJO as well as the corresponding mapping file having fields which are returning from the store procedure.
John Grath

Joined: Sep 18, 2007
Posts: 15
On previous projects I have created a Spring managed JDBC service to execute stored procedures and run more complicated bespoke SQL. I find that this approach works best on larger projects.
I agree. Here's the link:
subject: Calling stored procedure from hibernate
It's not a secret anymore!