My Nifty Idea

I have had a cool idea floating at the back of my mind for a little while and today as I have work I really don't want to do I worked out how to make it work and have thrown together a prototype. So first some background. I find myself writing a fair amount of database code. A lot of this is simple wrappers around a oracle package. The JDBC gods demand a fair amount of boiler plate. Even without going all the way to a ORM tool like Hibernate I know APIs exist to reduce said boilerplate but I've always felt (rightly or not) that I'd still end up doing a fair bit of boilerplate code even so or they were tied to an API I don't already use (i.e. Spring JDBC is slightly tried to Spring). I know the proper answer is likely to bite the bullet and use Spring. But sod that, I want to roll my own.
Mmm. Sorry slipping into irrelevant babbling. What I want out of an API is to define an interface and have that magically fill in the blanks. So for example if I define:

public interface Queue { 
    int getCount();
}

then all I'd have to do would be add some annotations to tell the Magic what I want and it would go ahead and make it happen. So today I created a prototype.
First the annotation:

package playtest;

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;

@Retention(RetentionPolicy.RUNTIME)
public @interface Execute { 
   String script();
}
You then annotate the method you want to be magical.

package playtest;

public interface Queue {

   @Execute(script="{? = call Queue.getCount}")
   Integer getCount();
}

Next the magic. Note: I'm using a Guice provider to get hold of the database. I'm not 100% that is the right way to go as while I'm very likely to use Guice do I really want to tie the implementation down to Guice? This play test of an implementation can only return void or Integer. If I was to go forward with this I'd have to get it to be able to return all primitive types at least and ideally be able to marshal complex types. Likely via annotations in the complex types. Maybe what I really want is JPA.

package playtest;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.google.inject.Provider;

public class QuickAndDirtySQLHandler implements InvocationHandler {
private final Logger log = Logger.getLogger(getClass().getName());
private final Provider connectionProvider;

public QuickAndDirtySQLHandler(final Provider connectionProvider) {
this.connectionProvider = connectionProvider;
}

@Override
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
Execute execute = method.getAnnotation(Execute.class);
if (execute == null) {
throw new IllegalStateException("Required annotation missing!");
}

boolean returnsValue = false;

if (method.getReturnType().equals(Integer.class)) {
returnsValue = true;
}

Connection connection = connectionProvider.get();
CallableStatement statement = null;

try {
statement = connection.prepareCall(execute.script());
if (returnsValue) {
statement.registerOutParameter(1, Types.INTEGER);
}
statement.execute();

if (returnsValue) {
return statement.getInt(1);
} else {
return null;
}

} finally {

close(statement);
connection.close();
}


}

private void close(Statement s) {
if (s == null) {
return;
}
try {
s.close();
} catch(SQLException sqle) {
log.log(Level.WARNING, "Failed to close statement", sqle);
}
}
}
Next a helper class to handle database access.

package playtest.db;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

import com.google.inject.Provider;

public class ExampleDatabaseProvider implements Provider{

  private final BasicDataSource ds = new BasicDataSource();

  public ExampleDatabaseProvider(String driver, String username,
     String password, String url) {
    ds.setDriverClassName(driver);
    ds.setUsername(username);
    ds.setPassword(password);
    ds.setUrl(url);
    ds.setMaxActive(1);
    ds.setDefaultAutoCommit(false);
  }

  @Override
  public Connection get() {
    try {
     return ds.getConnection();
    } catch(SQLException sqle) {
    // Switch to a throwing provider?
     throw new IllegalStateException(sqle);
    }
  }

  public void close() throws SQLException {
    ds.close();
  }
}
And finally some magic:

package playtest;

import java.lang.reflect.Proxy;

import playtest.db.ExampleDatabaseProvider;

public class KickStart {
  public static void main(String argv[]) throws Exception {
    ExampleDatabaseProvider dbProvider = 
      new ExampleDatabaseProvider(
        "jdbc.driver","username", "password", "jdbc:database");

    Queue example = (Queue)Proxy.newProxyInstance(
      KickStart.class.getClassLoader(),
      new Class[] {Queue.class},
      new QuickAndDirtySQLHandler(dbProvider));

   System.out.println(example.getCount());

   dbProvider.close();
  }
}
I think that would be much better than raw JDBC for projects that demand the use of stored procedures (like all projects at my current employer). I should really look at how JPA handles forced use of stored procedures.

I think this is a cool idea and I plan to run with it (if I can find the time). I'll post updates here under the tag magic-procedures. The GitHub site is: http://github.com/mlk/magic-procedures and the public repository is git@github.com:mlk/magic-procedures.git