A programmer's tale

Thursday, April 19, 2007

Hadling views in JDBC: better way :)

I've been programming in JDBC for last few months. During this course of programming I'm happy and at the same time unhappy with this API.
Happy because it's just vendor independent. You write your program and run anywhere with modifying the code.
But this are several very annoying issues I've encounter during JDBC programming.
Specially I'm uncomfortable with the DatabaseMetaData in JDBC. It does not provide and abstraction over the ResultSet. There are good and bad of that. But from a programmer point of view, I'm very unhappy with handling ResultSet and it's long list of getters . SQLException is another annoyance to me. Every time I have to handle it or bubble up. But as far as SQLException is concerned I don't think the exception is so much important to another programmer who is going to use your API. Instead I like to write robust fallback code if SQLException occurs. For all these I have written better abstraction of JDBC metadata by which a programmer can retrieve database objects like catalogs, schemas, table etc. more object-oriented way. I will post the API designs, usages etc. of my abstraction later .
For now I'll mention some tricks of handling database views in JDBC. If you are programming in MySQL4.0 with JDBC, you will find that you get all the tables present in the database if you want to retrieve the views. Actually MySQL 4.0 does not support views. But you get tables as views that is not expected. Note some other databases don't support views. Now how to handle this better way:

For getting view you need to call
ResultSet getTables(String catalog, String schemaPattern,
String tableNamePattern, String types[]) throws SQLException in DatabaseMetaData.
But you need to double check that the underlying database really supports views or not.
Now first call ResultSet getTableTypes() throws SQLException to get the table types supported by the database. From the returning ResultSet get the String array that contains the supported table types.
Write a utility method that returns true if an array contains all the elements of another array i.e. if an array be a subset of another array like:

public static boolean containsAll(Object[] array, Object[] arrayToFind) {
for (Object objectToFind : arrayToFind) {
if (!org.apache.commons.lang.ArrayUtils.contains(array, objectToFind))
return false;
}

return true;
}

You can also write little hepler method like :

private boolean supportsView() {
return containsAll(tableTyeps, {"VIEW"});
}

where tableTypes is a array of String before by calling getTableTypes() . like {"VIEW", "TABLE", "SYSTEM TABLE"} etc.

Now before actually calling the getTables(....) method on DatabaseMetaData call the above helper method. If it returns false, then the database does not support views. Hence you can easy return empty array without hitting the database. I have tested this technique several places and it just fly.