JDBC Tutorial: MySQL JDBC Transaction

By /
JDBC Tutorial MySQL JDBC Transaction

Using SQL, one can perform multiple operations on a table like fetching the data, inserting a new record, deleting a particular record, updating a table, creating a new table, and so on.

A SQL transaction is nothing but a single unit of multiple operations. A transaction in SQL can have only two states after execution, COMMIT (saved onto database) or ROLLBACK (no changes saved on the database), and there exists a constraint on each transaction. The constraint is that after the execution of a transaction all the operations or queries must either successfully be executed or in case of one or more failures in execution, the transaction will be rolled back.

Java programming language supports transactions with the help of JDBC. In this tutorial, will learn how to use JDBC for MySQL transactions.

Also read: Setting Up MySQL JDBC Development Environment

The transaction follows a simple rule of ACID properties, let’s quickly go through what ACID stands for and what it represents:

  1. Atomicity: Atomicity states that all operations on the data must be a single unit. That means, that either all the operations are performed or none.
  2. Consistency: The state of a transaction must be maintained consistently. For example, when sending money to someone using UPI then the transaction must be reflected in both accounts.
  3. Isolation: Isolation states that each and every transaction is independent, which means no two or more transactions are dependent on each other in any way or form.
  4. Durability: Durability states that changes made on the data after a commit must persist and are not discarded at any point in time.

How To use JDBC for MySQL Transactions

JDBC is an abbreviation for Java Database Connectivity, it is an API of java that was developed to connect and execute queries on a database using Java programming language. JDBC provides a number of APIs with respect to different databases, for example, MySQL, Oracle database, etc.

To connect to a database using JDBC, we use four steps:

  1. Creating a connection with the database
  2. Create a SQL Query
  3. Execute Result Sets
  4. Close connection

When we create a SQL transaction, multiple queries are written in it, if all the queries are successfully executed then the database commits the changes otherwise in case of even a single failure, the whole transaction is rolled back. Transactions come in handy and are very important when it comes to the simultaneous execution of multiple queries, or multiple tables to be affected.

The syntax for creating MySQL transactions using JDBC: 

//start transaction
myConn.setAutoCommit(false);

//prepare SQL statements (INSERT, UPDATE, DELETE)

boolean status = askUserIfOkToSave();

if(status){
    //store into database
    muConn.commit();
}
else{
    //roll-back
    myConn.rollback();
}Code language: Java (java)

A Simple Example of MySQL JDBC Transaction

Now that we have gone through the whole procedure, let’s see an example of creating a MySQL transaction using JDBC. Let’s take a database named employee. The employee table contains fields such as employee ID, first name, last name, contact number, address, salary, department, and supervisor.

We’ll perform 3 separate operations (DELETE, UPDATE, and SELECT), if all three transactions are executed successfully, then the changes will be committed to the database otherwise the transaction will be rolled back. Let’s see the code below:

import java.sql.*;
import java.util.Scanner;

public class TransactionDemo {

   public static void main(String[] args) throws SQLException {

      Connection myConn = null;
      Statement myStmt = null;

      try {
         // 1. Get a connection to database
         myConn = DriverManager.getConnection(
               "jdbc:mysql://localhost:3306/db", "employee", "password");

         // Set Auto Commit to False
         myConn.setAutoCommit(false);

         // Transaction Step 1: Delete employees who belong to Management department
         myStmt = myConn.createStatement();
         myStmt.executeUpdate("DELETE FROM employee WHERE dept='Management'");

         // Transaction Step 2:Set salary to 50000 of employees where department is IT 
         myStmt.executeUpdate("UPDATE employee SET salary=500000 WHERE dept='IT'");

         //Transaction Step 3: view first name and salary of employee who belongs to sales department
         myStmt.executeQuery("SELECT fname, salary FROM employee WHERE dept='Sales');

         System.out.println("Transaction is ready.");

         // Ask user to save transaction or not
         boolean statusTrue = askUserIfOkToSave();

         if (StatusTrue) {
            // Commit transaction
            myConn.commit();
            System.out.println("Transaction COMMITTED.\n");
         } else {
            // rollback
            myConn.rollback();
            System.out.println("Transaction ROLLED BACK.\n");
         }

      } catch (Exception e) {
         e.printStackTrace();
      } finally {
         close(myConn, myStmt, null);
      }
   }
 }
}Code language: Java (java)

This transaction will successfully commit to the database, as the transaction adhered to ACID property and all operations were successfully executed.

Summary

In this tutorial, we learned what MySQL transactions are, what ACID properties are, and what is the need for transactions in SQL. We learned how we can create MySQL transactions using JDBC and at last, we saw an example to get more knowledge out of what we discussed earlier. This tutorial will help you get started with JDBC and MySQL transactions.

See Also: The official documentation for MySQL transaction and JDBC MySQL transaction for more information.