Java 将事务与 JDBI/IDBI/Dropwizard 一起使用——回滚问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20794997/
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
Using Transaction with JDBI / IDBI / Dropwizard -- rollback problems
提问by jcity
I'm having a lot of trouble getting transactions to work with IDBI. We're using the dropwizard framework and simple inserts, updates, selects, and deletes have worked find but now we cannot seem to get the transactions to work correctly. Here is what I'm trying
我在使事务与 IDBI 一起工作时遇到了很多麻烦。我们正在使用 dropwizard 框架，简单的插入、更新、选择和删除都可以找到，但现在我们似乎无法让事务正常工作。这是我正在尝试的
public class JDb {
private JustinTest2 jTest2 = null;
private Handle dbHandle = null;
public JDb(final IDBI idbi) {
try {
dbHandle = idbi.open();
dbHandle.getConnection().setAutoCommit(false);
jTest2 = dbHandle.attach(JustinTest2.class);
} catch( SQLException e ) {
}
}
public void writeJustin(final int styleId, final int eventId) {
dbHandle.begin();
int num = jTest2.findByStyleId(styleId);
try {
jTest2.doStuff(styleId, eventId);
dbHandle.commit();
} catch(Exception e) {
dbHandle.rollback(); // Never rolls back here, always get the inserted row!
}
num = jTest2.findByStyleId(styleId);
}
}
And here is my JustinTest2 class
这是我的 JustinTest2 课程
public abstract class JustinTest2 {
@SqlUpdate("INSERT INTO jTest2 (styleId, jNum) VALUES (:styleId, :jNum)")
public abstract void insert(@Bind("styleId") int styleId, @Bind("jNum") int jNum);
@SqlQuery("SELECT count(styleId) " +
"FROM jTest2 " +
"WHERE styleId=:styleId")
public abstract int findByStyleId(@Bind("styleId") int styleId);
public int doStuff(int styleId, int eventId) throws Exception{
int count = findByStyleId(styleId);
insert(styleId, eventId);
count = findByStyleId(styleId);
if(count==1) {
throw new Exception("Roll back");
}
return count;
}
}
I've also tried implementing writeJustin like :
我也试过像这样实现 writeJustin：
public void writeJustin(final int styleId, final int eventId) throws Exception {
int rows_updated = jTest2.inTransaction(new Transaction<Integer, JustinTest2>() {
@Override
public Integer inTransaction(JustinTest2 transactional, TransactionStatus status) throws Exception {
jTest2.insert(styleId, eventId);
int num = transactional.findByStyleId(styleId);
try {
if(num == 1) throw new Exception("BOOM");
} catch (Exception e) {
transactional.rollback();
throw e;
}
num = transactional.findByStyleId(styleId);
return num;
}
});
}
I cannot seem to get the transaction to rollback, in each of these ways the inserted row is always there after the rollback, whether I try directly through the handle or whether I use inTransaction (which from my understanding should not commit the transaction if an exception is thrown within the call back) Anyone have any idea what I might be doing wrong?
我似乎无法让事务回滚，在每一种方式中，插入的行在回滚后始终存在，无论我是直接通过句柄尝试还是使用 inTransaction （根据我的理解，如果出现异常，则不应提交事务在回调中抛出）有人知道我可能做错了什么吗？
采纳答案by jcity
I figured this out. It turns out the table I was testing was using MyISAM and not InnoDB as the storage engine. MyISAM does not support transactions. I rebuilt the table using InnoDB and the code above worked fine.
我想通了。事实证明，我正在测试的表使用 MyISAM 而不是 InnoDB 作为存储引擎。MyISAM 不支持事务。我使用 InnoDB 重建了表，上面的代码运行良好。
For anyone who doesn't know you can see which engine a table is using by using:
对于不知道的人，您可以使用以下命令查看表正在使用哪个引擎：
show create table <tablename>;
Should see something like:
应该看到类似的东西：
CREATE TABLE `grades` (
`id` int(11) NOT NULL,
`percent` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
回答by Patrick M
This is tangential to your question, but I'm adding it as an answer because your question is high on the Google results and there aren't a lot of examples of it out there.
这与您的问题无关，但我将其添加为答案，因为您的问题在 Google 结果中排名靠前，并且没有很多示例。
With JDBI v2, you can use the @Transactionannotationto simplify your code. Just decorate the public method with the annotation and JDBI will handle the begin, commit and rollback behind the scenes.
使用 JDBI v2，您可以使用@Transaction注释来简化代码。只需用注解修饰公共方法，JDBI 就会在后台处理开始、提交和回滚。
public abstract class JustinTest2 {
@SqlUpdate("INSERT INTO jTest2 (styleId, jNum) VALUES (:styleId, :jNum)")
protected abstract void insert(@Bind("styleId") int styleId, @Bind("jNum") int jNum);
@SqlQuery("SELECT count(styleId) " +
"FROM jTest2 " +
"WHERE styleId=:styleId")
protected abstract int findByStyleId(@Bind("styleId") int styleId);
@Transaction
public int doStuff(int styleId, int eventId) throws Exception{
int count = findByStyleId(styleId);
insert(styleId, eventId);
count = findByStyleId(styleId);
if(count==1) {
throw new Exception("Roll back");
}
return count;
}
}
Note that I made the insertand findByStyleIdmethods protected; down from publicto enforce they be done together in a transaction (in the public doStuffmethod); not privatebecause the JDBI auto-generated implementation would not be able to override them (having methods be private abstractdoesn't work for that reason - you'd be forcing the compiler to accept a method without a body).
请注意，我对insert和findByStyleId方法进行了保护；down frompublic强制它们在事务中一起完成（在公共doStuff方法中）；不是private因为 JDBI 自动生成的实现将无法覆盖它们（private abstract因为这个原因，有方法不起作用 - 你会强迫编译器接受没有主体的方法）。
You can also specify a TransactionIsolationLevelin the annotation to override your database's defaults.
您还可以TransactionIsolationLevel在注释中指定 a以覆盖数据库的默认值。
@Transaction(TransactionIsolationLevel.REPEATABLE_READ)
