Java JDBC Transactions Tutorial

This Java tutorial intends to help you understand how database transactions work with JDBC. At the end, you will be able to write Java database programs that execute SQL statements within a transaction.

1. Why Transactions?

In database systems, transactions are designed to preserve data integrity by grouping multiple statements to be executed as a single unit. In a transaction, either all of the statements are executed, or none of the statements is executed. If any statement failed to execute, the whole transaction is aborted and the database is rolled back to the previous state. This assures the data is kept consistence in the events of network problems, software errors, etc.

Let’s see an example.

Imagine in a sales application, the manager saves a new order and also updates total sales to date in the current month. The order details and the total sales should be updated at the same time, otherwise the data will be inconsistence. Here, the application should group the save order details statement and update total sales statement in a transaction. Both these statements must be executed. If either one statement failed to execute, all changes are discarded.

Transactions also provide protection against conflicts that might arise when multiple users access the same data at the same time, by using locking mechanisms to block access by others to the data that is being accessed by the current transaction.

2. Typical Transactions Handling Workflow in JDBC

Now, let’s see how to use transactions with JDBC. Given connection is an active database connection, the following code skeleton illustrates a typical workflow of handling a database transaction with JDBC in Java:

try {

	// begin the transaction:
	connection.setAutoCommit(false);

	// execute statement #1

	// execute statement #2

	// execute statement #3

	// ...

	// commit the transaction
	connection.commit();

} catch (SQLException ex) {
	// abort the transaction
	connection.rollback();

} finally {

	// close statements

	connection.setAutoCommit(true);
}

Let’s explore this workflow in details.

Disabling Auto Commit mode

By default, a new connection is in auto-commit mode. This means each SQL statement is treated as a transaction and is automatically committed right after it is executed. So we have to disable the auto commit mode to enable two or more statements to be grouped into a transaction:

connection.setAutoCommit(false);

Committing the transaction

After the auto commit mode is disabled, all subsequent SQL statements are included in the current transaction, and they are committed as a single unit until we call the method commit():

connection.commit();

So a transaction begins right after the auto commit is disabled and ends right after the connection is committed. Remember to execute SQL statements between these calls to ensure they are in the same transaction.

Rolling back the transaction

If any statement failed to execute, a SQLException is thrown, and in the catch block, we invoke the method rollback() to abort the transaction:

connection.rollback();

Any changes made by the successful statements are discarded and the database is rolled back to the previous state before the transaction.

Enabling Auto Commit mode

Finally, we enable the auto commit mode to get the connection back to the default state:

connection.setAutoCommit(true);

In the default state (auto commit is enabled), each SQL is treated as a transaction and we don’t need to call the commit() method manually.

3. A JDBC Transactions Example

Now, let’s look at a real code example. Suppose that we are working on a database called sales with the following tables:

When a new order is saved (a new row inserted into the table orders), the monthly sales also must be updated (a corresponding row gets updated in the table monthly_sales). So these two statements (save order and update sales) should be grouped into a transaction.

The following method shows how to execute these two statements in a transaction with JDBC:

public void saveOrder(int productId, Date orderDate, float amount, int reportMonth) {
	PreparedStatement orderStatement = null;
	PreparedStatement saleStatement = null;

	try {

		conn.setAutoCommit(false);

		String sqlSaveOrder = "insert into orders (product_id, order_date, amount)";
			sqlSaveOrder += " values (?, ?, ?)";

		String sqlUpdateTotal = "update monthly_sales set total_amount = total_amount + ?";
		sqlUpdateTotal += " where product_id = ? and report_month = ?";

		orderStatement = conn.prepareStatement(sqlSaveOrder);
		saleStatement = conn.prepareStatement(sqlUpdateTotal);

		orderStatement.setInt(1, productId);
		orderStatement.setDate(2, orderDate);
		orderStatement.setFloat(3, amount);

		saleStatement.setFloat(1, amount);
		saleStatement.setInt(2, productId);
		saleStatement.setInt(3, reportMonth);

		orderStatement.executeUpdate();
		saleStatement.executeUpdate();

		conn.commit();

	} catch (SQLException ex) {
		if (conn != null) {
			try {

				conn.rollback();

				System.out.println("Rolled back.");
			} catch (SQLException exrb) {
				exrb.printStackTrace();
			}
		}
	} finally {
		try {
			if (orderStatement != null ) {
				orderStatement.close();
			}

			if (saleStatement != null ) {
				saleStatement.close();
			}

			conn.setAutoCommit(true);
		} catch (SQLException excs) {
			excs.printStackTrace();
		}
	}
}

