File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
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
Author

Calling stored procedure from hibernate

lrjainjain brotherjain
Greenhorn

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
as
BEGIN
select ssn,firstname from emp;
END

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"/>
</return>
{ ? = call test_proc_hibernate() }
</sql-query>


My DAO is making the following call

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


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

-----------------------
CASE B)
If I change it to

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

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

---------------------
CASE C)
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() }
</sql-query>


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

Please HELP !!
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
a ramazany
Greenhorn

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

</sql-query>
</hibernate-mapping>
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
Greenhorn

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: http://aspose.com/file-tools
 
subject: Calling stored procedure from hibernate