ActiveJDBC batch insert and transaction

Question

What is the recommended way to insert a batch of records or none if the database raises an error for any of the inserts? Here is my current code:

PreparedStatement ps = Base.startBatch("INSERT INTO table(col1) VALUES(?)");
for (MyModel m : myModels)
    Base.addBatch(ps, m.getCol1());
Base.executeBatch(ps);
ps.close();

This inserts records until the first one that fails (if happens).
I want all or nothing to be inserted, then I was thinking of wrapping the executeBatch():

Base.openTransaction();
Base.executeBatch(ps);
Base.commitTransaction();

If it is correct, should I do Base.rollbackTransaction() in some try catch?
Should I also close the ps.close() in a finally block?

Thanks!

score 0 · Accepted Answer · answered Sep 17 '15 at 21:24

Transacted batch operations are not any different from non-batch operations. Please, see this: http://javalite.io/transactions#transacted-activejdbc-example for a typical pattern.

You will do this then:

List<Person> myModels = new ArrayList<>();
    try{
        Base.openTransaction();
        PreparedStatement ps = Base.startBatch("INSERT INTO table(col1) VALUES(?)");
        for (Person m : myModels){
            Base.addBatch(ps, m.getCol1());
        }
        Base.executeBatch(ps);
        ps.close();
        Base.commitTransaction();
    }catch(Exception e){
        Base.rollbackTransaction();
    }

This way, your data is intact in case of exceptions

But in your example `ps.close()` won't be reached if there is an exception, is that ok? — Maxime Laval, Sep 17 '15 at 22:01
sure, this is just pseudo-code. You will need a `finally` block to close resources, connection including — ipolevoy, Sep 18 '15 at 01:51

ActiveJDBC batch insert and transaction

1 Answers1