{"id":3494,"date":"2015-05-16T19:42:10","date_gmt":"2015-05-17T03:42:10","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3494"},"modified":"2024-01-11T02:17:03","modified_gmt":"2024-01-11T09:17:03","slug":"mysql-jdbc-transaction","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-jdbc-tutorial\/mysql-jdbc-transaction\/","title":{"rendered":"MySQL JDBC Transaction"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to perform a transaction in JDBC using the commit()<\/code> and rollback()<\/code> methods of the Connection<\/code> object.<\/p>\n\n\n\n

This tutorial picks up where the Inserting Data Into Table Using JDBC PreparedStatement<\/a>\u00a0tutorial left off.<\/p>\n\n\n\n

Setting auto-commit mode<\/h2>\n\n\n\n

When you connect to MySQL databases<\/a>, the auto-commit mode is set to true<\/code> by default. <\/p>\n\n\n\n

If the auto-commit mode is true, any changes that you made to the database are applied permanently.<\/p>\n\n\n\n

To control when to apply the change permanently to a database, you set the auto-commit to false by calling the setAutoCommit()<\/code> method of the Connection<\/code> object:<\/p>\n\n\n

conn.setAutoCommit(false<\/span>);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that you should always call setAutoCommit()<\/code> method immediately after opening a connection to the database.<\/p>\n\n\n\n
When the auto-commit mode is false<\/code>, you can call the following method of the Connection<\/code> object:<\/p>\n\n\n\n
    \n
  • The commit()<\/code> method to apply the change permanently to the database.<\/li>\n\n\n\n
  • The rollback()<\/code> method to undo the changes.<\/li>\n<\/ul>\n\n\n\n
    MySQL JDBC transaction example<\/h2>\n\n\n\n
    We will add a new candidate and assign skills to the candidate in one transaction. Here are the steps:<\/p>\n\n\n\n
      \n
    • First, insert a row into the candidates<\/code> table and get the inserted ID back.<\/li>\n\n\n\n
    • Second, insert a set of candidate ID and skill ID into the candidate_skills<\/code> table.<\/li>\n\n\n\n
    • Third, commit or roll back the changes, depending on those operations.<\/li>\n<\/ul>\n\n\n\n
      \"mysql<\/figure>\n\n\n\n
      The following defines the method addWithSkills()<\/code> in the Candidate<\/code> class that follows those steps: <\/p>\n\n\n
      public<\/span> class<\/span> Candidate<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> addWithSkills<\/span>(String firstName, String lastName, Date dob, String email, String phone, int<\/span>[] skills)<\/span> <\/span>{\n        Connection conn = null<\/span>;\n        PreparedStatement stmt = null<\/span>, pstmtAssignment= null<\/span>;\n        ResultSet rs = null<\/span>;\n\n        try<\/span> {\n            conn = MySQLConnection.connect();\n\n            if<\/span>(conn == null<\/span>) return<\/span>;\n\n            conn.setAutoCommit(false<\/span>);\n\n            String sql = \"INSERT INTO candidates(first_name, last_name, dob, phone, email) VALUES (?, ?, ?, ?, ?)\"<\/span>;\n            stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);\n\n            stmt.setString(1<\/span>, firstName);\n            stmt.setString(2<\/span>, lastName);\n            stmt.setDate(3<\/span>, dob);\n            stmt.setString(4<\/span>, phone);\n            stmt.setString(5<\/span>, email);\n\n            int<\/span> rowAffected = stmt.executeUpdate();\n\n            if<\/span> (rowAffected == 1<\/span>) {\n                rs = stmt.getGeneratedKeys();\n                int<\/span> candidateId = (rs.next()) ? rs.getInt(1<\/span>) : 0<\/span>;\n\n                String sqlPivot = \"INSERT INTO candidate_skills(candidate_id, skill_id) VALUES (?, ?)\"<\/span>;\n                pstmtAssignment = conn.prepareStatement(sqlPivot);\n\n                for<\/span> (int<\/span> skillId : skills) {\n                    pstmtAssignment.setInt(1<\/span>, candidateId);\n                    pstmtAssignment.setInt(2<\/span>, skillId);\n                    pstmtAssignment.executeUpdate();\n                }\n                conn.commit();\n            } else<\/span> {\n                conn.rollback();\n            }\n        } catch<\/span> (SQLException ex) {\n            try<\/span> {\n                conn.rollback();\n            } catch<\/span> (SQLException e) {\n               e.printStackTrace();\n            }\n            System.err.println(ex.getMessage());\n        } finally<\/span> {\n            try<\/span> {\n                if<\/span> (rs != null<\/span>) rs.close();\n                if<\/span> (stmt != null<\/span>) stmt.close();\n                if<\/span> (pstmtAssignment != null<\/span>) pstmtAssignment.close();\n                if<\/span> (conn != null<\/span>) conn.close();\n            } catch<\/span> (SQLException e) {\n                System.err.println(e.getMessage());\n            }\n        }\n    }\n\/\/ ...<\/span>\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
      How it works.<\/p>\n\n\n\n
      First, establish a connection to the database:<\/p>\n\n\n
      conn = MySQLConnection.connect();<\/code><\/span><\/pre>\n\n\n
      Second, set the auto-commit to false:<\/p>\n\n\n
      conn.setAutoCommit(false<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      Third, insert a new row into the candidate table:<\/p>\n\n\n
      int rowAffected = stmt.executeUpdate();<\/code><\/span><\/pre>\n\n\n
      Fourth, check the rowAffected, if the insert fails, roll back the transaction:<\/p>\n\n\n
      conn<\/span>.rollback<\/span>();<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
      Otherwise, get the candidate id.<\/p>\n\n\n\n
      Fifth, insert rows into the candidate_skills<\/code> table. If the inserts succeed, commit the transaction:<\/p>\n\n\n
      conn<\/span>.commit<\/span>();<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
      Otherwise, roll it back:<\/p>\n\n\n
      conn<\/span>.rollback<\/span>();<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
      Also, if any SQL exception occurs, roll back the transaction.<\/p>\n\n\n\n
      The following calls the addWithSkills()<\/code> method to add a new candidate and assign new skills:<\/p>\n\n\n
      import<\/span> java.sql.Date;\n\npublic class<\/span> Main<\/span> <\/span>{\n    public static<\/span> void<\/span> main(String<\/span>[] args){\n        int[] skills = { 1<\/span>,2<\/span>,3<\/span> };\n        Candidate.addWithSkills(\n                \"John\"<\/span>,\n                \"Doe\"<\/span>,\n                Date<\/span>.valueOf(\"1990-01-04\"<\/span>),\n                \"john.d@yahoo.com\"<\/span>,\n                \"(408) 898-5641\"<\/span>,\n                skills\n        );\n    }\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      Verify the transaction<\/h3>\n\n\n\n
      First, retrieve the data from the candidates<\/code> table:<\/p>\n\n\n
      SELECT * FROM candidates \nORDER BY id desc\nLIMIT 1<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +-----+------------+-----------+------------+----------------+------------------+----------------+\n| id  | first_name | last_name | dob        | phone          | email            | resume         |\n+-----+------------+-----------+------------+----------------+------------------+----------------+\n| 135<\/span> | John       | Doe       | 1990<\/span>-01<\/span>-04<\/span> | (408<\/span>) 898<\/span>-5641<\/span> | john.d@yahoo.com | NULL           |\n+-----+------------+-----------+------------+----------------+------------------+----------------+\n1<\/span> row in<\/span> set<\/span> (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      The output indicates that one row has been inserted successfully.<\/p>\n\n\n\n
      Second, retrieve the skill id from the candidate_skills<\/code> table with the candidate id 135:<\/p>\n\n\n
      SELECT * \nFROM candidate_skills \nWHERE candidate_id = 135<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
      Output:<\/p>\n\n\n
      +--------------+----------+\n| candidate_id | skill_id |\n+--------------+----------+\n|          135<\/span> |        1<\/span> |\n|          135<\/span> |        2<\/span> |\n|          135<\/span> |        3<\/span> |\n+--------------+----------+\n3<\/span> rows in<\/span> set<\/span> (0.02 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      The output shows that three rows have been inserted successfully.<\/p>\n\n\n\n
      Both the candidate and the skills assigned to the candidate have been updated successfully in one transaction.<\/p>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t