SQL Transactions in Java

1. Rollback all data on import from a table if a row is corrupted.You have a list of sql queries as a parameter:
– create database connection:

Connection connection = Database.getInstance().getConnection();

– set autocommit on false :

connection.setAutoCommit(false);

(read note1*)
– declare a boolean variable that will be set on true if all is going according to the plan and no errors found

boolean executed = true;

– execute queries and ‘find’ out what worked and what failed:

try {
for(String sql : listOfSQLQueries) {
result = stmt.executeUpdate(sql);
if(result == 0) {
executed = false;
break;
} else {
executed = true;
}
}
} catch (Exception e) {
executed = false;
}
 
// if error found, rollback:
if (!executed) {
connection.rollback();
} else {
connection.commit();
}

*Note1:remember that statement.close() will ignore the connection.setAutocommit(false) and will execute and commit the sql.

2. Rollback using Savepoints:

Savepoint savePoint = null;
try {
savePoint = connection.setSavepoint("dummySavepoint");
dummy_sql = "SELECT * FROM " + databaseSchema + "." + table + " WHERE " + databaseSchema + "." + tableName + "."
+ id + " = '" + id + "'";
// execute logic
}
// catch block
...
connection.rollback("dummySavepoint") // to rollback to our above declared savepoint
connection.releaseSavepoint("dummySavepoint") // to release the savepoint</span>

Note2: A simple rollback or commit erases all savepoints.

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.

WP Themes says:

Nice brief and this enter helped me alot in my college assignement. Thanks you on your information.

June 30, 2010 at 3:51 pm

javaexpert says:

You say: ‘set autocommit on false ‘ yet you set it to ‘true’ (connection.setAutoCommit(true);). Actually it should be done like this: first you set autocommit to false and right after you do the connection.commit(); you set it to true again:

Connection connection = Database.getInstance().getConnection();
– set autocommit on false :
connection.setAutoCommit(false);

try {
for(String sql : listOfSQLQueries) {
result = stmt.executeUpdate(sql);
if(result == 0) {
executed = false;
break;
} else {
executed = true;
}
}
} catch (Exception e) {
executed = false;
}
– if error found, rollback:
if (!executed) {
connection.rollback();
} else {
connection.commit();
connection.setAutoCommit(true);
}

July 13, 2010 at 1:46 pm

You say: \’set autocommit on false \’ yet you set it to \’true\’ (connection.setAutoCommit(true);). Actually it should be done like this: first you set autocommit to false and right after you do the connection.commit(); you set it to true again:

July 13, 2010 at 1:47 pm

You say: set autocommit on false yet you set it to true (connection.setAutoCommit(true);). Actually it should be done like this: first you set autocommit to false and right after you do the connection.commit(); you set it to true again:

4 thoughts on “SQL Transactions in Java”