Stored procedure with or without Hibernate ?

[RockerRocker]

Hi,

I need to call many Stored Procedures in the application (mostly get data procedures). The application uses Spring, Hibernate (with annotations), JSF and Oracle. Could someone let me know the best way to access stored procedures.

1. Using Spring w/o using Hibernate (even though application has it)
2. Using Spring and Hibernate (using Hibernate to access stored procedures)

I would like to know Pros/Cons of above approach

Next,I have seen some examples using procedure access with Hibernate but most of them were simple single table single POJO example. Can someone please share example of framework for the real application scenario - I have one procedure with cursor output having 10 columns from 5 different tables + I have 10 such procedures.

Rocker

[RockerRocker]

[Durden]

Well, you can use JdbcTemplate to query your procedures with standard "call procedure (?, ?)" syntax.

Hibernate also supports stored procedures with procedure mapping, see http://docs.jboss.org/hibernate/stab...ngle/#sp_query

If your DAO-logic consists mostly of procedure calls, I would recommend to use jdbcTemplate.

[RockerRocker]

Thanks, but as mentioned in my post, i need some framework code for both options

[upenderc]

I approached the following way. If it is useful you can use it.

package com.demo.storedprocedure
import java.util.Map;
public class CallStoredProcedure {

private static Map<String,ProcedureDetails> procedures;

public static ProcedureDetails getProcedureByName(String procedureName){
ProcedureDetails storedProcedure = procedures.get(procedureName);
return storedProcedure;
}

public Map<String, ProcedureDetails> getProcedures() {
return procedures;
}

public void setProcedures(Map<String, ProcedureDetails> procedures) {
this.procedures = procedures;
}


}

-----------------------------------------------------------------------
package com.demo.storedprocedure
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class ProcedureDetails extends StoredProcedure{

private List<SqlParameter> parameters;

public List<SqlParameter> getParameters() {
return parameters;
}
public void setParameters( List<SqlParameter> parameters) {
/*if (parameters.size()>0){
this.parameters.clear();
}*/
this.parameters = parameters;
}
@PostConstruct
public void setParameters() {
for (SqlParameter sqlParam : parameters) {
super.declareParameter(sqlParam);
}
compile();
}
public Map<String,Object> executeProc(Map inParams){
Map<String,Object> outParams = execute(inParams);
return outParams;
}

}
----------------------------------------------------------------------
package com.demo.storedprocedure

public class ProcedureDAOImpl implements ProcedureDAOManager {

private static final String UID = "uid";
private static final String IDT_SUBMISSION = "idtSubmission";
@Override
public Long callspInsertEmployee(Integer numSubmission, String uid) {
ProcedureDetails details = CallStoredProcedure.getProcedureByName("spInsertEm ployee");
Map<String,Object> inParams = new HashMap<String,Object>(2);
inParams.put("numSubmission", numSubmission);
inParams.put(UID, uid);
Map<String,Object> outParams = details.executeProc(inParams);
Long idtTxSubmission = null;
if (outParams!=null){
Integer id = (Integer)outParams.get("PO_NUM_TXN_SUBMISSION");
idtTxSubmission = id.longValue();
}
return idtTxSubmission;
}

@Override
public void callSpRefreshWksht(Integer submissionId, String uid) {
ProcedureDetails details = CallStoredProcedure.getProcedureByName("spRefreshW orksheet");
Map<String,Object> inParams = new HashMap<String,Object>(2);
inParams.put(IDT_SUBMISSION, submissionId);
inParams.put(UID, uid);
details.executeProc(inParams);

}
}
-----------------------------------------------------
<bean id="storedProc" class="com.demo.storedprocedure.CallStoredProcedur e">
<property name="procedures">
<map>
<entry key="spInsertEmployee">
<ref bean="spInsertEmployee" />
</entry>
<entry key="spRefreshWorksheet">
<ref bean="refreshWorsheetProc" />
</entry>
</map>
</property>
</bean>

<!-- PROCEDURE NAME AND PARAMETER DECLARATIONS -->

<bean id="refreshWorsheetProc" class="com.demo.storedprocedure.ProcedureDetails">
<property name="sql" value="SP_REFRESH_EMPLOYEE" />
<property name="dataSource" ref="dataSource" />
<property name="parameters">
<list>
<!-- IN PARAMETERS -->
<bean parent="inInteger">
<constructor-arg index="0"><value>idtSubmission</value></constructor-arg>
</bean>
<bean parent="inVarchar">
<constructor-arg index="0"><value>uid</value></constructor-arg>
</bean>

</list>
</property>
</bean>


<bean id="spInsertEmployee"
class="com.demo.storedprocedure.ProcedureDetails">
<property name="sql" value="SP_INSERT_EMPLOYEE" />
<property name="dataSource" ref="dataSource" />
<property name="parameters">
<list>
<!-- IN PARAMETERS -->
<bean parent="inInteger">
<constructor-arg index="0">
<value>idtSubmission</value>
</constructor-arg>
</bean>
<bean parent="inVarchar">
<constructor-arg index="0">
<value>uid</value>
</constructor-arg>
</bean>
<!-- OUT PARAMETERS -->
<bean parent="outInteger">
<constructor-arg index="0"><value>PO_NUM_TXN_EMPLOYEE_ID</value></constructor-arg>
</bean>
</list>
</property>
</bean>

