I’ve been working on a side project that is more a dynamic site than a web application like I’m used to do.
In this project, there is no need to have mutable states on objets, so there is no point in using Java beans and JPA is overkill for the task at hand.
So, we started with plain old JDBC and immutable objects where data is passed on to the constructor. That looks pretty much like this:
packagebe.noselus.repository;importbe.noselus.model.Assembly;importjavax.inject.Inject;importjavax.inject.Singleton;importjavax.sql.DataSource;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importjava.util.concurrent.ConcurrentHashMap;@SingletonpublicclassAssemblyRepositoryInDatabaseimplementsAssemblyRepository{privatefinalDataSourcedataSource;@InjectpublicAssemblyRepositoryInDatabase(finalDataSourcedataSource){this.dataSource=dataSource;}@OverridepublicAssemblyfindId(finalintid){try(Connectiondb=dataSource.getConnection();PreparedStatementstat=db.prepareStatement("SELECT * FROM assembly WHERE id = ?;")){stat.setInt(1,id);stat.execute();finalResultSetresultSet=stat.getResultSet();resultSet.next();returnassemblyMapping(resultSet);}catch(SQLExceptione){thrownewRuntimeException("Error retrieving assembly with id "+id,e);}}@OverridepublicList<Assembly>getAssemblies(){List<Assembly>result=newArrayList<>();try(Connectiondb=dataSource.getConnection();PreparedStatementstat=db.prepareStatement("SELECT * FROM assembly;")){stat.execute();finalResultSetresultSet=stat.getResultSet();while(resultSet.next()){Assemblyassembly=assemblyMapping(resultSet);result.add(assembly);}}catch(SQLExceptione){thrownewRuntimeException("Error retrieving assemblies",e);}returnresult;}privateAssemblyassemblyMapping(finalResultSetresultSet)throwsSQLException{finalintfoundId=resultSet.getInt("id");finalStringlabel=resultSet.getString("label");finalStringlevel=resultSet.getString("level");returnnewAssembly(foundId,label,Assembly.Level.valueOf(level));}}
It’s working, and it’s understandable, but there are a few try catches and some boiler plate code that I’m not very fond of, so I tried to find a tool to make it better.
There are quite a lot of tools to help there. Spring JDBC template, JDBI, Commons DbUtils, JOOQ, MyBatis, sql2o,…
Given I did not wanna do javabeans, I needed something pretty low level and not assuming the JavaBean convention was used, which odly does reduce quite significantly the pool of candidates.
Anyway, after looking around I decided to go for DbUtils. I started converting my Repository and ended up with this:
Out of the box DbUtils (AssemblyRepositoryPlainDbUtils.java)download
packagebe.noselus.repository;importbe.noselus.model.Assembly;importorg.apache.commons.dbutils.QueryRunner;importorg.apache.commons.dbutils.ResultSetHandler;importorg.apache.commons.dbutils.handlers.AbstractListHandler;importjavax.inject.Inject;importjavax.sql.DataSource;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.List;publicclassAssemblyRepositoryPlainDbUtilsimplementsAssemblyRepository{privatefinalDataSourcedataSource;@InjectpublicAssemblyRepositoryPlainDbUtils(finalDataSourcedataSource){this.dataSource=dataSource;}@OverridepublicAssemblyfindId(finalintid){QueryRunnerrun=newQueryRunner(dataSource);ResultSetHandler<Assembly>assemblyResultSetHandler=newAssemblyResultSetHandler();Assemblyassembly=null;try{assembly=run.query("SELECT * FROM assembly WHERE id = ?;",assemblyResultSetHandler,id);}catch(SQLExceptione){thrownewRuntimeException(e);}if(assembly==null){thrownewRuntimeException("Error retrieving assembly with id "+id);}returnassembly;}@OverridepublicList<Assembly>getAssemblies(){QueryRunnerrun=newQueryRunner(dataSource);ResultSetHandler<List<Assembly>>assemblyResultSetHandler=newAssemblyListHandler();try{returnrun.query("SELECT * FROM assembly;",assemblyResultSetHandler);}catch(SQLExceptione){thrownewRuntimeException(e);}}privatestaticclassAssemblyResultSetHandlerimplementsResultSetHandler<Assembly>{@OverridepublicAssemblyhandle(finalResultSetresultSet)throwsSQLException{if(!resultSet.next()){returnnull;}returnmap(resultSet);}}privatestaticclassAssemblyListHandlerextendsAbstractListHandler<Assembly>{@OverrideprotectedAssemblyhandleRow(finalResultSetresultSet)throwsSQLException{returnmap(resultSet);}}privatestaticAssemblymap(finalResultSetresultSet)throwsSQLException{finalintfoundId=resultSet.getInt("id");finalStringlabel=resultSet.getString("label");finalStringlevel=resultSet.getString("level");returnnewAssembly(foundId,label,Assembly.Level.valueOf(level));}}
Meh.
My implemented methods are a little bit shorter, but overall, I have more code, still have try catches, and two extra classes. Doesn’t look much simpler to me.
But then again, I had the feeling that lots of the extra code could be easily managed separatly and in a generic way.
So, I applied the main rule of TDD : mercyless refactoring.
I’ve got two ResultsetHandlers that are very close to each other and call the same method, so, I decided to turn this method into a function. As I’m still in Java 7 on that project, this means creating an interface with a single method.
ResultSet mappers will convert a ResultSet into an object (ResultSetMapper.java)download
123456789101112131415
packagebe.noselus.repository;importjava.sql.ResultSet;importjava.sql.SQLException;/** * Map a ResultSet into an instance of T * * @param <T> the typed object in which the content of the ResultSet should be converted. */publicinterfaceResultSetMapper<T>{Tmap(ResultSetresultSet)throwsSQLException;}
Next I will extract the two inner classes and make then generic enough so that I can reuse them for all my Repositories:
ResultsetHandler that will use a ResultSetMapper to convert a ResultSet to a typed object (MapperBasedResultSetHandler.java)download
This is much better. The code has shrinked already. The only annoying thing left is the try catches for the SQLExceptions.
To get rid of them, I decided to make an adapter, that will encapsulate the QueryRunner of DbUtils and do the try catches for me in order to convert checked exceptions into unchecked exceptions.
Adapt the Query Runner to avid checked exceptions and narrow down the API to my needs (QueryRunnerAdapter.java)download
packagebe.noselus.repository;importbe.noselus.model.Assembly;importorg.apache.commons.dbutils.ResultSetHandler;importjavax.inject.Inject;importjavax.sql.DataSource;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.List;publicclassAssemblyRepositoryDbUtilsimplementsAssemblyRepository,ResultSetMapper<Assembly>{privatefinalDataSourcedataSource;@InjectpublicAssemblyRepositoryDbUtils(finalDataSourcedataSource){this.dataSource=dataSource;}@OverridepublicAssemblyfindId(finalintid){QueryRunnerAdapterrun=newQueryRunnerAdapter(dataSource);ResultSetHandler<Assembly>assemblyResultSetHandler=newMapperBasedResultSetHandler<>(this);Assemblyassembly=run.query("SELECT * FROM assembly WHERE id = ?;",assemblyResultSetHandler,id);if(assembly==null){thrownewRuntimeException("Error retrieving assembly with id "+id);}returnassembly;}@OverridepublicList<Assembly>getAssemblies(){QueryRunnerAdapterrun=newQueryRunnerAdapter(dataSource);ResultSetHandler<List<Assembly>>assemblyResultSetHandler=newMapperBasedResultSetListHandler<>(this);returnrun.query("SELECT * FROM assembly;",assemblyResultSetHandler);}@OverridepublicAssemblymap(finalResultSetresultSet)throwsSQLException{finalintfoundId=resultSet.getInt("id");finalStringlabel=resultSet.getString("label");finalStringlevel=resultSet.getString("level");returnnewAssembly(foundId,label,Assembly.Level.valueOf(level));}}