Java – Transaction handling using JDBC PreparedStatement interface

June 24, 2016 SJ JDBC 0

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 PreparedStatement 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 using PreparedStatement :

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

JDBCAutoCommitTransactionPS.java

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

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

public class JDBCAutoCommitTransactionPS {

	@SuppressWarnings("resource")
	public static void main(String[] args) {

		// variables
		Connection connection = null;
		PreparedStatement preparedStatement = 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"); 

			// Insert query
			String sqlInsertQuery = "INSERT INTO PLAYER (NAME, AGE, MATCHES)"
					+ " VALUES (?, ?, ?)";

			// Step 2.B: Creating JDBC Statement
			preparedStatement = connection.prepareStatement(
					sqlInsertQuery); // for insert

			// set values
			preparedStatement.setString(1, "Justin Langer");
			preparedStatement.setInt(2, 45);
			preparedStatement.setInt(3, 105);

			// Step 2.C: Execute
			preparedStatement.executeUpdate();

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

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

			// Step 2.B: Creating JDBC Statement
			preparedStatement = connection
					.prepareStatement(sqlUpdateQuery); // for update

			// purposely setting wrong data-type to cause db ERROR
			preparedStatement.setString(1, "Steve Waugh");
			preparedStatement.setInt(2, 11);

			// Step 2.C: Execute
			preparedStatement.executeUpdate();

			// finally print success message
			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
					preparedStatement.close();

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

Output:

Insertion is successful
java.sql.SQLException: Incorrect integer value:
	'Steve 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:2551)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
	at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
	at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5094)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1994)
	at in.bench.resources.mysql.db.transaction.JDBCAutoCommitTransactionPS.main(
JDBCAutoCommitTransactionPS.java:59)

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 using PreparedStatement :

GracefulTransactionPSWithJDBC.java

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

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

public class GracefulTransactionPSWithJDBC {

	@SuppressWarnings("resource")
	public static void main(String[] args) throws SQLException {

		// variables
		Connection connection = null;
		PreparedStatement preparedStatement = 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);

			// Insert query
			String sqlInsertQuery = "INSERT INTO PLAYER (NAME, AGE, MATCHES)"
					+ " VALUES (?, ?, ?)";

			// Step 2.B: Creating JDBC PreparedStatement
			preparedStatement = connection
					.prepareStatement(sqlInsertQuery); // for insert

			// set values
			preparedStatement.setString(1, "Justin Langer");
			preparedStatement.setInt(2, 45);
			preparedStatement.setInt(3, 105);

			// Step 2.C: Execute
			preparedStatement.executeUpdate();

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

			// Step 2.B: Creating JDBC Statement
			preparedStatement = connection
					.prepareStatement(sqlUpdateQuery); // for update

			// purposely setting wrong data-type to cause db ERROR
			preparedStatement.setString(1, "Steve Waugh");
			preparedStatement.setInt(2, 11);

			// Step 2.C: Execute
			preparedStatement.executeUpdate();

			// 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
					preparedStatement.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 – Where clause example using JDBC Statement interface
Java – Transaction handling using JDBC Statement interface

Related posts:

  1. Java – Transaction handling using JDBC Statement interface
  2. Java – Deleting a record using JDBC Statement interface
  3. Java – Updating a record using JDBC PreparedStatement interface
  4. Java – Metadata of database using JDBC DatabaseMetaData interface