28 February 2017

Using spwrap to simplify calling stored procedures from java

spwrap is a tiny framework to simplify the call to stored procedures from Java code.

In this post, I'll implement the Columbian coffee example from Java Tutorial at oracle.

This example uses mysql database to store coffee and suppliers data. see the install script at github.

The script installs 2 Tables: suppliers and coffees fill them with data and create 3 stored procedures: 
show_suppliers: To list all coffee names with supplier names (takes no parameters and return as result set)
get_supplier_of_coffee: To get a supplier name of coffee (takes 1 input parameter and return 1 output parameter)
raise_price: To raise the price of coffee (take 3 input parameters and return 1 output parameter)
Note: the original  raise_price stored procedure take 2 input parameters and 1 in/out parameter, but spwrap doesn't support INOUT parameters, so I split it into 1 input and 1 output parameter.

We will use spwrap to simplify the call to these 3 stored procedures.

First, We need to Create a Java interface to represent these 3 stored procedures:
public interface ColumbianDAO {

    List<SupplierCoffee> showSuppliers();

    String getSupplierOfCoffee(@Param(VARCHAR) String coffeeName);

    BigDecimal raisePrice(@Param(VARCHAR)String coffeeName,
                          @Param(FLOAT)float maximumPercentage,
                          @Param(NUMERIC) BigDecimal newPrice);
The interface contains 3 methods to represent the 3 stored procedures, the annotation @StoredProc uses to mark method as a stored procedure.

The annotation @Scalar to represent the return type of stored procedure so that the output parameter mapped correctly to the method return type.

For the stored procedures that return its result as result set, you need to provide result set mapper to map the result set object to your domain object (SupplierCoffee), here's the mappers implementation:
public class SupplierCoffee implements ResultSetMapper<SupplierCoffee> {
    private String supplierName, coffeeName;

    public SupplierCoffee map(Result<?> result) {
        // convert the result into SupplierCoffee        
        SupplierCoffee supplierCoffee = new SupplierCoffee();
        supplierCoffee.supplierName = result.getString(1);
        supplierCoffee.coffeeName = result.getString(2);
        return supplierCoffee;

    public String toString() {
        return "SupplierCoffee{" +
                "supplierName='" + supplierName + '\'' +
                ", coffeeName='" + coffeeName + '\'' +
And now you can call the stored procedures using the following code:
DAO dao = new DAO.Builder("jdbc:mysql://localhost:3306/columbian", "root", "")
        .config(new Config().useStatusFields(false))

ColumbianDAO columbianDAO = dao.create(ColumbianDAO.class);

List<SupplierCoffee> supplierCoffees = columbianDAO.showSuppliers();

String coffee = "Colombian";
String supplier = columbianDAO.getSupplierOfCoffee(coffee);
System.out.printf("Supplier of the coffee '%s' is '%s'\n", coffee, supplier);

BigDecimal newPrice = columbianDAO.raisePrice(coffee, 0.10f, BigDecimal.valueOf(19.99));
System.out.printf("new price of '%s' is '%s'\n", coffee, newPrice);
Download the complete source code of the complete example at github.

If you want to know more about spwrap visit the project page at github.  If you like the project, please start it at github :)
Post a Comment