Spring JDBC Template for calling Stored Procedures

Question

What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template?

Say, I have a stored procedure that declares both IN and OUT parameters, something like this:

mypkg.doSomething(
    id OUT int,
    name IN String,
    date IN Date
)

I have come across CallableStatementCreator based approaches where we have to explicitly register IN and OUT parameters. Consider the following method in JdbcTemplate class:

public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)

Of course, I do know that I can use it like so:

List<SqlParameter> declaredParameters = new ArrayList<SqlParameter>();

declaredParameters.add(new SqlOutParameter("id", Types.INTEGER));
declaredParameters.add(new SqlParameter("name", Types.VARCHAR));
declaredParameters.add(new SqlParameter("date", Types.DATE));

this.jdbcTemplate.call(new CallableStatementCreator() {

    @Override
    CallableStatement createCallableStatement(Connection con) throws SQLException {
        CallableStatement stmnt = con.createCall("{mypkg.doSomething(?, ?, ?)}");

        stmnt.registerOutParameter("id", Types.INTEGER);
        stmnt.setString("name", "<name>");
        stmnt.setDate("date", <date>);

        return stmnt;
    }
}, declaredParameters);

What is the purpose of declaredParameters when I am already registering them in my csc implementation? In other words, why would I need to pass in a csc when spring can simply do con.prepareCall(sql) internally? Basically, can't I pass in either one of them instead of both of them?

Or, is there a much better way to call stored procedures (using Spring JDBC Template) than what I have come across so far?

Note: You may find many questions that appear to have a similar title but they are not the same as this one.

Answer 1

There are a number of ways to call stored procedures in Spring.

If you use CallableStatementCreator to declare parameters, you will be using Java's standard interface of CallableStatement, i.e register out parameters and set them separately. Using SqlParameter abstraction will make your code cleaner.

I recommend you looking at SimpleJdbcCall. It may be used like this:

SimpleJdbcCall jdbcCall = new SimpleJdbcCall(jdbcTemplate)
    .withSchemaName(schema)
    .withCatalogName(package)
    .withProcedureName(procedure)();
...
jdbcCall.addDeclaredParameter(new SqlParameter(paramName, OracleTypes.NUMBER));
...
jdbcCall.execute(callParams);

For simple procedures you may use jdbcTemplate's update method:

jdbcTemplate.update("call SOME_PROC (?, ?)", param1, param2);

Answer 2

Here are the ways to call the stored procedures from java

1. Using CallableStatement:

 connection = jdbcTemplate.getDataSource().getConnection();
  CallableStatement callableStatement = connection.prepareCall("{call STORED_PROCEDURE_NAME(?, ?, ?)}");
  callableStatement.setString(1, "FirstName");
  callableStatement.setString(2, " LastName");
  callableStatement.registerOutParameter(3, Types.VARCHAR);
  callableStatement.executeUpdate();

Here we externally manage the resource closing

2. Using CallableStatementCreator

 List paramList = new ArrayList();
    paramList.add(new SqlParameter(Types.VARCHAR));
    paramList.add(new SqlParameter(Types.VARCHAR));
    paramList.add(new SqlOutParameter("msg", Types.VARCHAR));

    Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {

    @Override
    public CallableStatement createCallableStatement(Connection connection)
    throws SQLException {

    CallableStatement callableStatement = connection.prepareCall("{call STORED_PROCEDURE_NAME(?, ?, ?)}");
    callableStatement.setString(1, "FirstName");
            callableStatement.setString(2, " LastName");
            callableStatement.registerOutParameter(3, Types.VARCHAR);
    return callableStatement;

    }
    }, paramList);

3. Use SimpleJdbcCall:

SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)

.withProcedureName("STORED_PROCEDURE_NAME");

Map<String, Object> inParamMap = new HashMap<String, Object>();
inParamMap.put("firstName", "FirstNameValue");
inParamMap.put("lastName", "LastNameValue");
SqlParameterSource in = new MapSqlParameterSource(inParamMap);


Map<String, Object> simpleJdbcCallResult = simpleJdbcCall.execute(in);
System.out.println(simpleJdbcCallResult);

4. Use StoredProcedure class of org.springframework.jdbc.object

The Code:
First Create subclass of StoredProcedure: MyStoredProcedure