<!-- INTEGER IN PARAMETER FOR PROCEDURE -->
<bean id="inInteger" class="org.springframework.jdbc.core.SqlParameter" abstract="true">
<constructor-arg index="1">
<bean class="org.springframework.beans.factory.config.Fi eldRetrievingFactoryBean">
<property name="staticField"><value>java.sql.Types.INTEGER</value></property>
</bean>
</constructor-arg>
</bean>
<!-- VARCHAR IN PARAMETER FOR PROCEDURE -->
<bean id="inVarchar" class="org.springframework.jdbc.core.SqlParameter" abstract="true">
<constructor-arg index="1">
<bean class="org.springframework.beans.factory.config.Fi eldRetrievingFactoryBean">
<property name="staticField"><value>java.sql.Types.VARCHAR</value></property>
</bean>
</constructor-arg>
</bean>
<!-- VARCHAR OUT PARAMETER FOR PROCEDURE -->
<bean id="outVarchar" class="org.springframework.jdbc.core.SqlOutParamet er" abstract="true">
<constructor-arg index="1">
<bean class="org.springframework.beans.factory.config.Fi eldRetrievingFactoryBean">
<property name="staticField"><value>java.sql.Types.VARCHAR</value></property>
</bean>
</constructor-arg>
</bean>
<!-- NUMBER OUT PARAMETER FOR PROCEDURE -->
<bean id="outInteger" class="org.springframework.jdbc.core.SqlOutParamet er" abstract="true">
<constructor-arg index="1">
<bean class="org.springframework.beans.factory.config.Fi eldRetrievingFactoryBean">
<property name="staticField"><value>java.sql.Types.INTEGER</value></property>
</bean>
</constructor-arg>
</bean>
<!-- DOUBLE OUT PARAMETER FOR PROCEDURE -->
<bean id="outDouble" class="org.springframework.jdbc.core.SqlOutParamet er" abstract="true">
<constructor-arg index="1">
<bean class="org.springframework.beans.factory.config.Fi eldRetrievingFactoryBean">
<property name="staticField"><value>java.sql.Types.DOUBLE</value></property>
</bean>
</constructor-arg>
</bean>
<!-- CURSOR OUT PARAMETER FOR PROCEDURE -->
<bean id="outCursor" class="org.springframework.jdbc.core.SqlOutParamet er" abstract="true">
<constructor-arg index="1">
<bean class="org.springframework.beans.factory.config.Fi eldRetrievingFactoryBean">
<property name="staticField"><value>oracle.jdbc.OracleTypes. CURSOR</value></property>
</bean>
</constructor-arg>
</bean>
</beans>
----------------------------------------------

[RockerRocker]

Thanks upenderc. This helps, more since i was unable to google a 'solid' single example of the procedure framework.

RockerRocker

[kjkurien]

This is a better approach to call Oracle PL/SQL package/procedure/stored procedure using Spring BlazeDS stack. It should work with just Spring as well.

import javax.sql.DataSource;

import oracle.jdbc.driver.OracleTypes;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class KJDebugStoredProcedure extends StoredProcedure {

private static final String SPROC_NAME = "kjdebug.testsp";

public KJDebugStoredProcedure(DataSource dataSource) {
super(dataSource, SPROC_NAME);
declareParameter(new SqlOutParameter("o_errorcode", OracleTypes.NUMBER));
declareParameter(new SqlOutParameter("o_errortext", OracleTypes.VARCHAR));
declareParameter(new SqlOutParameter("o_output", OracleTypes.VARCHAR));
declareParameter(new SqlParameter("i_num", OracleTypes.NUMBER));
declareParameter(new SqlParameter("i_txt", OracleTypes.VARCHAR));
compile();
}
}


import java.util.HashMap;
import java.util.Map;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autow ired;
import org.springframework.test.context.ContextConfigurat ion;
import org.springframework.test.context.junit4.SpringJUni t4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration( { "dispatcherServlet-servlet.xml" })

public class ProcedureTest {

private KJDebugStoredProcedure kds;

@Autowired
public void setSP(KJDebugStoredProcedure kds){
this.kds = kds;
}
@Test
public void testKJDebug(){
Map<String, Object> inParams = new HashMap<String, Object>(2);
inParams.put("i_num", new Integer(50));
inParams.put("i_txt", "Hello KJ");
Map m = kds.execute(inParams);
System.out.println(m);



}
}

In the dispatcher servlet the following config is required
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverM anagerDataSource">
<property name="driverClassName" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@....:1521:...db" />
</bean>


<bean id="kjdebugsp" class="db.proc.KJDebugStoredProcedure">
<constructor-arg><ref bean="dataSource"/></constructor-arg>
</bean>


