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) .

Where Oracle IN operator fails...

I figured this out while working in one of my recent projects. Implementing the data access layer for search use cases through Spring's Hibernate support framework, I require to create a lot of restrictions (Hibernate Criterion API). This most of the time requires to create a Restrictions.sqlRestriction criterion for certain search criteria. I had a condition to create a restriction like this

DetachedCriteria criteria = DetachedCriteria.forClass(Client.class);

criteria.add(Restrictions.in("clientId",getClientIds()) );

I created a seperate method getClientIds which gives back a list of clientIds. The problem occured when the number of clientIds returned by getClientIds method is more than 1000. IN operator against oracle fails with an exception if the number of items in the IN operator is > 1000. I got over this problem by replacing the clientId IN (id1, id2, id3,...id1000) kind of clause by clientId IN (innerQuery).

Y Cloneable?

We all have heard and quite often implemented and worked with Cloneable interface. Working with it quite recently encountered something that easily gets ignored while reading about Cloneable. Delving a bit deeper into it ,I figured out that if we have a basic requirment of creating a shallow copy of our custom class, why not define our own method with the name clone or can even technically override the one from Object's class, without calling the conventional super.clone() statement?

For eg: I have a class called User.

public class User{

private String firstName;

private String lastName;

private int age;

// a constructor to initialize an object

public User(String fName, String lName, int age){//code goes here}

Now if I need to return a clone of this object, I can always define/override the one from object class as

@Override

public Object clone(){

return new User(this.firstName, this.lastName, this.age);

}

My idea is that making your class implement Cloneable does not trigger anything automatic in the jvm. The semantics of this interface make sense, if you call Object's class clone method via super.clone(). Because Object's clone method, first makes a check if the calling class has implemented Cloneable , otherwise throws a checked exception CloneNotSupported. So implementing Cloneable interface for your class is more like adhearing to some java object oriented guidlines, saying that one can clone any object if its class has implemented this interface, else throw an exception. This kind of enforces a restriction on the callers, to carefully make a wise decision before making a clone of the object. But the other thing which comes to my mind is that, if I do not want others to make a copy of my object why would I need to expose a 'clone' like method in my class? This is because if I do not write any clone method in my User class above, compiler will not let you call user.clone() saying that "method clone from type Object is not visible". Then whole point of writing a clone method in my User class and to implement this restriction by throwing CloneNotSupportedException makes an additional exercise.

So the whole point is that if I really want to expose cloning of my objects, making a call to Object's clone would give me a template shallow copy of my object, which I could further extend as per requirment to create a deep copy. And to do so, I need to make my class implement Cloneable, otherwise a call to super.clone() would fail. So override clone whenever you seriosuly need to generate a copy of it.

Why local classes access only final variables?

Local classes in java are of big help and a nice utility to work with making your code more encapsulated and arranged. Anonymous local inner classes is a flavour that most of us use pretty often. Now there is a strong mandate that runs with this concept, they can use only final variables of the method in which they are declared. Now why 'final', and why not non-final variables?

The reason gets clear when we delve a bit deeper and find out how local classes works.

An anonymous local class can use local variables because the compiler automatically gives the class a private instance field to hold a copy of each local variable the class uses. The compiler also adds hidden parameters to each constructor to initialize these automatically created private fields. Thus, a local class does not actually access local variables, but merely its own private copies of them. The only way this can work correctly is if the local variables are declared final, so that they are guaranteed not to change. With this guarantee in place, the local class is assured that its internal copies of the variables accurately reflect the actual local variables.