Call an Oracle Function from Spring with CallableStatement

Following the previous post, I wondered if there's a more elegant way to call a function. It turns out there is and it's based on CallableStatement:

int value = jdbcOperations.execute(
  new CallableStatementCreator() {
    // ...
  },
  new CallableStatementCallback<Integer>() {
    // ...
  }
);

The CallableStatementCreator sets up the statement and the CallableStatementCallback executes the statement and returns the value up to the execute() call.

Having a function:

CREATE OR REPLACE FUNCTION HAS_PANEL_FLAG_OK(
  P_USER_ID    in USERS.USER_ID%TYPE,
  P_PANEL_ID   in PANELS.PANEL_ID%TYPE,
)
RETURN integer
IS
  lv_count NUMBER;
BEGIN
  SELECT count(*) into lv_count
    FROM USERS
    WHERE USERS.PANEL_ID = P_PANEL_ID and USERS.USER_ID = P_USER_ID;
  return lv_count;
EXCEPTION
  when OTHERS then
    return 0;
END;

We can build a query string like:

static final String PANEL_OPERATIONS_OK =
  "{ ? = call HAS_PANEL_FLAG_OK(?, ?) }";

The CallableStatementCreator instance is then:

CallableStatementCreator statementCreator =
    new CallableStatementCreator() {
  @Override
  public CallableStatement createCallableStatement(Connection con) throws SQLException {
    CallableStatement cs = con.prepareCall(PANEL_OPERATIONS_OK);
    cs.registerOutParameter(1, Types.INTEGER); // The returned result
    cs.setString(2, userId);
    cs.setString(3, panelId);
    return cs;
  }
}

This states the first parameter is a result, the second and 3rd parameter are function parameters.

NOTE: Be careful because you can specify indices or keywords for parameter positioning, but you're not allowed to mix the two!

The CallableStatementCallback is:

CallableStatementCallback<Integer> statementCallback =
    new CallableStatementCallback<Integer>() {
  @Override
  public Integer doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
    cs.execute();
    int result = cs.getInt(1);
    return result; // Propagated to the jdbcTemplate.execute method
  }
}

This will execute the statement and will return the result (first parameter of the statement).

The jdbcOperations.execute() function will return the result from CallableStatementCallback's doInCallableStatement().

The full java code looks like this:

int value = jdbcOperations.execute(
  statementCreator,
  statementCallback
);

This is much more powerful. You can customise all function's parameters the same as with a classic SQL query.

HTH,