Java – Transaction handling using JDBC Statement interface

June 23, 2016 SJ JDBC 2

In this article, we will learn how and when JDBC transaction can be handled using explicit start and end of transaction while interacting with database i.e.; using Statement Interface

Q) Why there is a need to handle JDBC transaction ?

  • By default, JDBC connection are auto-commit i.e.; 
connection.setAutoCommit(true);
  • Which commits SQL statement issued/executed to database

Q) What are the problem/issues of program without JDBC transaction ?

  • Like for example, statement.executeUpdate(sqlQuery);
  • Above statement commits to database as and when issued/executed to database
  • If any one of the below SQL statements fails from multiple DML operations for any application then,
    1. First, program terminates with error stating appropriate reasons
    2. All above statements of program will be executed successfully but,
    3. below statements will left un-executed
  • Thereby, causing database to be inconsistent state and breaking integrity

1. Pre-requisite :

  • Java JDK 1.8.0_77
  • MySQL database 5.5.16
  • Eclipse Luna IDE 4.4.0
  • mysql-connector-java-5.1.38.jar file

2. Database parameters :

Let us move on and code an example to connect MySQL database from Java application to invoke or call database stored procedure using JDBC API. But before that, we will list down required things to connect database

  • database server IP or address (localhost)
  • sever port (3306)
  • database name (PLAYER_INFO)
  • username (root)
  • password (root@123)

Note: All bold are database values to connect MySQL database

3. Without explicit JDBC Transaction handling :

  • Let us see an example without handling JDBC transaction
  • So as to understand need of JDBC transaction while dealing/interacting with database

JDBCAutoCommitTransaction.java

package in.bench.resources.mysql.db.transaction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCAutoCommitTransaction {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		Statement statement = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/PLAYER_INFO",
					"root",
					"root@123"); 

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// Step 2.C: Execute both in order

			// Insert query
			String sqlInsertQuery = "INSERT INTO PLAYER (NAME, AGE, MATCHES)"
					+ " VALUES ('Glenn McGrath', '46', '124')";

			// 1st execute insert query
			statement.executeUpdate(sqlInsertQuery);

			System.out.println("Insertion is successful");

			// Update query -> this is written to throw ERROR
			// data-type mismatch
			String sqlUpdateQuery = "UPDATE PLAYER"
					+ " SET MATCHES = 'Stephen Rodger Waugh'"
					+ " WHERE PLAYER_ID = 11";

			// 2nd execute update query
			statement.executeUpdate(sqlUpdateQuery);

			System.out.println("All DML operations are successful");
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {

			// Step 3: Closing database connection
			try {
				if(null != connection) {

					// cleanup resources, once after processing
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

Insertion is successful
java.sql.SQLException: Incorrect integer value:
	'Stephen Rodger Waugh' for column 'MATCHES'
        at row 1
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3878)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3814)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2478)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2625)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2547)
	at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1541)
	at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2605)
	at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1469)
	at in.bench.resources.mysql.db.transaction.JDBCAutoCommitTransaction.main(
JDBCAutoCommitTransaction.java:48)

Explanation :

  • Insertion is successful
  • but updation is failure because there is mismatch in data-type
  • Now, we got exact reasons why there is need to handle JDBC transaction with explicit commit/rollback operation

Next question that comes in mind is,

Q) How to handle JDBC transaction ?

  • To handle JDBC transaction,
    1. Set auto commit mode to false so as to handle JDBC transaction
    2. That’s setAutoCommit(false);
    3. commit/rollback as per program logic
    4. commit(); –> for successful execution
    5. rollback(); –> for failures
  • By doing above steps, we are assuring consistency/integrity with database
  • Note: We can handle JDBC transaction (commit/rollback) within try-catch-finally block

3.1 JDBC Transaction handling with commit/rollback :

GracefulTransactionWithJDBC.java

package in.bench.resources.mysql.db.transaction;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class GracefulTransactionWithJDBC {

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

		// variables
		Connection connection = null;
		Statement statement = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/PLAYER_INFO",
					"root",
					"root@123"); 

			// Start of JDBC transaction
			connection.setAutoCommit(false);

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// Step 2.C: Execute both in order

			// Insert query
			String sqlInsertQuery = "INSERT INTO PLAYER (NAME, AGE, MATCHES)"
					+ " VALUES ('Matthew Hayden', '44', '103')";

			// 1st execute insert query
			statement.executeUpdate(sqlInsertQuery);

			// Update query -> this is written to throw ERROR
			// data-type mismatch
			String sqlUpdateQuery = "UPDATE PLAYER"
					+ " SET MATCHES = 'Stephen Rodger Waugh'"
					+ " WHERE PLAYER_ID = 11";

			// 2nd execute update query
			statement.executeUpdate(sqlUpdateQuery);

			// End of JDBC transaction
			connection.commit(); // commit, if successful

			System.out.println("All DML operations are successful");
		}
		catch(SQLException sqlex){

			// rollback, if any of the DML operation is failure
			connection.rollback();
			System.out.println("roll back done !!");
			// sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

roll back done !!

4. Download :

Related Articles :

References:

Happy Coding !!
Happy Learning !!

Java – Transaction handling using JDBC PreparedStatement interface
Java – Calling Stored Procedure using JDBC CallableStatement interface with Batch execution

Related posts:

  1. Java – Transaction handling using JDBC PreparedStatement interface
  2. Java – Dropping a table using JDBC Statement interface
  3. Java – Deleting a record using JDBC PreparedStatement interface
  4. Java – Metadata of Resultset using JDBC ResultSetMetaData interface