How to start a transaction in JDBC?

JDBCObject Oriented ProgrammingProgramming

A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A transaction is the propagation of one or more changes to the database. For example, if you are creating a record or updating a record or deleting a record from the table, then you are performing a transaction on that table. It is important to control these transactions to ensure the data integrity and to handle database errors.

Ending a transaction

After performing the required operations you can end/save a transaction using the commit command. In JDBC applications you can do this using the commit() method of the Connection Interface.

Whenever an issue occur with in a transaction you can revert the changes done in the database using rollback.

Staring a transaction

In general, in JDBC, after you establish a connection, by default, your connection will be in auto-commit mode i.e. Every statement you execute using this connection is saved automatically, which means the database manages its own transactions and each individual SQL-statement is considered as a transations.

You can enable manual-transaction support by turning off the auto-commit mode. To do so, you need to passing the boolean value false to the setAutoCommit() method of the Connection interface.

conn.setAutoCommit(false);

Example

Following program inserts data in to this table using batch processing. Here we set the auto commit false, add the required statements to a batch, execute the batch and then commit the database on our own.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class BatchProcessing_Statement {
   public static void main(String args[])throws Exception {
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Setting auto-commit false
      con.setAutoCommit(false);
      //Statements to insert records
      String insert1 = "INSERT INTO Dispatches( Product_Name , Name_Of_Customer , "
         + "Month_Of_Dispatch , Price, Location) VALUES "
         + "('KeyBoard', 'Amith', 'January', 1000, 'hyderabad')";
      String insert2 = "INSERT INTO Dispatches( Product_Name , Name_Of_Customer , "
         + "Month_Of_Dispatch , Price, Location) VALUES "
         + "('Earphones', 'SUMITH', 'March', 500, 'Vishakhapatnam')";
      String insert3 = "INSERT INTO Dispatches( Product_Name , Name_Of_Customer , "
         + "Month_Of_Dispatch , Price, Location) VALUES "
         + "('Mouse', 'Sudha', 'September', 200, 'Vijayawada')";
      //Adding the statements to the batch
      stmt.addBatch(insert1);
      stmt.addBatch(insert2);
      stmt.addBatch(insert3);
      //Executing the batch
      stmt.executeBatch();
      //Saving the changes
      con.commit();
      System.out.println("Records inserted......");
   }
}

Output

Connection established......
Records inserted......
raja
Published on 04-Jul-2019 14:51:36
Advertisements