Result Extraction

If your stored procedure returns a result there are three different way of how you can retrieve the result.

Out Parameter

For stored procedures that use out parameter use the @OutParameter annotation.

public interface TaxProcedures {

  @OutParameter
  BigDecimal salesTax(BigDecimal subtotal);

}

The out parameter is assumed to be the last parameter of the stored procedure. If the out parameter has a different index use the index attribute.

public interface TaxProcedures {

  @OutParameter(index = 1) // JDBC indices are 1-based
  BigDecimal salesTax(BigDecimal subtotal);

}

Return Value

For SQL functions rather than stored procedures use the @ReturnValue annotation

public interface TaxProcedures {

  @ReturnValue
  BigDecimal salesTax(BigDecimal subtotal);

}

ResultSet

Some JDBC drivers (e.g.. H2) don't support out parameter but return the values in a ResultSet, in this case add neither annotation. The same is true for HSQLDB functions (HSQLDB procedures support you parameters).

public interface TaxProcedures {

  BigDecimal salesTax(BigDecimal subtotal);

}

InOut Parameter

For stored procedures that use inout parameters use the @InOutParameter annotation.

public interface TaxProcedures {

  @InOutParameter
  BigDecimal salesTax(BigDecimal subtotal);

}

The inout parameter is assumed to be the last parameter of the stored procedure. If the inout parameter has a different index use the index attribute.

public interface TaxProcedures {

  @InOutParameter(index = 1) // JDBC indices are 1-based
  BigDecimal salesTax(BigDecimal subtotal, String state);

}

@InOutParameter allows to specify much less information than @OutParameter as the information is taken from the method Parameters.

Ref Cursor

If the stored procedure uses a ref cursor to return multiple values they can be collected into a List

public interface DateProcedures {

  @OutParameter
  List<Date> getLeapYears();

}

If your ref cursor has more than one column you can additionally pass a ValueExtractor extract the values of a row into an object.

public interface DateProcedures {

  @OutParameter
  List<Year> getLeapYears(ValueExtractor<Year> extractor);

}

// calling the stored procedure
List<Year> leapYears = dateProcedures.getLeapYears((resultSet, rowNumber) -> {
  int century = resultSet.getInt("century");
  int decade = resultSet.getInt("decade");
  return Year.of(century * 100 + decade);
});

The ValueExtractor interface is equal in functionality to Spring-JDBC RowMapper.

If the procedure uses out parameters then JDBC 4.2 is required to support this feature. If the driver does not support JDBC 4.2 vendor types are needed. E.g. for the OJDBC 12.1c driver or earlier the type of the out parameter has to be set to OracleTypes#CURSOR

MySQL implicitly supports ref cursors but not via out parameters. In this case simply leave out the @OutParameter and @ReturnValue annotations as the driver returns it as a ResultSet.

Starting with version 0.10.0 of the library when you are on Java 9 or later you can write the RowMapper in a default method in the interface.

public interface DateProcedures {

  @OutParameter
  List<Year> getLeapYears(ValueExtractor<Year> extractor);

  // just a convenience method, so no annotations
  default List<Year> getLeapYears() {
    return this.getLeapYears((resultSet, rowNumber) -> {
      int century = resultSet.getInt("century");
      int decade = resultSet.getInt("decade");
      return Year.of(century * 100 + decade);
    });
  }

}

Then you can simply call the default method.

List<Year> leapYears = dateProcedures.getLeapYears();

Array

SQL Arrays as return values are supported through Java Arrays, see the dedicated Array Documentation for more information.