class MyStoredProcedure extends StoredProcedure {

public MyStoredProcedure(JdbcTemplate jdbcTemplate, String name) {

super(jdbcTemplate, name);
setFunction(false);

}

}

Use MyStoredProcedure to call database stored procedure:


//Pass jdbcTemlate and name of the stored Procedure.
MyStoredProcedure myStoredProcedure = new MyStoredProcedure(jdbcTemplate, "PROC_TEST");

//Sql parameter mapping
SqlParameter fNameParam = new SqlParameter("fName", Types.VARCHAR);
SqlParameter lNameParam = new SqlParameter("lName", Types.VARCHAR);
SqlOutParameter msgParam = new SqlOutParameter("msg", Types.VARCHAR);
SqlParameter[] paramArray = {fNameParam, lNameParam, msgParam};


myStoredProcedure.setParameters(paramArray);
myStoredProcedure.compile();


//Call stored procedure
Map storedProcResult = myStoredProcedure.execute("FirstNameValue", " LastNameValue");

Reference

Answer 3

I generally prefer to extend Spring based StoredProcedure class to execute stored procedures.

1. You need to create your class constructor and need to call StoredProcedure class constructor in it. This super class constructor accepts DataSource and procedure name.

   Example code:

   public class ProcedureExecutor extends StoredProcedure {
         public ProcedureExecutor(DataSource ds, String funcNameorSPName) {
           super(ds, funcNameorSPName);
           declareParameter(new SqlOutParameter("v_Return", Types.VARCHAR, null, new SqlReturnType() {
                   public Object getTypeValue(CallableStatement cs,
                        int paramIndex, int sqlType, String typeName) throws SQLException {
                   final String str = cs.getString(paramIndex);
                   return str;
               }           
           }));    
           declareParameter(new SqlParameter("your parameter",
                   Types.VARCHAR));
           //set below param true if you want to call database function 
           setFunction(true);
           compile();
           }
   

2. Override execute method of stored procedure call as below

   public Map<String, Object> execute(String someParams) {
                final Map<String, Object> inParams = new HashMap<String, Object>(8);
                inParams.put("my param", "some value");
                Map outMap = execute(inParams);
                System.out.println("outMap:" + outMap);
                return outMap;
            }
   

Hope this helps you.

Answer 4

One more way to call stored procedure is:

sql="execute Procedure_Name ?";
Object search[]={Id};
List<ClientInvestigateDTO> client=jdbcTemplateObject.query(sql,search,new 
   ClientInvestigateMapper());

In this example 'ClientInvestigateDTO' is the POJO class and 'ClientInvestigateMapper' is the mapper class.'client' stores all the result you get on calling the stored procedure.

Answer 5

Using jdbcTemplate.query() to call a procedure does not catch errors raised by the procedure in some cases. I have seen this behavior on MS SqlServer where a proceudure was raising an error using RAISEERROR in case of an error and calling the proc using the query method was ignoring the error.

I switched to SimpleJdbcTemplate for procedure calls and that works. Note : this might not apply to procedures. In my case the proc was doing a select to return data and after that if it had not found any data it would raise an error. Using jdbcTemplate.query woudld give me back an empty data set with no error.

Answer 6

I'm replying to your comment:

Or, is there a much better way to call stored procedures (using Spring JDBC Template) than what I have come across so far?

And to a comment from the comment section:

If anyone thinks there is an even nicer way to call stored procedures now that Spring 4 is out

If you're open to using a third party library, jOOQ has a code generator that can be used to generate stubs for all of your stored procedures, functions, packages, UDTs, etc.

In your particular case, there would be a Mypkg class with a doSomething() method that you could call like this:

int id = Mypkg.doSomething(
    configuration, // This wraps your JDBC connection
    name, date
);

That's it. All the plumbing to JDBC is being taken care of, which is especially nice when you have UDTs (Oracle OBJECT or TABLE types, PACKAGE types), or implicit cursors.

You said that you'd like to use Spring JDBC Template, but you can obviously just extract the JDBC Connection from it and supply that to jOOQ, so technically, you'll still be using Spring JDBC Template...

Disclaimer: I work for the company behind jOOQ

Answer 7

In case of DML procedure & no return variables I used:

Map params = ImmutableMap.builder().put("p1", p1).put("p2", p2).build();
jdbcTemplate.update("{ call pack.proc( :p1, :p1) }", params);

PS In case there are return variables better use functions instead