File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
    Bookmark Topic Watch Topic
  • New Topic

implementing stored procedures in hibernate

 
santhoshkumar samala
Ranch Hand
Posts: 156
Eclipse IDE Hibernate Spring
  • 0
  • Mark post as helpful
  • send pies
  • Report post to moderator
hi

can we implement stored procedures using hibernate?
 
Mark Spritzler
ranger
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • 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
 
Ben Ethridge
Ranch Hand
Posts: 108
  • 0
  • Mark post as helpful
  • send pies
  • 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
 
Ching Liang Chin
Greenhorn
Posts: 3
  • 0
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • 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
  • 0
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • 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
 
a ramazany
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • 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
Sheriff
Posts: 17276
6
IntelliJ IDE Mac Spring
  • 0
  • Mark post as helpful
  • send pies
  • Report post to moderator
Please stop resurrecting old threads.

Mark
    Bookmark Topic Watch Topic
  • New Topic