Programming Tutorials

(追記) (追記ここまで)

Using Transactions in JDBC

By: Ramlak in JDBC Tutorials on 2007年04月06日 [フレーム]

By default, JDBC classes operate in auto-commit mode. This means that each SQL statement executed is considered a separate transaction ( a singleton transaction) and a commit is made at the completion of the statement. In order to group a set of transactions together, this autocommit mode must be disabled using the connection class setAutoCommit method and passing the method boolean false value.

With autocommit disabled, there is always an implicit transaction in place. To commit a series of previously executed SQL statements to the database, an explicit commit can be made by calling the Connection method commit. Alternatively, a rollback can be made by calling the Connection method rollback. This rolls back the current transaction and restores the database to the state bit was in before the start of the current transaction. Failure to commit a transaction before closing the corresponding Connection object will lead to an automatic rollback of the database updates; all work will be lost. Developers should be sure that all work is committed to the database before closing the Connection.

Various database-dependant isolation levels can be set. There are methods in the DatabaseMetaData class to learn the existing defaults in place in the current session and methods in the Connection class to change the current isolation level.

JDBC Isolation Mode Description

TRANSACTION_NONE Transactions are not supported. Not all databases support this mode; most require some level of transactions to be in place.

TRANSACTION_READ_COMMITTED Only reads on the current row are repeatable.

TRANSACTION_READ_UNCOMMITTED Rows being used by a tranaction can be read even if the rows have not been committed.

TRANSACTION_REPEATABLE_READ Reads on all rows of a result are repeatable.

TRANSACTION_SERIALIZABLE Reads on all rows of a transaction are repeatable in the order in which they were executed.

Here's a sample program that demonstrates how to use transactions in JDBC:

import java.sql.*;
public class TransactionExample {
 public static void main(String[] args) {
 Connection conn = null;
 try {
 Class.forName("com.mysql.jdbc.Driver");
 conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password");
 conn.setAutoCommit(false); // disable auto-commit
 
 // Perform some operations
 Statement stmt = conn.createStatement();
 stmt.executeUpdate("INSERT INTO orders (customer_id, order_date, amount) VALUES (1, '2022-05-01', 100.00)");
 stmt.executeUpdate("UPDATE customers SET balance = balance - 100.00 WHERE customer_id = 1");
 
 // Commit the transaction
 conn.commit();
 
 System.out.println("Transaction committed successfully.");
 } catch (SQLException ex) {
 // Rollback the transaction if there's an exception
 if (conn != null) {
 try {
 conn.rollback();
 } catch (SQLException ex2) {
 ex2.printStackTrace();
 }
 }
 ex.printStackTrace();
 } catch (ClassNotFoundException ex) {
 ex.printStackTrace();
 } finally {
 // Close the connection
 if (conn != null) {
 try {
 conn.close();
 } catch (SQLException ex) {
 ex.printStackTrace();
 }
 }
 }
 }
}

In this program, we first disable the auto-commit mode using the setAutoCommit(false) method on the Connection object. This means that all changes to the database will be treated as part of a transaction until we explicitly commit the transaction.

We then perform some operations, including an INSERT and an UPDATE statement, which are both part of the same transaction. If any exception occurs, we catch it and rollback the transaction using the rollback() method on the Connection object.

If there are no exceptions, we commit the transaction using the commit() method on the Connection object.

Finally, we close the connection in the finally block.

Previous
<< What is the ACID principal?

Next
Creating Database Connection Pool in Tomcat 5.0 and Tomcat 5.5 for MySQL and Java>>

(追記) (追記ここまで)