Guice, JDBC and managing database connections

If your database change infrequently, you could use the data source that comes with the database's JDBC driver and isolate the calls to the 3rd party library in a provider (My example uses the one provided by the H2 dataabse, but all JDBC providers should have one). If you change to a different implementation of the DataSource (e.g. c3PO, Apache DBCP, or one provided by app server container) you can simply write a new Provider implementation to get the datasource from the appropriate place. Here I've use singleton scope to allow the DataSource instance to be shared amongst those classes that depend on it (necessary for pooling).

public class DataSourceModule extends AbstractModule {    @Override    protected void configure() {        Names.bindProperties(binder(), loadProperties());        bind(DataSource.class).toProvider(H2DataSourceProvider.class).in(Scopes.SINGLETON);        bind(MyService.class);    }    static class H2DataSourceProvider implements Provider<DataSource> {        private final String url;        private final String username;        private final String password;        public H2DataSourceProvider(@Named("url") final String url,                                    @Named("username") final String username,                                    @Named("password") final String password) {            this.url = url;            this.username = username;            this.password = password;        }        @Override        public DataSource get() {            final JdbcDataSource dataSource = new JdbcDataSource();            dataSource.setURL(url);            dataSource.setUser(username);            dataSource.setPassword(password);            return dataSource;        }    }    static class MyService {        private final DataSource dataSource;        @Inject        public MyService(final DataSource dataSource) {            this.dataSource = dataSource;        }        public void singleUnitOfWork() {            Connection cn = null;            try {                cn = dataSource.getConnection();                // Use the connection            } finally {                try {                    cn.close();                } catch (Exception e) {}            }        }    }    private Properties loadProperties() {        // Load properties from appropriate place...        // should contain definitions for:        // url=...        // username=...        // password=...        return new Properties();    }}

To handle transactions a Transaction Aware data source should be used. I wouldn't recommend implementing this manually. Using something like warp-persist or a container supplied transaction management, however it would look something like this:

public class TxModule extends AbstractModule {    @Override    protected void configure() {        Names.bindProperties(binder(), loadProperties());        final TransactionManager tm = getTransactionManager();        bind(DataSource.class).annotatedWith(Real.class).toProvider(H2DataSourceProvider.class).in(Scopes.SINGLETON);        bind(DataSource.class).annotatedWith(TxAware.class).to(TxAwareDataSource.class).in(Scopes.SINGLETON);        bind(TransactionManager.class).toInstance(tm);        bindInterceptor(Matchers.any(), Matchers.annotatedWith(Transactional.class), new TxMethodInterceptor(tm));        bind(MyService.class);    }    private TransactionManager getTransactionManager() {        // Get the transaction manager        return null;    }    static class TxMethodInterceptor implements MethodInterceptor {        private final TransactionManager tm;        public TxMethodInterceptor(final TransactionManager tm) {            this.tm = tm;        }        @Override        public Object invoke(final MethodInvocation invocation) throws Throwable {            // Start tx if necessary            return invocation.proceed();            // Commit tx if started here.        }    }    static class TxAwareDataSource implements DataSource {        static ThreadLocal<Connection> txConnection = new ThreadLocal<Connection>();        private final DataSource ds;        private final TransactionManager tm;        @Inject        public TxAwareDataSource(@Real final DataSource ds, final TransactionManager tm) {            this.ds = ds;            this.tm = tm;        }        public Connection getConnection() throws SQLException {            try {                final Transaction transaction = tm.getTransaction();                if (transaction != null && transaction.getStatus() == Status.STATUS_ACTIVE) {                    Connection cn = txConnection.get();                    if (cn == null) {                        cn = new TxAwareConnection(ds.getConnection());                        txConnection.set(cn);                    }                    return cn;                } else {                    return ds.getConnection();                }            } catch (final SystemException e) {                throw new SQLException(e);            }        }        // Omitted delegate methods.    }    static class TxAwareConnection implements Connection {        private final Connection cn;        public TxAwareConnection(final Connection cn) {            this.cn = cn;        }        public void close() throws SQLException {            try {                cn.close();            } finally {                TxAwareDataSource.txConnection.set(null);            }        }        // Omitted delegate methods.    }    static class MyService {        private final DataSource dataSource;        @Inject        public MyService(@TxAware final DataSource dataSource) {            this.dataSource = dataSource;        }        @Transactional        public void singleUnitOfWork() {            Connection cn = null;            try {                cn = dataSource.getConnection();                // Use the connection            } catch (final SQLException e) {                throw new RuntimeException(e);            } finally {                try {                    cn.close();                } catch (final Exception e) {}            }        }    }}

I would use something like c3po to create datasources directly. If you use ComboPooledDataSource you only need instance (pooling is done under the covers), which you can bind directly or through a provider.

Then I'd create an interceptor on top of that, one that e.g. picks up @Transactional, manages a connection and commit/ rollback. You could make Connection injectable as well, but you need to make sure you close the connections somewhere to allow them to be checked into the pool again.

1. To inject a data source, you probably don't need to be bound to a single data source instance since the database you are connecting to features in the url. Using Guice, it is possible to force programmers to provide a binding to a DataSource implementation (link) . This data source can be injected into a ConnectionProvider to return a data source.

2. The connection has to be in a thread local scope. You can even implement your thread local scope but all thread local connections must be closed & removed from ThreadLocal object after commit or rollback operations to prevent memory leakage. After hacking around, I have found that you need to have a hook to the Injector object to remove ThreadLocal elements. An injector can easily be injected into your Guice AOP interceptor, some thing like this:

    protected  void visitThreadLocalScope(Injector injector,                         DefaultBindingScopingVisitor visitor) {        if (injector == null) {            return;        }        for (Map.Entry, Binding> entry :                 injector.getBindings().entrySet()) {            final Binding binding = entry.getValue();            // Not interested in the return value as yet.            binding.acceptScopingVisitor(visitor);        }            }    /**     * Default implementation that exits the thread local scope. This is      * essential to clean up and prevent any memory leakage.     *      * 
The scope is only visited iff the scope is an sub class of or is an     * instance of {@link ThreadLocalScope}.     */    private static final class ExitingThreadLocalScopeVisitor             extends DefaultBindingScopingVisitor {        @Override        public Void visitScope(Scope scope) {                        // ThreadLocalScope is the custom scope.            if (ThreadLocalScope.class.isAssignableFrom(scope.getClass())) {                ThreadLocalScope threadLocalScope = (ThreadLocalScope) scope;                threadLocalScope.exit();            }            return null;        }    }

Make sure you call this after the method has been invoked and closing the connection. Try this to see if this works.