Stored Procedures With Spring JdbcTemplate

1. Overview

In this tutorial, we’ll discuss the Spring JDBC framework’s JdbcTemplate class’s ability to execute a database stored procedure. Database stored procedures are similar to functions. While functions support input parameters and have a return type, procedures support both input and output parameters.

2. Prerequisite

Let’s consider a simple stored procedure in the PostgreSQL database:

CREATE OR REPLACE PROCEDURE sum_two_numbers(
    IN num1 INTEGER,
    IN num2 INTEGER,
    OUT result INTEGER
)
LANGUAGE plpgsql
AS '
BEGIN
    sum_result := num1 + num2;
END;
';

The stored procedure sum_two_numbers takes two input numbers and returns their sum in the output parameter sum_result. Generally, stored procedures can support multiple input and output parameters. However, for this example, we’ve considered a single output parameter.

3. Using the JdbcTemplate#call() Method

Let’s see how to invoke the database stored procedure by using the JdbcTemplate#call() method:

void givenStoredProc_whenCallableStatement_thenExecProcUsingJdbcTemplateCallMethod() {
    List<SqlParameter> procedureParams = List.of(new SqlParameter("num1", Types.INTEGER),
      new SqlParameter("num2", Types.NUMERIC),
      new SqlOutParameter("result", Types.NUMERIC)
    );

    Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement callableStatement = con.prepareCall("call sum_two_numbers(?, ?, ?)");
            callableStatement.registerOutParameter(3, Types.NUMERIC);
            callableStatement.setInt(1, 4);
            callableStatement.setInt(2, 5);

            return callableStatement;
        }
    }, procedureParams);

    assertEquals(new BigDecimal(9), resultMap.get("result"));
}

First, we define the IN parameters num1 and num2 of the stored procedure sum_two_numbers() with the help of the SqlParameter class. Then, we define the OUT parameter result with the SqlOutParameter.

Later, we pass the CallableStatementCreater object and the List<SqlParameter> in procedureParams to the JdbcTemplate#call() method.

In the CallableStatementCreator#createCallableStatement() method, we create the CallableStatement object by calling the Connection#prepareCall() method. Similar to PreparedStatement, we set the IN parameters in the CallableStatment object.

However, we must register the OUT parameter using the registerOutParameter() method.

Finally, we retrieve the result from the Map object in resultMap.

4. Using JdbcTemplate#execute() Method

There could be scenarios where we would need more control over the CallableStatement. Hence, the Spring framework provides the CallableStatementCallback interface similar to PreparedStatementCallback. Let’s see how to use it in the JdbcTemplate#execute() method:

void givenStoredProc_whenCallableStatement_thenExecProcUsingJdbcTemplateExecuteMethod() {
    String command = jdbcTemplate.execute(new CallableStatementCreator() {
        @Override
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement callableStatement = con.prepareCall("call sum_two_numbers(?, ?, ?)");
            return callableStatement;
        }
    }, new CallableStatementCallback<String>() {
        @Override
        public String doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
            cs.setInt(1, 4);
            cs.setInt(2, 5);
            cs.registerOutParameter(3, Types.NUMERIC);
            cs.execute();
            BigDecimal result = cs.getBigDecimal(3);
            assertEquals(new BigDecimal(9), result);

            String command = "4 + 5 = " + cs.getBigDecimal(3);
            return command;
        }
    });
    assertEquals("4 + 5 = 9", command);
}

The CallableStatementCreator object parameter creates the CallableStatement object. Later, it’s available in the CallableStatementCallback#doInCallableStatement() method.

In this method, we set the IN and OUT parameters in the CallableStatement object, and later, we call CallableStatement#execute(). Finally, we fetch the result and form the command  4 + 5 = 9.

We can reuse the CallableStatement object in doInCallableStatement() method multiple times to execute the stored procedure with different parameters.

5. Using SimpleJdbcCall

The SimpleJdbcCall class internally uses JdbcTemplate to execute stored procedures and functions. It also supports fluent-style method chaining, making it simpler to understand and use.

Additionally, SimpleJdbcCall is designed to work in multi-threaded scenarios. Hence, it allows for safe, concurrent access by multiple threads without requiring any external synchronization.