And here is the full source code of a test program:

import java.sql.*;

/**
 * JDBC Transaction Demo Program
 *
 * @author www.codejava.net
 */
public class JDBCTransactionsDemo {
	private String dbURL = "jdbc:mysql://localhost:3306/sales";
	private String user = "root";
	private String password = "P@ssw0rd";
	private Connection conn;

	public void connect() {
		try {
			conn = DriverManager.getConnection(dbURL, user, password);
			System.out.println("Connected.");
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public void disconnect() {
		try {
			conn.close();
			System.out.println("Closed.");
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public void saveOrder(int productId, Date orderDate, float amount, int reportMonth) {
		PreparedStatement orderStatement = null;
		PreparedStatement saleStatement = null;

		try {

    		conn.setAutoCommit(false);

    		String sqlSaveOrder = "insert into orders (product_id, order_date, amount)";
			sqlSaveOrder += " values (?, ?, ?)";

    		String sqlUpdateTotal = "update monthly_sales set total_amount = total_amount + ?";
    		sqlUpdateTotal += " where product_id = ? and report_month = ?";

    		orderStatement = conn.prepareStatement(sqlSaveOrder);
    		saleStatement = conn.prepareStatement(sqlUpdateTotal);

    		orderStatement.setInt(1, productId);
    		orderStatement.setDate(2, orderDate);
    		orderStatement.setFloat(3, amount);

			saleStatement.setFloat(1, amount);
			saleStatement.setInt(2, productId);
			saleStatement.setInt(3, reportMonth);

			orderStatement.executeUpdate();
			saleStatement.executeUpdate();

			conn.commit();

		} catch (SQLException ex) {
			if (conn != null) {
				try {

					conn.rollback();

					System.out.println("Rolled back.");
				} catch (SQLException exrb) {
					exrb.printStackTrace();
				}
			}
		} finally {
			try {
				if (orderStatement != null ) {
					orderStatement.close();
				}

				if (saleStatement != null ) {
					saleStatement.close();
				}

				conn.setAutoCommit(true);
			} catch (SQLException excs) {
				excs.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		JDBCTransactionsDemo demo = new JDBCTransactionsDemo();

		int productId = 1;
		int reportMonth = 7;
		Date date = new Date(System.currentTimeMillis());
		float amount = 580;

		demo.connect();

		demo.saveOrder(productId, date, amount, reportMonth);

		demo.disconnect();
	}
}

Here, you understand how the setAutoCommit(), commit() and rollback() methods are used in real code.

4. Using Save Points in a Transaction

The JDBC API provides the Connection.setSavepoint() method that marks a point to which the transaction can be rolled back. The rollback() method is overloaded to takes a save point as its argument:

connection.rollback(savepoint)

This allows us to undo only changes after the save point in case something wrong happen. The changes before the save point are still committed. The following workflow helps you understand how save points are used in a transaction:

try {

	// begin the transaction:
	connection.setAutoCommit(false);

	// execute statement #1

	// execute statement #2


	// statements #1 & #2 are executed successfully till this point:
	Savepoint savepoint = connection.setSavepoint();


	// execute statement #3

	// execute statement #4


	if (/* something wrong */) {
		// roll back the transaction to the savepoint:
		connection.rollback(savepoint);
	}

	// execute statement #5

	// ...

	// commit the transaction
	connection.commit();

} catch (SQLException ex) {
	// abort the transaction
	connection.rollback();

} finally {

	// close statements

	connection.setAutoCommit(true);
}

Now, let’s see a real code example. In the following program, the transaction consists of the following statement:

- insert a new product to the table products.

- insert a new order to the table orders.

- update total amount in the monthly sales.

In case the amount of the new order cannot help monthly sales > 10,000, the transaction is rolled back to the point where the new product was inserted.

Here’s the code of the program that shows how to use save point in a transaction with JDBC:

import java.sql.*;

/**
 * JDBC Transaction with Save Point Demo Program
 *
 * @author www.codejava.net
 */
public class JDBCTransactionSavePointDemo {
	private String dbURL = "jdbc:mysql://localhost:3306/sales";
	private String user = "root";
	private String password = "P@ssw0rd";
	private Connection conn;

	public void connect() {
		try {
			conn = DriverManager.getConnection(dbURL, user, password);
			System.out.println("Connected.");
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public void disconnect() {
		try {
			conn.close();
			System.out.println("Closed.");
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
	}

	public void saveOrder(String newProductName, float newProductPrice,
				int productId, Date orderDate, float orderAmount, int reportMonth) {

		PreparedStatement productStatement = null;
		PreparedStatement orderStatement = null;
		PreparedStatement saleStatement = null;
		PreparedStatement getTotalStatement = null;

		try {

    		conn.setAutoCommit(false);

    		String sqlSaveProduct = "insert into products (product_name, price)";
			sqlSaveProduct += " values (?, ?)";

    		productStatement = conn.prepareStatement(sqlSaveProduct);
			productStatement.setString(1, newProductName);
			productStatement.setFloat(2, newProductPrice);

			productStatement.executeUpdate();

			Savepoint savepoint = conn.setSavepoint();

    		String sqlSaveOrder = "insert into orders (product_id, order_date, amount)";
			sqlSaveOrder += " values (?, ?, ?)";

			orderStatement = conn.prepareStatement(sqlSaveOrder);
    		orderStatement.setInt(1, productId);
    		orderStatement.setDate(2, orderDate);
    		orderStatement.setFloat(3, orderAmount);

			orderStatement.executeUpdate();

			String sqlGetTotal = "select total_amount from monthly_sales";
			sqlGetTotal += " where product_id = ? and report_month = ?";

			getTotalStatement = conn.prepareStatement(sqlGetTotal);
			getTotalStatement.setInt(1, productId);
			getTotalStatement.setInt(2, reportMonth);

			ResultSet rs = getTotalStatement.executeQuery();
			rs.next();
			float totalAmount = rs.getFloat("total_amount");
			rs.close();

			if (totalAmount + orderAmount < 10000) {

				conn.rollback(savepoint);

			}

    		String sqlUpdateTotal = "update monthly_sales set total_amount = total_amount + ?";
    		sqlUpdateTotal += " where product_id = ? and report_month = ?";


    		saleStatement = conn.prepareStatement(sqlUpdateTotal);


			saleStatement.setFloat(1, orderAmount);
			saleStatement.setInt(2, productId);
			saleStatement.setInt(3, reportMonth);


			saleStatement.executeUpdate();

			conn.commit();

		} catch (SQLException ex) {
			if (conn != null) {
				try {

					conn.rollback();

					System.out.println("Rolled back.");
				} catch (SQLException exrb) {
					exrb.printStackTrace();
				}
			}
		} finally {
			try {
				if (productStatement != null ) {
					productStatement.close();
				}

				if (orderStatement != null ) {
					orderStatement.close();
				}

				if (saleStatement != null ) {
					saleStatement.close();
				}

				if (getTotalStatement != null ) {
					getTotalStatement.close();
				}

				conn.setAutoCommit(true);
			} catch (SQLException excs) {
				excs.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		JDBCTransactionSavePointDemo demo = new JDBCTransactionSavePointDemo();

		String newProductName = "iPod";
		float newProductPrice = 399;

		int productId = 1;
		int reportMonth = 7;
		Date date = new Date(System.currentTimeMillis());
		float orderAmount = 580;

		demo.connect();

		demo.saveOrder(newProductName, newProductPrice, productId, date, orderAmount, reportMonth);

		demo.disconnect();
	}
}

NOTES:

• The JDBC API provides the Connection.releaseSavepoint(savepoint)method that removes the specified save point from the current transaction. A save point has been released become invalid and cannot be rolled back to. Any attempt to roll back the transaction to a released save point causes a SQLException.
• A save point is automatically released and becomes invalid when the transaction is committed or when the entire transaction is rolled back.

API References:

• Using Transactions (the Java Tutorials)
• java.sql.Connection Javadoc

Related JDBC Tutorials:

• Java JDBC CRUD Tutorial
• How to call stored procedure with JDBC
• How to read database metadata in JDBC
• How to insert binary data into database with JDBC
• How to read binary data from database with JDBC
• How to use Scrollable ResultSet
• How to use Updatable ResultSet
• How to use CachedRowSet

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

JDBCTransactionExamples.zip

[JDBC Transaction Code Examples]

2 kB