使用ormlite有段时间了,这个一个非常好用的数据库orm工具包,非常适合曾经做过J2EE的人使用。
今天看了下ormlite的源码突然发现里面有个TransactionManager类,我一直苦于寻找一种事务执行批量语句的方法,以前写sql的时候经常要考虑事务安全问题,不过sql提供了非常好的事务支持,因此用起来也很方便,但是ormlite找了半天也没找到事务支持的资料,网上虽然能搜到一些,但是用在实际项目中之后会爆出多线程锁问题(数据库Connection的执行上一个事务的时候没有释放导致下一个事务执行的时候无法获取到数据库Connection),整理了下ormlite的事务使用方法具体有一下两种比较可靠的方案:
- 这种是网上找的,自己看了下ormlite源码后做了写修改,个人感觉比网上的更加可靠
private boolean doBatch(List<T> list, int batchType) {
int result = 0;
ConnectionSource connectionSource = ormLiteDao.getConnectionSource();
DatabaseConnection connection = null;
try {
connection = connectionSource.getReadWriteConnection();
connection.setAutoCommit(false);
for (T t : list) {
switch (batchType) {
case DaoOperation.INSERT:
result += ormLiteDao.create(t);
break;
case DaoOperation.DELETE:
result += ormLiteDao.delete(t);
break;
case DaoOperation.UPDATE:
result += updateIfValueNotNull(t);
break;
default:
LogUtil.w("no this type.");
break;
}
}
connection.commit(null);
} catch (SQLException e) {
LogUtil.e(e);
} finally {
if (connection != null) {
try {
connectionSource.releaseConnection(connection);
} catch (SQLException e) {
LogUtil.e(e);
}
}
}
return result == list.size();
}
- 这种是我看了下ormlite源码后自己写的代码,用的是ormlite的api来实现事务执行,比上一种更加可靠,现在我的项目中使用的就是这种方式,代码简洁,可靠性高
private boolean doBatchInTransaction(final List<T> list, final int batchType) {
boolean doBatch = false;
ConnectionSource connectionSource = ormLiteDao.getConnectionSource();
TransactionManager transactionManager = new TransactionManager(connectionSource);
Callable<Boolean> callable = new Callable<Boolean>() {
@Override
public Boolean call() throws Exception {
return doBatch(list, batchType);
}
};
try {
doBatch = transactionManager.callInTransaction(callable);
} catch (SQLException e) {
LogUtil.e(e);
}
return doBatch;
}
private boolean doBatch(List<T> list, int batchType) {
int result = 0;
try {
for (T t : list) {
switch (batchType) {
case DaoOperation.INSERT:
result += ormLiteDao.create(t);
break;
case DaoOperation.DELETE:
result += ormLiteDao.delete(t);
break;
case DaoOperation.UPDATE:
result += updateIfValueNotNull(t);
break;
default:
LogUtil.w("no this type.");
break;
}
}
} catch (SQLException e) {
LogUtil.e(e);
}
return result == list.size();
}
- 解读ormlite的TransactionManager源码
public class TransactionManager {
private static final Logger logger = LoggerFactory.getLogger(TransactionManager.class);
private static final String SAVE_POINT_PREFIX = "ORMLITE";
private ConnectionSource connectionSource;
private static AtomicInteger savePointCounter = new AtomicInteger();
public TransactionManager() {
}
public TransactionManager(ConnectionSource connectionSource) {
this.connectionSource = connectionSource;
this.initialize();
}
public void initialize() {
if(this.connectionSource == null) {
throw new IllegalStateException("dataSource was not set on " + this.getClass().getSimpleName());
}
}
public <T> T callInTransaction(Callable<T> callable) throws SQLException {
return callInTransaction(this.connectionSource, callable);
}
public static <T> T callInTransaction(ConnectionSource connectionSource, Callable<T> callable) throws SQLException {
DatabaseConnection connection = connectionSource.getReadWriteConnection();
Object var4;
try {
boolean saved = connectionSource.saveSpecialConnection(connection);
var4 = callInTransaction(connection, saved, connectionSource.getDatabaseType(), callable);
} finally {
connectionSource.clearSpecialConnection(connection);
connectionSource.releaseConnection(connection);
}
return var4;
}
public static <T> T callInTransaction(DatabaseConnection connection, DatabaseType databaseType, Callable<T> callable) throws SQLException {
return callInTransaction(connection, false, databaseType, callable);
}
public static <T> T callInTransaction(DatabaseConnection connection, boolean saved, DatabaseType databaseType, Callable<T> callable) throws SQLException {
boolean autoCommitAtStart = false;
Object e2;
try {
boolean hasSavePoint = false;
Savepoint savePoint = null;
if(saved || databaseType.isNestedSavePointsSupported()) {
if(connection.isAutoCommitSupported()) {
autoCommitAtStart = connection.isAutoCommit();
if(autoCommitAtStart) {
connection.setAutoCommit(false);
logger.debug("had to set auto-commit to false");
}
}
savePoint = connection.setSavePoint("ORMLITE" + savePointCounter.incrementAndGet());
if(savePoint == null) {
logger.debug("started savePoint transaction");
} else {
logger.debug("started savePoint transaction {}", savePoint.getSavepointName());
}
hasSavePoint = true;
}
try {
Object e = callable.call();
if(hasSavePoint) {
commit(connection, savePoint);
}
e2 = e;
} catch (SQLException var17) {
if(hasSavePoint) {
try {
rollBack(connection, savePoint);
} catch (SQLException var15) {
logger.error(var17, "after commit exception, rolling back to save-point also threw exception");
}
}
throw var17;
} catch (Exception var18) {
if(hasSavePoint) {
try {
rollBack(connection, savePoint);
} catch (SQLException var16) {
logger.error(var18, "after commit exception, rolling back to save-point also threw exception");
}
}
throw SqlExceptionUtil.create("Transaction callable threw non-SQL exception", var18);
}
} finally {
if(autoCommitAtStart) {
connection.setAutoCommit(true);
logger.debug("restored auto-commit to true");
}
}
return e2;
}
public void setConnectionSource(ConnectionSource connectionSource) {
this.connectionSource = connectionSource;
}
private static void commit(DatabaseConnection connection, Savepoint savePoint) throws SQLException {
String name = savePoint == null?null:savePoint.getSavepointName();
connection.commit(savePoint);
if(name == null) {
logger.debug("committed savePoint transaction");
} else {
logger.debug("committed savePoint transaction {}", name);
}
}
private static void rollBack(DatabaseConnection connection, Savepoint savePoint) throws SQLException {
String name = savePoint == null?null:savePoint.getSavepointName();
connection.rollback(savePoint);
if(name == null) {
logger.debug("rolled back savePoint transaction");
} else {
logger.debug("rolled back savePoint transaction {}", name);
}
}
}