MySQL JDBC Transaction

Summary: in this tutorial, you will learn how to perform a transaction in JDBC using the commit() and rollback() methods of the Connection object.

This tutorial picks up where the Inserting Data Into Table Using JDBC PreparedStatement tutorial left off.

Setting auto-commit mode

When you connect to MySQL databases, the auto-commit mode is set to true by default.

If the auto-commit mode is true, any changes that you made to the database are applied permanently.

To control when to apply the change permanently to a database, you set the auto-commit to false by calling the setAutoCommit() method of the Connection object:

conn.setAutoCommit(false);Code language: Java (java)

Notice that you should always call setAutoCommit() method immediately after opening a connection to the database.

When the auto-commit mode is false, you can call the following method of the Connection object:

• The commit() method to apply the change permanently to the database.
• The rollback() method to undo the changes.

MySQL JDBC transaction example

We will add a new candidate and assign skills to the candidate in one transaction. Here are the steps:

• First, insert a row into the candidates table and get the inserted ID back.
• Second, insert a set of candidate ID and skill ID into the candidate_skills table.
• Third, commit or roll back the changes, depending on those operations.

The following defines the method addWithSkills() in the Candidate class that follows those steps:

public class Candidate {
    public static void addWithSkills(String firstName, String lastName, Date dob, String email, String phone, int[] skills) {
        Connection conn = null;
        PreparedStatement stmt = null, pstmtAssignment= null;
        ResultSet rs = null;

        try {
            conn = MySQLConnection.connect();

            if(conn == null) return;

            conn.setAutoCommit(false);

            String sql = "INSERT INTO candidates(first_name, last_name, dob, phone, email) VALUES (?, ?, ?, ?, ?)";
            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            stmt.setString(1, firstName);
            stmt.setString(2, lastName);
            stmt.setDate(3, dob);
            stmt.setString(4, phone);
            stmt.setString(5, email);

            int rowAffected = stmt.executeUpdate();

            if (rowAffected == 1) {
                rs = stmt.getGeneratedKeys();
                int candidateId = (rs.next()) ? rs.getInt(1) : 0;

                String sqlPivot = "INSERT INTO candidate_skills(candidate_id, skill_id) VALUES (?, ?)";
                pstmtAssignment = conn.prepareStatement(sqlPivot);

                for (int skillId : skills) {
                    pstmtAssignment.setInt(1, candidateId);
                    pstmtAssignment.setInt(2, skillId);
                    pstmtAssignment.executeUpdate();
                }
                conn.commit();
            } else {
                conn.rollback();
            }
        } catch (SQLException ex) {
            try {
                conn.rollback();
            } catch (SQLException e) {
               e.printStackTrace();
            }
            System.err.println(ex.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (pstmtAssignment != null) pstmtAssignment.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                System.err.println(e.getMessage());
            }
        }
    }
// ...
}Code language: Java (java)

How it works.

First, establish a connection to the database:

conn = MySQLConnection.connect();

Second, set the auto-commit to false:

conn.setAutoCommit(false);Code language: JavaScript (javascript)

Third, insert a new row into the candidate table:

int rowAffected = stmt.executeUpdate();

Fourth, check the rowAffected, if the insert fails, roll back the transaction:

conn.rollback();Code language: CSS (css)

Otherwise, get the candidate id.

Fifth, insert rows into the candidate_skills table. If the inserts succeed, commit the transaction:

conn.commit();Code language: CSS (css)

Otherwise, roll it back:

conn.rollback();Code language: CSS (css)

Also, if any SQL exception occurs, roll back the transaction.

The following calls the addWithSkills() method to add a new candidate and assign new skills:

import java.sql.Date;

public class Main {
    public static void main(String[] args){
        int[] skills = { 1,2,3 };
        Candidate.addWithSkills(
                "John",
                "Doe",
                Date.valueOf("1990-01-04"),
                "[email protected]",
                "(408) 898-5641",
                skills
        );
    }
}Code language: JavaScript (javascript)

Verify the transaction

First, retrieve the data from the candidates table:

SELECT * FROM candidates 
ORDER BY id desc
LIMIT 1;Code language: Java (java)

Output:

+-----+------------+-----------+------------+----------------+------------------+----------------+
| id  | first_name | last_name | dob        | phone          | email            | resume         |
+-----+------------+-----------+------------+----------------+------------------+----------------+
| 135 | John       | Doe       | 1990-01-04 | (408) 898-5641 | [email protected] | NULL           |
+-----+------------+-----------+------------+----------------+------------------+----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output indicates that one row has been inserted successfully.

Second, retrieve the skill id from the candidate_skills table with the candidate id 135:

SELECT * 
FROM candidate_skills 
WHERE candidate_id = 135;Code language: Java (java)

Output:

+--------------+----------+
| candidate_id | skill_id |
+--------------+----------+
|          135 |        1 |
|          135 |        2 |
|          135 |        3 |
+--------------+----------+
3 rows in set (0.02 sec)Code language: JavaScript (javascript)

The output shows that three rows have been inserted successfully.

Both the candidate and the skills assigned to the candidate have been updated successfully in one transaction.