Calling Stored Procedures via Spring API

To call database stored procedure from a Java application, the normal way is to make use of JDBC API via CallableStatement. Coming of the age, Spring comes with Object level representation of Stored Procedure. Welcome to StoredProcedure class. This class gives us an object oriented abstraction of Stored procedures. Rather than making use of native JDBC API to call a stored proc, create and instance of StoredProcedure class and pass on the procedure parameters to it. Lets quickly get into an example.

Lets say we have a procedure calculateTax, which takes yearly income as the parameter and returns the tax amount. One approach is you may create a custom stored procedure class called TaxStoredProc , which can be used by other parts of your application which are interested in invoking this procedure.

public class TaxStoredProc extends StoredProcedure{

public TaxStoredProc(){

setSql("calculateTax");

DataSource ds = getDataSource();

setDataSource(ds);

}

public Double calculateTax(double income){

declareParams();

Map params = new HashMap();

params.put("income", income);

Map output = execute(params);

//this map out contains out put result.

// value of the stored proc is stored in the out put parameter "result"

Double result = (Double)output.get("result");

return result;

}

private void declareParams() {

declareParameter(new SqlParameter("income",Types.DOUBLE));

// out put parameter which will collect the result.

declareParameter(new SqlOutParameter("result",Types.DOUBLE));

}

}

Code is simple. Extend StoredProcedure class with your custom class. while instantiating pass on the name of the procedure by calling setSql(procName), which is a method of the super class StoredProcedure hierarchy. Get the dataSource object (It depends on how you want to get it...so not specified here.) and set it. Create input parameters and output parameter for result and just call execute method passing the parameter map.

By this we have created a wrapper over the database stored proc called calculateTax.

Say from some part of your application one is required to call this proc, then he/she is not supposed to be aware of the proc name and write any jdbc api to call this. One can simply make use of this as :

TaxStoredProc proc = new TaxStoredProc ();

Double tax = proc.calculateTax(5689.45);

Just a two line of code and done.

Extending this set up would also help you develop Junit test cases for testing stored procedures. The test case writer has nothing to worry about the JDBC api semantics, and can simply instantiate our wrapper StoredProcedure instances and call methods on it.

Bidirectional Map

The most common hits we make to a map is to retrieve the value of a mapping based on a key. Now suppose, we have a collection of values and we need to fetch their corresponding keys from the map. Plain old way iterate over the entry set in the map, get their values and then corresponding keys. Apache commons library has pretty cool utility methods for jdk collections API. One of them is a Bidirectional Map, with the interface as BidiMap. This map allows bidirectional lookup between key and values. Calling bidimap.getKey(Object value) gives you the corresponding key for the passed on value. Other useful interfaces like OrderdMap allows you to maintain ordering in the map based data structures. Other interesting feature comes in the form of Bag implementation. Bag is a data structure that stores an object along with its cardinality(number of occurences) .