Let’s see how we can call the stored procedure sum_two_numbers with the help of this class:

void givenStoredProc_whenJdbcTemplate_thenCreateSimpleJdbcCallAndExecProc() {
    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withProcedureName("sum_two_numbers");

    Map<String, Integer> inParams = new HashMap<>();
    inParams.put("num1", 4);
    inParams.put("num2", 5);
    Map<String, Object> resultMap = simpleJdbcCall.execute(inParams);
    assertEquals(new BigDecimal(9), resultMap.get("result"));
}

First, we instantiate the SimpleJdbcCall class by passing the JdbcTemplate object to its constructor. Under the hood, this JdbcTemplate object executes the stored procedure. Then, we pass the procedure name to SimpleJdbcCall#withProcedureName() method.

Finally, we get the results in a Map object by passing the input parameters in a Map to the SimpleJdbcCall#execute() method. The results are stored against the keys with the name of the OUT parameter.

Interestingly, there’s no need to define the metadata of the stored procedure parameters because the SimpleJdbcCall class can read the database metadata. This support is limited to a few databases such as Derby, MySQL, Microsoft SQL Server, Oracle, DB2, Sybase, and PostgreSQL.

Hence, for others, we need to define the parameters explicitly in the SimpleJdbcCall#declareParameters() method:

@Test
void givenStoredProc_whenJdbcTemplateAndDisableMetadata_thenCreateSimpleJdbcCallAndExecProc() {
    SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate)
      .withProcedureName("sum_two_numbers")
      .withoutProcedureColumnMetaDataAccess();
    simpleJdbcCall.declareParameters(new SqlParameter("num1", Types.NUMERIC),
      new SqlParameter("num2", Types.NUMERIC),
      new SqlOutParameter("result", Types.NUMERIC));

    Map<String, Integer> inParams = new HashMap<>();
    inParams.put("num1", 4);
    inParams.put("num2", 5);
    Map<String, Object> resultMap = simpleJdbcCall.execute(inParams);
    assertEquals(new BigDecimal(9), resultMap.get("result"));
}

We disabled the database metadata processing by calling the SimpleJdbcCall#withoutProcedureColumnMetaDataAccess() method. The rest of the steps remain the same as before.

6. Using StoredProcedure

StoredProcedure is an abstract class, and we can override its execute() method for additional processing:

public class StoredProcedureImpl extends StoredProcedure {
    public StoredProcedureImpl(JdbcTemplate jdbcTemplate, String procName) {
        super(jdbcTemplate, procName);
    }

    private String doSomeProcess(Object procName) {
        //do some processing
        return null;
    }

    @Override
    public Map<String, Object> execute(Map<String, ?> inParams) throws DataAccessException {
        doSomeProcess(inParams);
        return super.execute(inParams);
    }
}

Let’s see how to use this class:

@Test
void givenStoredProc_whenJdbcTemplate_thenCreateStoredProcedureAndExecProc() {
    StoredProcedure storedProcedure = new StoredProcedureImpl(jdbcTemplate, "sum_two_numbers");
    storedProcedure.declareParameter(new SqlParameter("num1", Types.NUMERIC));
    storedProcedure.declareParameter(new SqlParameter("num2", Types.NUMERIC));
    storedProcedure.declareParameter(new SqlOutParameter("result", Types.NUMERIC));

    Map<String, Integer> inParams = new HashMap<>();
    inParams.put("num1", 4);
    inParams.put("num2", 5);

    Map<String, Object> resultMap = storedProcedure.execute(inParams);
    assertEquals(new BigDecimal(9), resultMap.get("result"));
}

Like SimpleJdbcCall, we first instantiate the subclass of StoredProcedure by passing in the JdbcTemplate and stored procedure name. Then we set the parameters, execute the stored procedure, and get the results in a Map.

Additionally, we must remember to declare the SqlParameter objects in the same order in which the parameters are passed to the stored procedure.

7. Conclusion

In this article, we discussed the JdbcTemplate‘s capability to execute stored procedures. JdbcTemplate has been the core class for handling data operations in databases. It can be used directly or implicitly with the help of wrapper classes like SimpleJdbcCall and StoredProcedure.