Columbian Coffe

The Columbian Coffe is a tutorial by Oracle on how to use JDBC and stored procedures. This article is inspired by an spwrap article on how to use spwarp instead of the code from the example.

The Columbian Coffe example is fairly small and contains only three procedures.

OutParameter

The first procedure is fairly simple and contains only one in and one out parameter

create procedure GET_SUPPLIER_OF_COFFEE(IN coffeeName varchar(32), OUT supplierName varchar(40))
  begin
    -- ...
  end;

This can be mapped fairly directly to Java. We create an interface to which we add a method for every stored procedure in Java

public interface Coffee {

  @OutParameter
  String getSupplierOfCoffee(String coffeeName);

}

The @OutParameter annotation signals that the result should be retrieved using an out parameter. If the out parameter wasn't the last parameter we would have to configure the index by setting @OutParameter(index=). The name of the interface can be chosen freely. We only have to make sure the getSupplierOfCoffee method name is mapped to GET_SUPPLIER_OF_COFFEE in SQL.

We can create an instance of the Coffee with the ProcedureCallerFactory class, it only needs a DataSource object. We customize the naming strategy so that we can use camel case in Java and snake case in SQL. Otherwise we would have to use snake case in Java or add another annotation to configure the SQL name of the procedure.

Coffee coffee = ProcedureCallerFactory.of(Coffee.class, dataSource)
    // getSupplierOfCoffee -> get_Supplier_Of_Coffee -> GET_SUPPLIER_OF_COFFEE
    .withProcedureNamingStrategy(NamingStrategy.snakeCase().thenUpperCase())
    .build();

RefCursor

The second procedure uses a ref cursor to return a result set

create procedure SHOW_SUPPLIERS()
  begin
    select SUP_NAME, COF_NAME
    -- ...
  end;

We can implement this like this

  List<CoffeeSupplier> showSuppliers(ValueExtractor<CoffeeSupplier> extractor);

Three things to note here:

• Since neither an out parameter nor an inout parameter nor a return value is used we do not need to add an annotation on how the result is retrieved. This relies on the driver returning a ResultSet.
• CoffeeSupplier is a simple data transfer object holding the name of the coffee supplier and the name of the coffee.
• ValueExtractor takes a ResultSet as an argument and returns a CoffeeSupplier, this is the same concept as a Spring JDBC RowMapper but without the index

We can call the function like this. Since ValueExtractor is a functional interface we can use a lambda expression

cofffee.showSuppliers(rs ->
    new CoffeeSupplier(rs.getString(1), rs.getString(2)))

InOutParameter

The final procedure uses an inout parameter to return a result.

create procedure RAISE_PRICE(IN coffeeName varchar(32), IN maximumPercentage float, INOUT newPrice numeric(10,2))
  begin
    main: BEGIN
      -- ...
  end;

This can be mapped fairly directly to Java.

  @InOutParameter
  BigDecimal raisePrice(String coffeeName, float maximumPercentage, BigDecimal newPrice);

The @OutParameter annotation signals that the result should be retrieved using an out parameter. If the inout parameter wasn't the last parameter we would have to configure the index by setting @InOutParameter(index=). Note that unlike in the Oracle tutorial we use BigDecimal to represent NUMERIC(10,2) instead of float since NUMERIC is an exact, fixed-point data type.

The sources from this article can be found in the examples repository. The only thing changed from the Oracle code was the removal of logging using SELECT.