Package Body kjdebug.

PROCEDURE testsp(o_errorcode OUT NUMBER, o_errortext OUT VARCHAR2, o_output OUT VARCHAR2, i_num IN NUMBER, i_txt IN VARCHAR2)
IS
v_text VARCHAR2(100);
BEGIN
o_output := 'Test ' || i_num || ' txt' || i_txt;
--v_text := 'Test ' || i_num || ' txt' || i_txt;
o_errorcode := 55;
--o_output := 'Test it';
--NULL;
END;

[leonardo.mattos]

Hi,

On our firms application, even we avoid to call procedures from database, when we have to do it, we follow a similar approach from Upenderc sample, using JDBC and spring support.

To me do not have any sense to use Hibernate in this case, even that´s is posible, because of my understanding from ORM is to map objetcs to relational model and not do wrap all the database access. So I try to set this as the guidance of our database decisions.

[jakain]

I had to do this a while back and just used JdbcTemplate, I was using hibernate already in the application but trying to map the stored procedure with the NamedNativeQuery annotation turned out to be a major PITA. Found using JdbcTemplate was straightforward and easier to maintain.

Example of calling a function that takes 2 NUMBER parameters and returns a NUMBER

final Object result = jdbcTemplate.execute(
                "{ ? = call myPackage.someFunction(?,?)}", new CallableStatementCallback() {
  public Object doInCallableStatement(final CallableStatement cs) throws SQLException {
    cs.registerOutParameter (1, Types.INTEGER);
    cs.setLong(2, someParam);
    cs.setLong(3, anotherParam);                    
    cs.execute();
    return cs.getInt(1);
  }
});

[jayakrishnapalla]

I had to do this a while back and just used JdbcTemplate, I was using hibernate already in the application but trying to map the stored procedure with the NamedNativeQuery annotation turned out to be a major PITA. Found using JdbcTemplate was straightforward and easier to maintain.

Example of calling a function that takes 2 NUMBER parameters and returns a NUMBER

final Object result = jdbcTemplate.execute(
                "{ ? = call myPackage.someFunction(?,?)}", new CallableStatementCallback() {
  public Object doInCallableStatement(final CallableStatement cs) throws SQLException {
    cs.registerOutParameter (1, Types.INTEGER);
    cs.setLong(2, someParam);
    cs.setLong(3, anotherParam);                    
    cs.execute();
    return cs.getInt(1);
  }
});

Hi,he StoredProcedure class is a superclass for object abstractions of RDBMS stored procedures. This class is abstract, and its various execute(..) methods have protected access, preventing use other than through a subclass that offers tighter typing.

The inherited sql property will be the name of the stored procedure in the RDBMS. Note that JDBC 3.0 introduces named parameters, although the other features provided by this class are still necessary in JDBC 3.0.

Here is an example of a program that calls a function, sysdate(), that comes with any Oracle database. To use the stored procedure functionality one has to create a class that extends StoredProcedure. There are no input parameters, but there is an output parameter that is declared as a date type using the class SqlOutParameter. The execute() method returns a map with an entry for each declared output parameter using the parameter name as the key.
import java.sql.Types;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.datasource.*;
import org.springframework.jdbc.object.StoredProcedure;

public class TestStoredProcedure {

public static void main(String[] args) {
TestStoredProcedure t = new TestStoredProcedure();
t.test();
System.out.println("Done!");
}

void test() {
DriverManagerDataSource ds = new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:mydb") ;
ds.setUsername("scott");
ds.setPassword("tiger");

MyStoredProcedure sproc = new MyStoredProcedure(ds);
Map results = sproc.execute();
printMap(results);
}

private class MyStoredProcedure extends StoredProcedure {

private static final String SQL = "sysdate";

public MyStoredProcedure(DataSource ds) {
setDataSource(ds);
setFunction(true);
setSql(SQL);
declareParameter(new SqlOutParameter("date", Types.DATE));
compile();
}

public Map execute() {
// the 'sysdate' sproc has no input parameters, so an empty Map is supplied...
return execute(new HashMap());
}
}

private static void printMap(Map results) {
for (Iterator it = results.entrySet().iterator(); it.hasNext(); ) {
System.out.println(it.next());
}
}
}

Find below an example of a StoredProcedure that has two output parameters (in this case Oracle cursors).
import oracle.jdbc.driver.OracleTypes;
import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.object.StoredProcedure;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

public class TitlesAndGenresStoredProcedure extends StoredProcedure {

private static final String SPROC_NAME = "AllTitlesAndGenres";

public TitlesAndGenresStoredProcedure(DataSource dataSource) {
super(dataSource, SPROC_NAME);
declareParameter(new SqlOutParameter("titles", OracleTypes.CURSOR, new TitleMapper()));
declareParameter(new SqlOutParameter("genres", OracleTypes.CURSOR, new GenreMapper()));
compile();
}

public Map execute() {
// again, this sproc has no input parameters, so an empty Map is supplied...
return super.execute(new HashMap());
}
}