implementing stored procedures in hibernate

[santhoshkumar samala]

hi

can we implement stored procedures using hibernate?

[Mark Spritzler]

Yes you can.

There are a couple of caveats to take in mind.

1. Only the first parameter of a stored procedure can be an OUT parameter.
2. You cannot have more than one OUT parameter and it must be the first parameter.
3. There are two ways to map your Java objects to the Stored Procedure results. One is where you map in an XML file one by one. the other is if the return result is the same structure of a table, then you can use the Java Object that is mapped to that table.

There are plenty of documents at hibernate's website for this.

Good Luck.

Mark

[Ben Ethridge]

There are plenty of documents at hibernate's website for this.

Hi. Can you point me to a java sample that shows how to do this for a stored procedure, as opposed to a function? All the hibernate docs I'm finding show the function way (and I have that running ok), but I don't yet see exactly how to do this using a stored proc, i.e. they explain the mapping file pretty well, but how do you set that first OUT parameter up on the java side?

Ben

[Ching Liang Chin]

I am also facing the same problem and don't know how actually it works. Will very appreciate if anyone can share any sample coding on how to call the stored procedure with the out parameter.

[Mark Spritzler]

Hi Ching.

Actually, there is a search feature that you can use on this forum. In a previous thread about SPs, I posted such code, if you search on it, you should be able to find it. Here is one of them, but it was actually posted after this thread was created, so you must have done some searching to find it.

Good Luck.

Mark

[Ching Liang Chin]

Thank you Mark, the sample code help alot and I am able to call the Oracle 9i stored procedule and function already.
Unfortunately, when I tried to call the stored procedure/function for MYSQL, it always give me the error as below, anyone got any idea what is wrong with the coding.

exception

org.apache.jasper.JasperException: could not execute query
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:370)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)


root cause

org.hibernate.exception.GenericJDBCException: could not execute query
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
org.hibernate.loader.Loader.doList(Loader.java:2148)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
org.hibernate.loader.Loader.list(Loader.java:2024)
org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:118)
org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1684)
org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
mypackage.DepartmentDAO.departmentName(DepartmentDAO.java:40)
org.apache.jsp.index_jsp._jspService(index_jsp.java:65)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)

Below is the mysql stored procedure that I am trying to call

CREATE PROCEDURE `myDepartName`()
BEGIN
SELECT dept_id, dept_name FROM t_department;
END

Below is the setting in the Department.hbm.xml

...
<sql-query name="departmentName_SP" callable="true">
<return alias="dept" class="mypackage.Department">
<return-property name="Id" column="dept_id"/>
<return-property name="strDeptName" column="dept_name"/>
</return>
{ call myDepartName() }
</sql-query>

Below is the function used to call the stored procedure

public List departmentName() {
Session session = HibernateUtil.getSession();
List lstDept;

lstDept = session.getNamedQuery("mypackage.Department.departmentName_SP").list();

return lstDept;
}

Thanks in advanced.

[Mark Spritzler]

Hmm, I haven't used stored procedures in MySql and/or with Hibernate. I suggest trying to find info at www.jboss.org at their wiki. I am sure there is a MySql call to stored procedure tutorial or example there.

Mark

[a ramazany]

also add class tag to your hbm.xml file

<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>

[Mark Spritzler]

Please stop resurrecting old threads.

Mark