MySQL JDBC Transaction | Code Factory
2 min readNov 4, 2020
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
- 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 thesetAutoCommit()
method of theConnection
object as follows:
Connection conn = DriverManager.getConnection(dbURL, dbUser, dbPassword);
conn.setAutoCommit(false);
- Once you have set auto-commit mode to
false
, you can callcommit()
orrollback()
methods of theConnection
object to commit or rollback the transaction. - 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();
}
- The following is the complete example of using JDBC transaction.
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();
}
}
}
}