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

}
}

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to write a software development RFP for adequate vendor’s estimates?

Zero to Hero with React Native

Covenant: Developing Custom C2 Communication Protocols

Customize Your Windows PowerShell With oh-my-posh & posh-git

CLI Project-PoGo API

I made my first open source contribution within 200 days (and how you can too)

Exception Factory Methods

A picture of smoky factory chimneys  in black and white

Insights on Chattt’s excellent Product Hunt launch

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
Code Factory

Code Factory

More from Medium

Multithreading in Java

Rest is Sync or Async? / How WebSocket works?

All new Java 8 — time API — LocalDate

Types of Update operations in MongoDB using Spring Boot