Guice, JDBC and managing database connections

Question

I'm looking to create a sample project while learning Guice which uses JDBC to read/write to a SQL database. However, after years of using Spring and letting it abstract away connection handling and transactions I'm struggling to work it our conceptually.

I'd like to have a service which starts and stops a transaction and calls numerous repositories which reuse the same connection and participate in the same transaction. My questions are:

• Where do I create my Datasource?
• How do I give the repositories access to the connection? (ThreadLocal?)
• Best way to manage the transaction (Creating an Interceptor for an annotation?)

The code below shows how I would do this in Spring. The JdbcOperations injected into each repository would have access to the connection associated with the active transaction.

I haven't been able to find many tutorials which cover this, beyond ones which show creating interceptors for transactions.

I am happy with continuing to use Spring as it is working very well in my projects, but I'd like to know how to do this in pure Guice and JBBC (No JPA/Hibernate/Warp/Reusing Spring)

@Service public class MyService implements MyInterface {    @Autowired   private RepositoryA repositoryA;   @Autowired   private RepositoryB repositoryB;   @Autowired   private RepositoryC repositoryC;     @Override   @Transactional   public void doSomeWork() {     this.repositoryA.someInsert();     this.repositoryB.someUpdate();     this.repositoryC.someSelect();     }     }  @Repository public class MyRepositoryA implements RepositoryA {    @Autowired   private JdbcOperations jdbcOperations;    @Override   public void someInsert() {     //use jdbcOperations to perform an insert   } }  @Repository public class MyRepositoryB implements RepositoryB {    @Autowired   private JdbcOperations jdbcOperations;    @Override   public void someUpdate() {     //use jdbcOperations to perform an update   } }  @Repository public class MyRepositoryC implements RepositoryC {    @Autowired   private JdbcOperations jdbcOperations;    @Override   public String someSelect() {     //use jdbcOperations to perform a select and use a RowMapper to produce results     return "select result";   } } 

Answer 1

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) {}             }         }     } }