MySQL JDBC Transaction | Code Factory

Setting auto-commit mode

  • When you connect to MySQL databases, the auto-commit mode is set to true by default. It means that the changes will be applied to the database once the statement successfully executed. In case you want to control when to commit the transaction, you call the setAutoCommit() method of the Connection object as follows:
Connection conn = DriverManager.getConnection(dbURL, dbUser, dbPassword);
conn.setAutoCommit(false);
  • Notice that you should always call setAutoCommit() method right after you open a connection to the database.

Committing and rolling back a transaction

  • Once the auto-commit mode is set to false , you can commit or rollback the transaction. The flow of using those methods is as follows:
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();
}
}

}
}

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store