Guice, JDBC和管理数据库连接

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.

在学习Guice时，我希望创建一个示例项目，Guice使用JDBC对SQL数据库进行读写。然而，在使用了多年的Spring并让它抽象了连接处理和事务之后，我正在努力从概念上解决它。

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?)
• 如何让存储库访问连接?(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.

下面的代码显示了我在Spring中如何实现这个功能。注入每个存储库的JdbcOperations可以访问与活动事务关联的连接。

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)

我很高兴继续使用Spring，因为它在我的项目中工作得很好，但是我想知道如何在纯Guice和JBBC(没有JPA/Hibernate/Warp/ reuse 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";
  }
}

4 个解决方案

#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).

如果您的数据库不经常更改，您可以使用数据库JDBC驱动程序附带的数据源，并将对提供者中的第三方库的调用隔离(我的示例使用H2 dataabse提供的数据源，但所有JDBC提供程序都应该有一个)。如果您更改到数据源的不同实现(例如c3PO、Apache DBCP或app server容器提供的)，您只需编写一个新的提供者实现，从适当的位置获取数据源。在这里，我使用了singleton作用域，允许在依赖于它的类之间共享数据源实例(对于池是必要的)。

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

#2

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.

我将使用类似c3po的东西来直接创建数据源。如果您使用的是ComboPooledDataSource，那么您只需要instance(池是在幕后完成的)，您可以直接或通过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.

然后我会在上面创建一个拦截器，例如，获取@Transactional，管理连接并提交/回滚。您也可以将连接设置为可注入的，但是您需要确保在某处关闭连接，以便再次将它们检入池中。

#3

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.

   要注入数据源，可能不需要绑定到单个数据源实例，因为要连接到url中的特性的数据库。使用Guice，可以强制程序员提供到数据源实现(link)的绑定。可以将此数据源注入到ConnectionProvider中以返回数据源。

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:

   连接必须位于线程本地范围内。您甚至可以实现线程局部范围，但是所有线程本地连接都必须在提交或回滚操作之后关闭并从ThreadLocal对象中删除，以防止内存泄漏。在破解之后，我发现您需要一个到注入器对象的钩子来删除ThreadLocal元素。可以很容易地将注入到Guice AOP拦截器中，诸如此类的事情:

    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;
        }

    }
作用域仅被访问，作用域是or的子类

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

请确保在方法被调用并关闭连接之后调用此函数。试试这个，看看是否有效。

#4

Please check the solution I provided: Transactions with Guice and JDBC - Solution discussion

请检查我提供的解决方案:与Guice和JDBC解决方案讨论的事务

it is just a very basic version and simple approach. but it works just fine to handle transactions with Guice and JDBC.

这只是一个非常基本的版本和简单的方法。但是，使用Guice和JDBC处理事务是没问题的。