Java JOOQ和交易
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20046247/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JOOQ & transactions
提问by assylias
I've been reading about transactions & jooqbut I struggle to see how to implement it in practice.
我一直在阅读有关交易和 jooq 的内容，但我很难看到如何在实践中实现它。
Let's say I provide JOOQ with a custom ConnectionProviderwhich happens to use a connection pool with autocommit set to false.
假设我为 JOOQ 提供了一个自定义ConnectionProvider，它碰巧使用自动提交设置为 false 的连接池。
The implementation is roughly:
实现大致如下：
@Override public Connection acquire() throws DataAccessException {
return pool.getConnection();
}
@Override public void release(Connection connection) throws DataAccessException {
connection.commit();
connection.close();
}
How would I go about wrapping two jooq queries into a single transaction?
我将如何将两个 jooq 查询包装到一个事务中？
It is easy with the DefaultConnectionProvider because there's only one connection - but with a pool I'm not sure how to go about it.
使用 DefaultConnectionProvider 很容易，因为只有一个连接 - 但是使用池我不知道如何去做。
采纳答案by Lukas Eder
jOOQ 3.4 Transaction API
jOOQ 3.4 事务 API
With jOOQ 3.4, a transaction APIhas been added to abstract over JDBC, Spring, or JTA transaction managers. This API can be used with Java 8 as such:
在 jOOQ 3.4 中，添加了事务 API以抽象 JDBC、Spring 或 JTA 事务管理器。此 API 可用于 Java 8，如下所示：
DSL.using(configuration)
.transaction(ctx -> {
DSL.using(ctx)
.update(TABLE)
.set(TABLE.COL, newValue)
.where(...)
.execute();
});
Or with pre-Java 8 syntax
或者使用 Java 8 之前的语法
DSL.using(configuration)
.transaction(new TransactionRunnable() {
@Override
public void run(Configuration ctx) {
DSL.using(ctx)
.update(TABLE)
.set(TABLE.COL, newValue)
.where(...)
.execute();
}
});
The idea is that the lambda expression (or anonymous class) form the transactional code, which:
这个想法是 lambda 表达式（或匿名类）形成事务代码，其中：
- Commits upon normal completion
- Rolls back upon exception
- 正常完成时提交
- 在异常时回滚
The org.jooq.TransactionProviderSPIcan be used to override the default behaviour, which implements nestable transactions via JDBC using Savepoints.
该org.jooq.TransactionProviderSPI可用于覆盖默认行为，它通过JDBC使用实现嵌套事务Savepoints。
A Spring example
一个春天的例子
The current documentation shows an example when using Spring for transaction handling:
当前文档显示了使用 Spring 进行事务处理时的示例：
This example essentially boils down to using a Spring TransactionAwareDataSourceProxy
这个例子基本上归结为使用 Spring TransactionAwareDataSourceProxy
<!-- Using Apache DBCP as a connection pooling library.
Replace this with your preferred DataSource implementation -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
init-method="createDataSource" destroy-method="close">
<property name="driverClassName" value="org.h2.Driver" />
<property name="url" value="jdbc:h2:~/maven-test" />
<property name="username" value="sa" />
<property name="password" value="" />
</bean>
<!-- Using Spring JDBC for transaction management -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="transactionAwareDataSource"
class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
<constructor-arg ref="dataSource" />
</bean>
<!-- Bridging Spring JDBC data sources to jOOQ's ConnectionProvider -->
<bean class="org.jooq.impl.DataSourceConnectionProvider"
name="connectionProvider">
<constructor-arg ref="transactionAwareDataSource" />
</bean>
A running example is available from GitHub here:
GitHub 上提供了一个运行示例：
A Spring and Guice example
Spring 和 Guice 示例
Although I personally wouldn't recommend it, some users have had success replacing a part of Spring's DI by Guice and handle transactions with Guice. There is also an integration-tested running example on GitHub for this use-case:
虽然我个人不会推荐它，但一些用户已经成功地用 Guice 替换了 Spring 的一部分 DI 并使用 Guice 处理事务。GitHub 上还有一个经过集成测试的运行示例，用于此用例：
回答by assylias
This is probably not the best way but it seems to work. The caveat is that it is not the releasebut the commitmethod which closes the connection and returns it to the pool, which is quite confusing and could lead to issues if some code "forgets" to commit...
这可能不是最好的方法，但它似乎有效。需要说明的是，它不是release，但commit它关闭连接，并将其返回到池，这是相当混乱，并可能导致问题，如果一些代码“忘记”提交方法...
So the client code looks like:
所以客户端代码看起来像：
final PostgresConnectionProvider postgres =
new PostgresConnectionProvider("localhost", 5432, params.getDbName(), params.getUser(), params.getPass())
private static DSLContext sql = DSL.using(postgres, SQLDialect.POSTGRES, settings);
//execute some statements here
sql.execute(...);
//and don't forget to commit or the connection will not be returned to the pool
PostgresConnectionProvider p = (PostgresConnectionProvider) sql.configuration().connectionProvider();
p.commit();
And the ConnectionProvider:
和 ConnectionProvider：
public class PostgresConnectionProvider implements ConnectionProvider {
private static final Logger LOG = LoggerFactory.getLogger(PostgresConnectionProvider.class);
private final ThreadLocal<Connection> connections = new ThreadLocal<>();
private final BoneCP pool;
public PostgresConnectionProvider(String serverName, int port, String schema, String user, String password) throws SQLException {
this.pool = new ConnectionPool(getConnectionString(serverName, port, schema), user, password).pool;
}
private String getConnectionString(String serverName, int port, String schema) {
return "jdbc:postgresql://" + serverName + ":" + port + "/" + schema;
}
public void close() {
pool.shutdown();
}
public void commit() {
LOG.debug("Committing transaction in {}", Thread.currentThread());
try {
Connection connection = connections.get();
if (connection != null) {
connection.commit();
connection.close();
connections.set(null);
}
} catch (SQLException ex) {
throw new DataAccessException("Could not commit transaction in postgres pool", ex);
}
}
@Override
public Connection acquire() throws DataAccessException {
LOG.debug("Acquiring connection in {}", Thread.currentThread());
try {
Connection connection = connections.get();
if (connection == null) {
connection = pool.getConnection();
connection.setAutoCommit(false);
connections.set(connection);
}
return connection;
} catch (SQLException ex) {
throw new DataAccessException("Can't acquire connection from postgres pool", ex);
}
}
@Override
//no-op => the connection won't be released until it is commited
public void release(Connection connection) throws DataAccessException {
LOG.debug("Releasing connection in {}", Thread.currentThread());
}
}
回答by CodePredator
Easiest way,(I have found) to use Spring Transactions with jOOQ, is given here: http://blog.liftoffllc.in/2014/06/jooq-and-transactions.html
最简单的方法，（我发现）使用带有 jOOQ 的 Spring 事务，这里给出：http://blog.liftoffllc.in/2014/06/jooq-and-transactions.html
Basically we implement a ConnectionProviderthat uses org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(ds)method to find and return the DB connection that holds transaction created by Spring.
基本上我们实现了一个ConnectionProvider使用org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(ds)方法来查找并返回保存由 Spring 创建的事务的数据库连接。
Create a TransactionManagerbean for your DataSource, example shown below:
TransactionManager为您创建一个bean DataSource，示例如下所示：
<bean
id="dataSource"
class="org.apache.tomcat.jdbc.pool.DataSource"
destroy-method="close"
p:driverClassName="com.mysql.jdbc.Driver"
p:url="mysql://locahost:3306/db_name"
p:username="root"
p:password="root"
p:initialSize="2"
p:maxActive="10"
p:maxIdle="5"
p:minIdle="2"
p:testOnBorrow="true"
p:validationQuery="/* ping */ SELECT 1"
/>
<!-- Configure the PlatformTransactionManager bean -->
<bean
id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
p:dataSource-ref="dataSource"
/>
<!-- Scan for the Transactional annotation -->
<tx:annotation-driven/>
Now you can annotate all the classes or methods which uses jOOQ's DSLContextwith
现在，您可以注释所有使用jOOQ的类或方法DSLContext有
@Transactional(rollbackFor = Exception.class)
And while creating the DSLContextobject jOOQ will make use of the transaction created by Spring.
在创建DSLContext对象时，jOOQ 将使用 Spring 创建的事务。
回答by user3865770
Though its an old question, Please look at this link to help configure JOOQ to use spring provided transaction manager. Your datasource and DSLContext have to be aware of Transacation.
虽然这是一个老问题，请查看此链接以帮助配置 JOOQ 以使用 spring 提供的事务管理器。您的数据源和 DSLContext 必须了解交易。
https://www.baeldung.com/jooq-with-spring
https://www.baeldung.com/jooq-with-spring
You may have to change
你可能需要改变
@Bean
public DefaultDSLContext dsl() {
????return new DefaultDSLContext(configuration());
}
to
到
@Bean
public DSLContext dsl() {
????return new DefaultDSLContext(configuration());
}
