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

• 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);

• Once you have set auto-commit mode to false , you can call commit() or rollback() methods of the Connection 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();
			}
		}
		
	}
}