jQuery in Action, 3rd edition
The moose likes Object Relational Mapping and the fly likes Executing stored procedures in hibernate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Reply locked New topic

Executing stored procedures in hibernate

Srivalli KVR

Joined: Mar 13, 2006
Posts: 7
how to execute stored procedure in Hdao class?
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

As a mapped query. See this.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Rick Alsopp

Joined: Apr 21, 2006
Posts: 3
Mr Bartender,

I have recently started looking at Hibernate for our next project, and so far have managed to get up and running with little fuss. However, I have been chasing around the web trying to find any concrete examples of configuring Hibernate for use with stored procedures ... The link you provided, and much of the information I found does not provide decent examples of the DAO calls and, specifically, the Hibernate mapping files setup. I am currently using MyQSL moving to DB2 UDB soon, have fully working Struts, POJO, Hibernate CRUD demo but can't find example for the mappings ...

Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

Well, there are two main points. 1. Creating a named query, 2. using the named query, setting the parameters and running the call. Now for mapping, if your return is a Reference cursor, and is the first parameter, it can be a cursor that is just like a representation of a table, and the DTO you create that is mapped to that table can be the object(s) returned. So there woulnd't be a need for extra mapping.

I am going to post actual code from our application that called a stored procedure.


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

Joined: Feb 05, 2001
Posts: 17276

First I am going to post the Named Query configuration

So that maps a Stored procedure called OC_PAYMENT_METHOD_SEL and the return type to map to the PaymentMaster DTO Java class that we have that actually maps to the PaymentMaster table in our database.

Now here is our Java code to call this stored procedure.

I'd post the DTO, but there isn't anything different about it from other DTOs that map to database tables.

I hope that helps.

Sridhar Ayyalaraju

Joined: Mar 08, 2005
Posts: 1
The code is extremly useful .But, would you tell me where to put the stored procedure? should it be placed in the hbm file itself or should we put it as a file in some folder and can be called from hbm.

where this 'call' finds the procedure
{ call OC_PAYMENT_METHOD_SEL(?, aymentTypeCode, :callingAPI) }

thanks in advance.
Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

But, would you tell me where to put the stored procedure?

There is only one place you can put the stored procedure: in the database.
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

Originally posted by Paul Sturrock:

There is only one place you can put the stored procedure: in the database.

That's what I first thought of too. But he means where does the xml configuration for making it a named query goes. We have aseperate xml file for all the named queries. I think in the hibernate config you point to this other configuration file. All should be included in your har file or jar file where you keep all your mappings.

Subramanyam Goli

Joined: May 22, 2006
Posts: 4
Iam doing the same thing but iam getting error

Hibernate: { call book_return( ?,?) }
org.hibernate.exception.SQLGrammarException: could not execute query

my procedure is

CREATE proc book_return
@firstid int ,
@second varchar OUTPUT
select @second=bookname from book where id=@firstid

my mapping file is
<sql-query name="book_testing" callable="true">
<return alias="book" class="Book">
<return-property name="lngBookId" column="id" />
<return-property name="strBookName" column="bookname" />
{ call book_return( ?,:lngBookId) }

my program is

List ls = session.getNamedQuery("book_testing")
Will Way

Joined: May 31, 2006
Posts: 5
I have specified the mapping in hbm file. But, Its giving a very annoying exception. Please Help.

Also, Please let me know about any of the link containing docs or examples for procedure calling with Hibernet.

My Code Details:-

<sql-query name="testCallableQuery" callable="true">
<return alias="proc" class="vo.GtJobs"/>
{ call erp.GetJob(?) }

List dataList = ss.getNamedQuery("testCallableQuery").list();
Iterator itr = dataList.iterator();


19:25:47,578 INFO [SessionFactoryImpl] Checking 0 named queries
19:25:47,687 INFO [STDOUT] Hibernate: { call erp.GetJob(?) }
19:25:47,859 WARN [JDBCExceptionReporter] SQL Error: 17006, SQLState: null
19:25:47,859 ERROR [JDBCExceptionReporter] Invalid column name
19:25:47,859 INFO [STDOUT] I am in JSP
19:25:47,875 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at org.apache.jsp.index_jsp._jspService(org.apache.jsp.index_jsp:95)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:5971)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1527)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1482)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 30 more

[ May 31, 2006: Message edited by: Will Way ]
Ching Liang Chin

Joined: Jun 13, 2006
Posts: 3
Hi Will Way,
You must include the <return-property name="Id" column="dept_id"/> in between the <return> tag

For Example
<sql-query name="testCallableQuery" callable="true">
<return alias="proc" class="vo.GtJobs">
<return-property name="Id" column="dept_id"/>
<return-property name="name" column="dept_name"/>

{ call erp.GetJob(?) }

Hope it helps.
Rajshen Shenoy

Joined: Jun 21, 2006
Posts: 1
I am trying to do an insert in a table with just 2 columns, for testing purposes. I am new to hibernate.
The following is the mapping:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
<hibernate-mapping package="com.scm.myapp.entity" default-access="field">
<class name="CurrSrcID" table="MyCurRefSourceId" >

<id name="CurID" column="CurID">
<generator class="assigned"/>
<property name="RefSourceID">
<column name="RefSourceID" length="30"/>
<sql-insert callable="true">{call insCurRefSourceId(?,?)}</sql-insert>

I get the following error mesg:

~~~ DATE : 06/21/2006 18:48:36:748 HttpProcessor[8080][0]>>Committing database transaction.

Hibernate: {call insCurRefSourceId(?,?)}

~~~ DATE : 06/21/2006 18:48:36:779 HttpProcessor[8080][0]>>Inside the translateorg.hibernate.exception.GenericJDBCException: could not insert: [com.scm.myapp.entity.CurrSrcID]


~~~ DATE : 06/21/2006 18:48:36:982 HttpProcessor[8080][0]>>Trying to rollback database transaction.

Do I need to include the call to the procedure anywhere else. I am not sure what I am missing here. Would appreciate any help.

Thanks in advance.

a ramazany

Joined: Apr 08, 2008
Posts: 4
you should add also class tag to your hbm.xml file ,like this

<class name="aip.law.orm.hntest.SpHntest">
<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="aip.law.orm.hntest.SpHntest">
<return-property name="id" column="ID"/>
<return-property name="caption" column="Caption"/>
{call sphntest()}

Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

"a ramazany"

This is a two year old thread.

I agree. Here's the link: http://aspose.com/file-tools
subject: Executing stored procedures in hibernate
It's not a secret anymore!