MySQL JDBC Transaction | Code Factory

Donate : Link

WordPress Blog : Link

Applications… : Link

In this tutorial, you will learn how to use commit() and rollback() methods of the Connection object to control transaction.

Setting auto-commit mode

Connection conn = DriverManager.getConnection(dbURL, dbUser, dbPassword);
conn.setAutoCommit(false);

Committing and rolling back a transaction

try(Connection conn = DriverManager.getConnection(dbURL,dbUser,dbPassword);){
conn.setAutoCommit(false);

// perform operations such as insert, update, delete here
// ..

// if everything is OK, commit the transaction
conn.commit();
} catch(SQLException e) {
// in case of exception, rollback the transaction
conn.rollback();
}
package com.example.java.programming.test;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
public class CodeFactory {

private static final String QUERY_1 = "";
private static final String QUERY_2 = "";

public static void main(String[] args) {

PreparedStatement objectPS = null;
PreparedStatement otherPS = null;
ResultSet rs = null;
Connection con = null;
int insertedId = 0;

try {
// Get Connection
con = DBConfiguration.getConnection();
con.setAutoCommit(false);

for (UserObject object : userObjectsList) {
// don't forgot to pass Statement otherwise you will get Exception
objectPS = con.prepareStatement(QUERY_1, Statement.RETURN_GENERATED_KEYS);

objectPS.setString(1, object.getString());
objectPS.setInt(2, object.getInt());

int rowAffected = objectPS.executeUpdate();
rs = objectPS.getGeneratedKeys();
if(rs.next()) {
insertedId = rs.getInt(1);
}

if(rowAffected == 1) {
otherPS = con.prepareStatement(QUERY_2);

for(OtherObject other : otherObjectsList) {
otherPS.setInt(1, insertedId);
otherPS.setString(2, other.getString());
otherPS.executeUpdate();
}
}
}
con.commit();
} catch(Exception e) {
// log exception
} finally {
if (rs != null && !rs.isClosed()) {
rs.close();
}
if (objectPS != null && !objectPS.isClosed()) {
objectPS.close();
}
if (otherPS != null && !otherPS.isClosed()) {
otherPS.close();
}
if (con != null) {
con.rollback();
con.setAutoCommit(true); // returning in true state after batch
con.close();
}
}

}
}