Running Oracle Stored Procedure With “In/Out Parameters” using Hibernate, Spring WebFlow and Maven

On a recent project I needed to run an Oracle stored procedure using hibernate. Just a little background as to what I was using because it played a big role in running the stored procedure:
For Build: Maven 2.2.1
For ORM: Hibernate 3.3.1.GA, hibernate-annotations and hibernate-commons
annotations 3.4.0.GA
org.hibernate
hibernate
3.3.1 GA

org.hibernate
hibernate-commons-annotations
3.4.0.GA

org.hibernate
hibernate-annotations
3.3.0.GA

I’m also using Spring WebFlow 3.2 and JSF 2.2
3.0.4.RELEASE
2.2.0.RC1
DBMS: Oracle SQL Developer

Stored Procedure: The stored procedure uses four In and one out parameter.The out parameter I am getting here is the new Id on which I will update all existing records. That is Stored Proc with some conditions will copy everything from the original Id and create new Output Id and update/delete everything on this newly created Id which is the output of the Stored Proc. Out parameters in Oracle are like passing an argument by reference. You create a variable, pass it in and when the procedure is done, that variable will have the value. For the purposes of this post let’s assume there are 4 in and 1 out parameters in our stored proc like so:
call myStoredProc(’in param 1′,2,3,4..., outParam1);
where outParam1 is a number.

I tried using NamedNativeQuery annotation on my model class and running it through hibernate but that didn’t work and apparently hibernate doesn’t like out parameter stored procedures but instead would like the stored procedure to return or set the first parameter to an out ref cursos. The only way to call our stored procedure is to get to the jdbc connection directly.But I cant set output parameter directly as I am using Hiberante. This is the hectic.......

To do this
1)I am using my hibernate session’s connection (this.getSession().connection )that is marked as deprecated.

2)I found the new 3.3+ hibernate versions have a “doWork” method on the session class. With that in place you could run a CallableStatement to the stored procedure.
So I decided to go with first option.

Here is what you need to do in the DaoImpl class in order to set the parameters and call the store proc using connection object

public Integer validateXXX(Integer selectedXXXOid, UserSystem userSystem, String vEntityCode,String selectedXXXOid)
{
Integer outputValue = 0;
try
{
Connection con = ((Session)getEntityManager().getDeleg())
.connection();
con.setAutoCommit(false);
CallableStatement cs = con
.prepareCall("call XXX_validation(?, ?, ?, ?)");

cs.setInt(1, selectedXXXOid);
cs.setString(2, userSystem.getUserName());
cs.setString(3, vEntityCode);
cs.setInt(4, Integer.parseInt(selectedXXXOid));
cs.registerOutParameter(4, java.sql.Types.INTEGER);
cs.execute();
outputValue = cs.getInt(4);
System.out.println("Value returned by stored procedure--->"+outputValue);

getEntityManager().flush();
getEntityManager().clear();
}
catch (SQLException sqe)
{
log.error("SQLException in XXX: "+sqe);
}
catch (Exception ex)
{
log.error("Exception in XXX: " +ex);
}

return outputValue;
}
}
I have a running example that is working for me but I rewrote the code for this post so the above code may not even compile and you will probably have to change the code to fit your needs but this gives you an idea of how to do it.