MySQL JDBC Transaction?

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 Connectionobject as follows:

1 2	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:

1 2 3 4 5 6 7 8 9 10 11 12 13

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

MySQL JDBC transaction example

In this example, we will insert a new record into the candidates table and also assign some of skills to the newly inserted candidate.

We will perform both inserting a candidate and assigning skills within one transaction. The steps will be as follows:

1. Insert a record into the candidates table and get the inserted ID back.
2. Insert a set of candidate ID and Skill ID into the candidate_skills table.
3. If all above operations are successfully, commit the transaction, otherwise roll it back.

The following is the complete example of using JDBC transaction.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

package org.mysqltutorial;   import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Date; import java.sql.ResultSet; import java.sql.Statement;   /** * * @author mysqltutorial.org */ public class Main {   /**    * Insert and assign skills to a specific candidates    * @param firstName    * @param lastName    * @param dob    * @param email    * @param phone    * @param skills    */     public static void addCandidate(String firstName,String lastName,Date dob,                                     String email, String phone, int[] skills) {                  Connection conn = null;                  // for insert a new candidate         PreparedStatement pstmt = null;                  // for assign skills to candidate         PreparedStatement pstmtAssignment = null;                  // for getting candidate id         ResultSet rs = null;           try {             conn = MySQLJDBCUtil.getConnection();             // set auto commit to false             conn.setAutoCommit(false);             //             // Insert candidate             //             String sqlInsert = "INSERT INTO candidates(first_name,last_name,dob,phone,email) "                               + "VALUES(?,?,?,?,?)";                          pstmt = conn.prepareStatement(sqlInsert,Statement.RETURN_GENERATED_KEYS);               pstmt.setString(1, firstName);             pstmt.setString(2, lastName);             pstmt.setDate(3, dob);             pstmt.setString(4, phone);             pstmt.setString(5, email);               int rowAffected = pstmt.executeUpdate();                           // get candidate id             rs = pstmt.getGeneratedKeys();             int candidateId = 0;             if(rs.next())                 candidateId = rs.getInt(1);             //                 // in case the insert operation successes, assign skills to candidate             //             if(rowAffected == 1)             {                 // assign skills to candidates                 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) {             // roll back the transaction             try{                 if(conn != null)                     conn.rollback();             }catch(SQLException e){                 System.out.println(e.getMessage());             }                                       System.out.println(ex.getMessage());         } finally {             try {                 if(rs != null)  rs.close();                 if(pstmt != null) pstmt.close();                 if(pstmtAssignment != null) pstmtAssignment.close();                 if(conn != null) conn.close();                              } catch (SQLException e) {                 System.out.println(e.getMessage());             }         }     }       public static void main(String[] args) {        // insert and assign skills         int[] skills = {1,2,3};         addCandidate("John", "Doe", Date.valueOf("1990-01-04"),                         "john.d@yahoo.com", "(408) 898-5641", skills);     } }

Let’s check the table candidates table before we run the program.

1 2	SELECT * FROM candidates ORDER BY id DESC;

Now, we run the program.

and check the candidates table again:

and check also the candidate_skills table to see if the assignments are there.

1	SELECT * FROM candidate_skills;

As you see we have successfully inserted data into both candidates and candidate_skills table within the same transaction.