PostgreSQL JDBC: Managing Transactions
Summary: in this tutorial, you will learn how to manage PostgreSQL transactions in Java programs using JDBC.
Steps for performing a PostgreSQL transaction in JDBC
The following are the steps for carrying out a transaction in JDBC:
Step 1. Establish a Connection
Use DriverManager.getConnection(url, user, password)
to establish a connection to your PostgreSQL database.
var connection = DriverManager.getConnection("jdbc:postgresql://your-database-host:your-database-port/your-database-name", "your-username", "your-password");
In this code, you need to replace url
, user
, and password
with your actual database connection details.
Step 2. Disable Auto-Commit
JDBC operates in auto-commit mode by default, to manually control the transaction, you need to disable the auto-commit mode using the setAutoComit()
method as follows:
connection.setAutoCommit(false);
Step 3. Perform database operations
Perform a database transaction using Statement
or PreparedStatement
within the transaction:
var statement = connection.createStatement();
// execute the statement
// ...
Step 4. Commit the transaction
Commit the changes to the database permanently if all database operations within the transaction are successful using the commit()
method of the Connection
object:
connection.commit();
Step 5. Rollback on failure
Undo the changes to the database if an exception occurs during the transaction using the rollback()
method:
connection.rollback();
Step 6. Close resources
Close the Connection
, Statement
, and ResultSet
(if applicable) to release resources.
connection.close();
statement.close();
Note that if you use the try-with-resources, you don’t need to call the close()
method explicitly to release resources.
Step 7. Exception handling
Handle exceptions appropriately using the try...catch...finally
statement and perform a rollback in case of an exception to ensure data consistency:
try {
// perform a database operation...
connection.commit();
} catch (SQLException e) {
connection.rollback();
e.printStackTrace();
} finally {
connection.close();
}
Summary
- Use the
commit()
method to apply permanent changes to the database. - Use the
rollback()
method to undo the changes.