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.
Tags: Java rollback savepoints SQL transactions
Posted in: Java
June 11, 2010
| 
Gabriela Radu | 
4 Comments
4 Responses
June 30, 2010 1
Nice brief and this enter helped me alot in my college assignement. Thanks you on your information.
July 13, 2010 2
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);
boolean executed = true;
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 3
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);
boolean executed = true;
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 4
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);
boolean executed = true;
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);
}
Leave a Reply