Spring JDBC Template for calling Stored Procedures

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);

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: MyStoredProcedureclass 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 mappingSqlParameter 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 procedureMap storedProcResult = myStoredProcedure.execute("FirstNameValue", " LastNameValue");

Reference

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.