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

implementing stored procedures in hibernate

santhoshkumar samala
Ranch Hand

Joined: Nov 12, 2003
Posts: 156

hi

can we implement stored procedures using hibernate?


santhosh<br />SCJP,SCWCD
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Ben Ethridge
Ranch Hand

Joined: Jul 28, 2003
Posts: 108

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
Greenhorn

Joined: Jun 13, 2006
Posts: 3
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
Greenhorn

Joined: Jun 13, 2006
Posts: 3
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

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
Greenhorn

Joined: Apr 08, 2008
Posts: 4
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
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17250
    
    6

Please stop resurrecting old threads.

Mark
 
GeeCON Prague 2014
 
subject: implementing stored procedures in hibernate