Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

implementing stored procedures in hibernate

 
Ranch Hand
Posts: 156
Hibernate Eclipse IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
hi

can we implement stored procedures using hibernate?
 
ranger
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
 
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator


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
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
Posts: 17347
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
Please stop resurrecting old threads.

Mark
 
Don't get me started about those stupid light bulbs.
    Bookmark Topic Watch Topic
  • New Topic