ormlite的事务使用

使用ormlite有段时间了，这个一个非常好用的数据库orm工具包，非常适合曾经做过J2EE的人使用。

今天看了下ormlite的源码突然发现里面有个TransactionManager类，我一直苦于寻找一种事务执行批量语句的方法，以前写sql的时候经常要考虑事务安全问题，不过sql提供了非常好的事务支持，因此用起来也很方便，但是ormlite找了半天也没找到事务支持的资料，网上虽然能搜到一些，但是用在实际项目中之后会爆出多线程锁问题（数据库Connection的执行上一个事务的时候没有释放导致下一个事务执行的时候无法获取到数据库Connection），整理了下ormlite的事务使用方法具体有一下两种比较可靠的方案：

1. 这种是网上找的，自己看了下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();
    }

1. 这种是我看了下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();
    }

1. 解读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 {
        //释放数据库连接，releaseConnection我看了里面是个空实现，也不知道为啥，没有具体深究是否在其他地方有具体实现，我看ormlite其他类的用法也都调用releaseConnection方法，就按照它的源码去用吧，不会错
            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 {
            // 不要看这个是采用Callable执行的就以为是异步，依然是同步，因为它直接调用call方法
                Object e = callable.call();
                if(hasSavePoint) {
                //提交事务，只有这句执行完了之后，你所做的操作才会在数据库中生效
                    commit(connection, savePoint);
                }

                e2 = e;
            } catch (SQLException var17) {
            // 出现异常就进行回滚，回滚到执行事务前的状态savePoint
                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) {
            /*1.如果连接支持自动提交在事务执行完了之后才设置自动提交，我觉的这么做的原因是
            为了更加保险，让事务执行更加有效，保证事务执行了之后立刻就进行提交生效
            2.其实我有一种猜测就是上面的commit方法可能不是真正的事务提交生效，而仅仅是把
全部执行完，而避免每个操作执行完了之后就提交一次，这样太耗时，效率低，等所有批量操作都执行完
了之后，提交到数据库连接，在finally里面在setAutoCommit(true)为自动提交，这时候就只要把
刚才的批量操作一次性全部提交到数据库并生效，这样使得批量操作只要在最后执行一次，效率比较高，
按照我以前用sql语句的理解是beginTransaction,endTransaction,commit，只要commit之后
然后事务执行就生效了*/
                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);
        }

    }
}