Spring Jdbc Template for Calling Stored Procedures

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

Achieving transaction by calling multiple procedures using Spring jdbc template

Following code will call multiple stored procedures within the same transaction.

@Transactional(rollbackFor=Exception.class)
public void callStoredProcedures(){
   // Stored procedure 1
   //....
   // Stored procedure n
}

A transaction would be initialized at the method start. All the subsequent database calls within that method will take part in this transaction and any exception within the method context would rollback the transaction.

Note that the transaction rollback for this method is configured for any Exception. By default a transaction is marked for rollback on exceptions of type RuntimeException and JdbcTemplate methods throw DataAccessException which is its subclass. If no rollback is required for checked exceptions the (rollbackFor=Exception.class) can be removed.

Also , for @Transactional annotation to work , enable transaction management. Please go through @EnableTransactionManagement

Spring JDBCTemplate Stored Procedure with ResultSet and OutPut Parameter

We use something like the following in our code

public Map<String, Object> findData() {
        List prmtrsList = new ArrayList();
        prmtrsList.add(new SqlParameter(Types.VARCHAR));
        prmtrsList.add(new SqlParameter(Types.VARCHAR));
        prmtrsList.add(new SqlOutParameter("result", Types.VARCHAR));

        Map<String, Object> resultData = jdbcTemplate.call(connection -> {
            CallableStatement callableStatement = connection.prepareCall("{call STORED_PROC(?, ?, ?)}");
            callableStatement.setString(1, "first");
            callableStatement.setString(2, "last");
            callableStatement.registerOutParameter(3, Types.VARCHAR);
            return callableStatement;
        }, prmtrsList);
        return resultData;
    }

---

Related Topics