Practicing JDBC Transactions and Database Connectivity Scenarios

Course: Advanced Java Training

Topic: Practicing JDBC Transactions and Database Connectivity Scenarios

Course Link: Advanced Java Training Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to JDBC Transactions and Database Connectivity

Java Database Connectivity (JDBC) is a Java API used to connect Java applications to relational databases, execute SQL statements, retrieve results, and manage database transactions. JDBC is commonly used with databases such as MySQL, PostgreSQL, Oracle Database, and SQL Server.

Database connectivity involves establishing a connection, preparing SQL statements, executing queries, processing results, handling errors, and releasing resources. Transactions become important when multiple database operations must succeed or fail together.

For example, transferring money between two bank accounts requires a debit operation and a credit operation. If the debit succeeds but the credit fails, the application must reverse the debit to prevent inconsistent balances. JDBC transactions provide the commit and rollback mechanisms needed for this type of operation.

2. Learning Objectives

• Understand JDBC architecture and database connectivity.
• Configure a JDBC driver and establish a database connection.
• Use Connection, PreparedStatement, ResultSet, and SQLException.
• Understand auto-commit mode and transaction boundaries.
• Use commit(), rollback(), and savepoints.
• Implement practical transaction scenarios such as money transfers.
• Handle invalid credentials, unavailable databases, and SQL errors.
• Understand transaction isolation and data consistency.
• Practice database CRUD operations using JDBC.
• Apply resource management, security, and testing best practices.

3. Understanding JDBC Architecture

JDBC allows Java code to communicate with a database through a JDBC driver. The driver translates JDBC operations into the database-specific communication protocol.

Java Application
       |
       v
JDBC API
       |
       v
JDBC Driver
       |
       v
Database Server
       |
       v
SQL Execution and Results

4. Setting Up a JDBC Database Connection

To connect a Java application to a database, you need a running database server, a database and user account, a compatible JDBC driver, and a valid JDBC URL.

4.1 Example MySQL JDBC Configuration

String url = "jdbc:mysql://localhost:3306/company_db";
String username = System.getenv("DB_USERNAME");
String password = System.getenv("DB_PASSWORD");

Connection connection = DriverManager.getConnection(
    url, username, password
);

Store credentials in environment variables or a secure configuration system rather than hardcoding passwords in source code. The database URL and driver configuration may differ for PostgreSQL, Oracle, SQL Server, or other databases.

4.2 Maven Dependency for MySQL

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>YOUR_COMPATIBLE_VERSION</version>
</dependency>

Replace the version placeholder with a supported version compatible with your Java runtime and MySQL server. In production projects, manage dependency versions consistently through Maven dependency management.

4.3 Basic Connection Test

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

public class DatabaseConnectionTest {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/company_db";
        String username = System.getenv("DB_USERNAME");
        String password = System.getenv("DB_PASSWORD");

        try (Connection connection =
                 DriverManager.getConnection(url, username, password)) {
            System.out.println("Database connection successful.");
            System.out.println(
                "Database: " +
                connection.getMetaData().getDatabaseProductName()
            );
        } catch (SQLException e) {
            System.err.println("Unable to connect to the database.");
            System.err.println("SQL state: " + e.getSQLState());
            System.err.println("Error code: " + e.getErrorCode());
        }
    }
}

The try-with-resources statement closes the connection automatically. If the environment variables are missing or the database is unavailable, connection establishment may fail with an SQLException.

5. What Is a Database Transaction?

A transaction is a logical unit of database work that groups one or more operations. The transaction is committed when all required operations succeed or rolled back when the application needs to cancel the changes.

Transactions help maintain consistent data when an operation involves multiple inserts, updates, or deletes.

Example: Bank Transfer

1. Check that the sender account exists.
2. Verify that the sender has sufficient funds.
3. Subtract the transfer amount from the sender's balance.
4. Add the transfer amount to the recipient's balance.
5. Commit both updates together.
6. If an operation fails before commit, roll back the transaction.

Without a transaction, the first update could succeed while the second fails, leaving the account balances inconsistent.

6. ACID Properties of Transactions

ACID describes four important properties associated with reliable database transactions.

7. Auto-Commit Mode in JDBC

By default, a new JDBC Connection is usually in auto-commit mode. In this mode, each completed SQL statement is committed automatically as its own transaction.

Check Auto-Commit Mode

boolean autoCommit = connection.getAutoCommit();
System.out.println("Auto-commit: " + autoCommit);

Disable Auto-Commit Mode

connection.setAutoCommit(false);

When auto-commit is disabled, the application can execute multiple SQL statements within one transaction and then call commit() or rollback().

Commit a Transaction

connection.commit();

This makes the changes in the current transaction permanent, subject to the database's transaction guarantees.

Roll Back a Transaction

connection.rollback();

This cancels the uncommitted changes in the current transaction.

After completing a transaction, handle the connection state carefully. In particular, if a pooled connection is used, restore any connection settings changed by the application before returning it to the pool. Read the JDBC Connection API documentation.

8. Creating a Sample Database and Table

The following SQL creates a sample database and an accounts table for practicing transactions. These statements use MySQL syntax.

CREATE DATABASE company_db;

USE company_db;

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_holder VARCHAR(100) NOT NULL,
    balance DECIMAL(12, 2) NOT NULL,
    CHECK (balance >= 0)
);

INSERT INTO accounts (account_id, account_holder, balance)
VALUES
(101, 'Asha', 5000.00),
(102, 'Rahul', 3000.00),
(103, 'Neha', 2500.00);

The CHECK constraint is intended to prevent negative balances. Constraint support and enforcement can depend on the database product and version. Always verify the behavior of your target database.

9. Performing a Basic Transaction

This example updates two account balances within one transaction. If an error occurs, the changes are rolled back.

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

public class BasicTransactionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/company_db";
        String username = System.getenv("DB_USERNAME");
        String password = System.getenv("DB_PASSWORD");

        String debitSql =
            "UPDATE accounts SET balance = balance - ? WHERE account_id = ?";
        String creditSql =
            "UPDATE accounts SET balance = balance + ? WHERE account_id = ?";

        try (Connection connection =
                 DriverManager.getConnection(url, username, password)) {

            connection.setAutoCommit(false);

            try (PreparedStatement debit =
                     connection.prepareStatement(debitSql);
                 PreparedStatement credit =
                     connection.prepareStatement(creditSql)) {

                debit.setBigDecimal(1, new java.math.BigDecimal("500.00"));
                debit.setInt(2, 101);
                int debitRows = debit.executeUpdate();

                credit.setBigDecimal(1, new java.math.BigDecimal("500.00"));
                credit.setInt(2, 102);
                int creditRows = credit.executeUpdate();

                if (debitRows != 1 || creditRows != 1) {
                    throw new SQLException(
                        "Expected exactly one account row for each update."
                    );
                }

                connection.commit();
                System.out.println("Transaction committed.");
            } catch (SQLException | RuntimeException e) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackError) {
                    e.addSuppressed(rollbackError);
                }
                throw e;
            }
        } catch (SQLException e) {
            System.err.println("Transaction failed: " + e.getMessage());
        }
    }
}

This example demonstrates transaction control. A production transfer service must also validate the amount, prevent insufficient funds, confirm both accounts are eligible, and protect against concurrent balance changes. The following section develops a safer example.

10. Practical Scenario: Bank Account Transfer

A bank transfer is a common JDBC transaction exercise because it requires multiple database operations to succeed together.

Requirements

• The transfer amount must be greater than zero.
• The sender and recipient must be different accounts.
• Both accounts must exist.
• The sender must have sufficient funds.
• The sender's balance must be reduced by the transfer amount.
• The recipient's balance must be increased by the same amount.
• If any required operation fails, the transaction must be rolled back.

Transaction Flow

Start
  |
  v
Validate transfer amount
  |
  v
Disable auto-commit
  |
  v
Debit sender using conditional UPDATE
  |
  v
Was exactly one sender row updated?
  | Yes
  v
Credit recipient
  |
  v
Was exactly one recipient row updated?
  | Yes
  v
Commit transaction
  |
  v
Success

Any failure before commit
  |
  v
Rollback transaction
  |
  v
Report failure

Complete Transfer Method

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BankTransferService {

    public void transfer(
            Connection connection,
            int senderId,
            int recipientId,
            BigDecimal amount) throws SQLException {

        if (amount == null || amount.compareTo(BigDecimal.ZERO) <= 0) {
            throw new IllegalArgumentException(
                "Transfer amount must be greater than zero."
            );
        }

        if (senderId == recipientId) {
            throw new IllegalArgumentException(
                "Sender and recipient must be different."
            );
        }

        boolean originalAutoCommit = connection.getAutoCommit();

        try {
            connection.setAutoCommit(false);

            String debitSql =
                "UPDATE accounts " +
                "SET balance = balance - ? " +
                "WHERE account_id = ? AND balance >= ?";

            String creditSql =
                "UPDATE accounts " +
                "SET balance = balance + ? " +
                "WHERE account_id = ?";

            try (PreparedStatement debit =
                     connection.prepareStatement(debitSql);
                 PreparedStatement credit =
                     connection.prepareStatement(creditSql)) {

                debit.setBigDecimal(1, amount);
                debit.setInt(2, senderId);
                debit.setBigDecimal(3, amount);

                int debitRows = debit.executeUpdate();

                if (debitRows != 1) {
                    throw new SQLException(
                        "Sender account not found or insufficient funds."
                    );
                }

                credit.setBigDecimal(1, amount);
                credit.setInt(2, recipientId);

                int creditRows = credit.executeUpdate();

                if (creditRows != 1) {
                    throw new SQLException(
                        "Recipient account not found."
                    );
                }

                connection.commit();
            } catch (SQLException | RuntimeException e) {
                try {
                    connection.rollback();
                } catch (SQLException rollbackError) {
                    e.addSuppressed(rollbackError);
                }
                throw e;
            }
        } finally {
            if (connection.getAutoCommit() != originalAutoCommit) {
                connection.setAutoCommit(originalAutoCommit);
            }
        }
    }
}

Important: This method assumes the caller owns the connection and that the method is allowed to manage its transaction. It should not be used inside an already active caller-managed transaction because it commits or rolls back the entire connection transaction. In a connection pool or application framework, define transaction ownership clearly and ensure cleanup errors are handled appropriately.

The conditional debit prevents the update from succeeding when the available balance is too low. For applications with concurrent transfers, consider locking rows in a consistent order or using an appropriate isolation strategy to prevent deadlocks and concurrency anomalies. Database constraints remain an important additional safeguard.

11. Scenario: Handling Insufficient Funds

Suppose Asha has a balance of 5,000.00 and attempts to transfer 6,000.00. The conditional debit statement updates zero rows because the account does not have enough funds.

UPDATE accounts
SET balance = balance - 6000.00
WHERE account_id = 101
  AND balance >= 6000.00;

When no row is updated, the application must not credit the recipient. The transaction should be rolled back, and the application should report a suitable business error.

Expected Result

• Sender balance remains unchanged.
• Recipient balance remains unchanged.
• No partial transfer is committed.
• The application reports insufficient funds.

12. Scenario: Recipient Account Does Not Exist

Suppose the sender account exists, but the recipient account ID is invalid. The debit operation may succeed temporarily inside the transaction, but the credit operation updates zero rows.

Because the transfer is a single logical operation, the application must roll back the debit. Otherwise, the sender could lose money without the recipient receiving it.

if (creditRows != 1) {
    connection.rollback();
    throw new SQLException("Recipient account not found.");
}

In the complete method, rollback is performed by the error-handling block. Avoid committing a transaction after a required update has failed.

13. Scenario: Batch Database Operations

JDBC batch processing groups multiple SQL operations for execution. Batching can reduce database round trips when inserting or updating many records.

Example Batch Insert

String sql =
    "INSERT INTO accounts (account_id, account_holder, balance) " +
    "VALUES (?, ?, ?)";

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);

    try (PreparedStatement statement =
             connection.prepareStatement(sql)) {

        statement.setInt(1, 201);
        statement.setString(2, "Karan");
        statement.setBigDecimal(3, new java.math.BigDecimal("1000.00"));
        statement.addBatch();

        statement.setInt(1, 202);
        statement.setString(2, "Meera");
        statement.setBigDecimal(3, new java.math.BigDecimal("1500.00"));
        statement.addBatch();

        int[] results = statement.executeBatch();
        connection.commit();

        System.out.println("Batch executed: " + results.length);
    } catch (SQLException | RuntimeException e) {
        try {
            connection.rollback();
        } catch (SQLException rollbackError) {
            e.addSuppressed(rollbackError);
        }
        throw e;
    }
}

The example uses a DataSource to obtain a connection. A batch may partially execute before an error is reported, depending on the driver and database. Using a transaction allows the application to roll back the transaction when atomic batch behavior is required and supported by the database.

14. Scenario: Using Savepoints

A savepoint marks a position inside an active transaction. It allows an application to roll back part of the transaction while keeping earlier changes pending.

Example

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

public class SavepointExample {
    public void execute(Connection connection) throws SQLException {
        boolean originalAutoCommit = connection.getAutoCommit();

        try {
            connection.setAutoCommit(false);

            try (Statement statement = connection.createStatement()) {
                statement.executeUpdate(
                    "UPDATE accounts SET balance = balance + 100 " +
                    "WHERE account_id = 101"
                );

                Savepoint checkpoint = connection.setSavepoint("AFTER_FIRST");

                statement.executeUpdate(
                    "UPDATE accounts SET balance = balance - 100 " +
                    "WHERE account_id = 102"
                );

                connection.rollback(checkpoint);
                connection.commit();
            }
        } catch (SQLException | RuntimeException e) {
            try {
                connection.rollback();
            } catch (SQLException rollbackError) {
                e.addSuppressed(rollbackError);
            }
            throw e;
        } finally {
            if (connection.getAutoCommit() != originalAutoCommit) {
                connection.setAutoCommit(originalAutoCommit);
            }
        }
    }
}

In this example, the second update is undone by rolling back to the savepoint, while the first update remains pending and is subsequently committed. Savepoints are useful for controlled partial rollback, but their support and behavior should be checked against the target JDBC driver and database.

15. Scenario: Database Connection Failure

Database connectivity can fail for several reasons, including an unavailable server, incorrect URL, invalid credentials, network interruption, firewall restrictions, or missing JDBC driver dependencies.

Common Symptoms

• Connection refused or connection timeout.
• Authentication failure.
• Unknown database.
• Missing driver or unsupported JDBC URL.
• Connection pool exhaustion.

Troubleshooting Checklist

1. Confirm that the database server is running.
2. Verify the host, port, database name, and JDBC URL.
3. Confirm the credentials and account permissions.
4. Ensure the correct JDBC driver is on the runtime classpath.
5. Check firewall, DNS, and network access.
6. Inspect the exception message, SQLState, and vendor error code.
7. Check database logs and connection-pool metrics.

Do not expose passwords or other secrets in logs. Report a safe diagnostic message to users and keep detailed technical information in access-controlled application logs.

16. Scenario: Handling SQL Exceptions

SQLException provides details about a database operation that failed. Common diagnostic methods include getMessage(), getSQLState(), getErrorCode(), and getNextException().

try {
    // Execute a JDBC database operation.
} catch (SQLException e) {
    System.err.println("Database operation failed.");
    System.err.println("SQLState: " + e.getSQLState());
    System.err.println("Vendor code: " + e.getErrorCode());

    for (SQLException next = e.getNextException();
         next != null;
         next = next.getNextException()) {
        System.err.println("Additional database error: " +
                           next.getMessage());
    }
}

SQLState values can help classify errors, but exact behavior varies across drivers. Avoid returning raw SQL, credentials, or internal database details to end users.

17. Scenario: Preventing SQL Injection

SQL injection can occur when untrusted input is concatenated directly into an SQL string. PreparedStatement allows values to be passed as parameters, reducing this risk for parameterized values.

Unsafe Example

String sql =
    "SELECT * FROM accounts WHERE account_id = " + userInput;

Safer Example

String sql =
    "SELECT * FROM accounts WHERE account_id = ?";

try (PreparedStatement statement =
         connection.prepareStatement(sql)) {
    statement.setInt(1, accountId);

    try (java.sql.ResultSet resultSet = statement.executeQuery()) {
        while (resultSet.next()) {
            System.out.println(resultSet.getString("account_holder"));
        }
    }
}

PreparedStatement parameters represent values, not SQL identifiers such as table names or column names. If identifiers must be dynamic, validate them against a strict allowlist. Database permissions should also follow the principle of least privilege.

18. Transaction Isolation Levels

Isolation levels determine how a transaction sees data modified by other concurrent transactions. JDBC exposes standard isolation constants through the Connection interface, but actual support depends on the database and driver.

Not all drivers support every isolation level, and database products may implement different concurrency strategies. Consult the documentation for the database being used.

Reading and Setting Isolation

int currentLevel = connection.getTransactionIsolation();

connection.setTransactionIsolation(
    Connection.TRANSACTION_READ_COMMITTED
);

Choose isolation based on business requirements and performance. Higher isolation may increase locking, blocking, or retry requirements.

19. Concurrency Scenario: Two Users Updating the Same Account

Consider two users who read the same account balance at the same time and both attempt to withdraw money. If the application reads a balance, calculates a new value in Java, and later writes the result without adequate concurrency protection, one update may overwrite another.

Potential Problems

• Lost updates.
• Incorrect balances.
• Race conditions.
• Deadlocks under certain lock ordering patterns.
• Unexpected results from concurrent reads and writes.

Possible Solutions

• Use conditional SQL updates, such as checking the available balance in the UPDATE statement.
• Use row-level locking where supported and appropriate.
• Use optimistic locking with a version column when suitable.
• Select a transaction isolation level that meets the business requirements.
• Keep transactions short and acquire locks in a consistent order.
• Handle deadlocks and transient transaction failures with carefully designed retries.

Retry only when the failure is known to be retryable and the operation is safe to repeat. For financial operations, consider idempotency keys and transaction records to prevent duplicate transfers after network timeouts or uncertain commit outcomes.

20. Scenario: JDBC Resource Management

JDBC connections, statements, and result sets consume resources. They should be closed promptly to avoid connection leaks, cursor exhaustion, and other resource problems.

Recommended Pattern

String sql = "SELECT account_id, balance FROM accounts";

try (Connection connection = dataSource.getConnection();
     PreparedStatement statement = connection.prepareStatement(sql);
     java.sql.ResultSet resultSet = statement.executeQuery()) {

    while (resultSet.next()) {
        int id = resultSet.getInt("account_id");
        java.math.BigDecimal balance =
            resultSet.getBigDecimal("balance");

        System.out.println(id + ": " + balance);
    }
}

Try-with-resources closes each resource automatically. In applications using a connection pool, closing the logical connection usually returns it to the pool rather than shutting down the underlying physical connection.

21. Scenario: Using a DataSource and Connection Pool

For production applications, a DataSource is generally preferable to opening every connection directly through DriverManager. A connection pool reuses database connections and can reduce the overhead of repeatedly creating them.

Example

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class AccountRepository {
    private final DataSource dataSource;

    public AccountRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void checkConnection() throws SQLException {
        try (Connection connection = dataSource.getConnection()) {
            if (!connection.isValid(3)) {
                throw new SQLException("Connection is not valid.");
            }
        }
    }
}

Configure the DataSource and pool using the framework or application server appropriate to the project. Pool size, timeouts, leak detection, and validation settings should be based on measured workload and database capacity.

22. Scenario: Database Connectivity with Multiple SQL Statements

Sometimes a business operation requires inserting a parent record and then inserting related child records. If the child insertion fails, the parent insertion may need to be rolled back as well.

Example: Order and Order Items

connection.setAutoCommit(false);

try {
    // 1. Insert an order and obtain its generated ID.
    // 2. Insert all order items using that ID.
    // 3. Verify required update counts.
    // 4. Commit only after all required operations succeed.
    connection.commit();
} catch (SQLException | RuntimeException e) {
    try {
        connection.rollback();
    } catch (SQLException rollbackError) {
        e.addSuppressed(rollbackError);
    }
    throw e;
}

Use generated keys when the database generates the order ID. Ensure all statements use the same transaction-bound connection; otherwise, the operations may not be committed or rolled back together.

23. Testing JDBC Transactions

Transaction tests should verify both successful operations and failure scenarios. A test should inspect the resulting database state, not only the method's return value.

Testing Recommendations

• Use an isolated test database or containerized database.
• Seed predictable test data before each test.
• Verify the database state after both success and failure.
• Clean up test data reliably.
• Test the real database engine when behavior depends on its locking or isolation implementation.
• Avoid relying solely on mocks for transaction semantics.

24. Common JDBC Transaction Errors

25. Best Practices for JDBC Transactions

• Keep transactions focused and as short as practical.
• Use setAutoCommit(false) only when explicit transaction control is needed.
• Commit only after every required operation has succeeded.
• Roll back when a required operation fails before commit.
• Use PreparedStatement for SQL values.
• Use BigDecimal for monetary amounts rather than floating-point types.
• Verify update counts when exactly one row is expected.
• Use database constraints to enforce important data rules.
• Close connections, statements, and result sets reliably.
• Do not swallow exceptions or ignore rollback failures.
• Choose transaction isolation based on consistency requirements.
• Use a connection pool for most production services.
• Do not mix transaction ownership between application methods without a clear design.
• Protect credentials and avoid logging sensitive information.
• Test failure cases and concurrency scenarios, not only the happy path.

26. Practical Project: JDBC Banking Transaction System

Build a command-line banking application that uses JDBC to manage accounts and perform transfers.

Project Features

• Create and retrieve bank accounts.
• Display account balances.
• Deposit funds.
• Withdraw funds.
• Transfer funds between accounts.
• Reject invalid or negative transaction amounts.
• Prevent withdrawals that exceed the available balance.
• Record transfer history.
• Roll back failed multi-statement operations.
• Handle database connection errors and invalid account IDs.

Suggested Tables

accounts
--------
account_id
account_holder
balance

transfers
---------
transfer_id
sender_account_id
recipient_account_id
amount
created_at
status

Define appropriate foreign keys, constraints, and indexes for the selected database. Transfer history and account updates should be designed so that the application can recover from failures and avoid recording misleading transaction states.

Suggested Development Steps

1. Create the database and tables.
2. Configure the JDBC driver and database credentials.
3. Implement connection management.
4. Implement account lookup and balance retrieval.
5. Implement deposit and withdrawal operations.
6. Implement transfers using explicit transactions.
7. Add transaction history and appropriate constraints.
8. Test success, failure, and concurrency scenarios.
9. Document setup instructions and known database requirements.

27. Exercises for Practice

1. Create a JDBC connection and print the database product name.
2. Insert, update, retrieve, and delete a sample account using PreparedStatement.
3. Disable auto-commit and execute two related updates.
4. Force the second update to fail and verify that the first update is rolled back.
5. Implement a transfer that rejects insufficient funds.
6. Implement a transfer that handles a nonexistent recipient.
7. Use a savepoint to roll back only part of a transaction.
8. Insert multiple records using JDBC batch processing.
9. Test connection failures and log useful diagnostic details safely.
10. Simulate concurrent withdrawals and verify the balance remains valid.
11. Write integration tests that inspect database state after rollback.
12. Review resource handling and confirm that connections are always returned or closed.

28. Interview Questions and Answers

Q1. What is JDBC?

JDBC is a Java API for connecting to relational databases and executing SQL statements.

Q2. What is a database transaction?

A transaction is a logical unit of work whose changes are committed together or rolled back when cancellation is required.

Q3. What is auto-commit mode?

Auto-commit mode usually commits each completed SQL statement automatically. Disable it when multiple statements must be grouped into a transaction.

Q4. What is the purpose of commit()?

The commit() method makes the current transaction's changes permanent according to the database's transaction guarantees.

Q5. What is the purpose of rollback()?

The rollback() method cancels uncommitted changes in the current transaction.

Q6. What are ACID properties?

ACID stands for Atomicity, Consistency, Isolation, and Durability.

Q7. What is a JDBC savepoint?

A savepoint marks a position in a transaction so that changes after it can be rolled back without necessarily discarding earlier changes.

Q8. What is PreparedStatement?

PreparedStatement executes parameterized SQL and helps protect against SQL injection when values are supplied through its parameters.

Q9. What happens if a statement fails during a transaction?

The application should determine whether the transaction can safely continue. If a required operation fails, it should normally roll back the transaction and handle the error.

Q10. What is transaction isolation?

Transaction isolation controls how concurrent transactions interact and what data changes they can observe.

Q11. What is a connection pool?

A connection pool manages reusable database connections, reducing the overhead of creating a new physical connection for every operation.

Q12. Why should JDBC resources be closed?

Closing resources releases database connections, statements, and result sets, helping prevent resource leaks.

Q13. How can SQL injection be prevented?

Use parameterized PreparedStatement queries, validate input, and restrict database permissions.

Q14. Why use BigDecimal for money?

BigDecimal provides decimal arithmetic suitable for monetary values and avoids the binary floating-point rounding behavior of float and double.

Q15. How should a money transfer be implemented?

Validate the request, perform both account updates on the same transaction-bound connection, verify the required update counts, commit only when all steps succeed, and roll back on failure.

29. Quick Revision Checklist

• Understand JDBC architecture and connection setup.
• Know the purpose of Connection, PreparedStatement, and ResultSet.
• Understand auto-commit, commit, and rollback.
• Know the ACID transaction properties.
• Practice bank transfers and multi-statement transactions.
• Understand savepoints and isolation levels.
• Handle SQL exceptions and connection failures safely.
• Use parameterized SQL and close resources properly.
• Test transaction failures and concurrent updates.
• Use constraints and appropriate database design to protect data integrity.

30. Conclusion

Practicing JDBC transactions and database connectivity scenarios helps Java developers build reliable applications that communicate safely with relational databases. Understanding connections, prepared statements, transactions, commits, rollbacks, savepoints, isolation levels, and exception handling is essential for maintaining data integrity.

Practical exercises such as bank transfers, batch inserts, account updates, and failure recovery demonstrate why transaction boundaries must be carefully designed. Combined with secure SQL practices, proper resource management, database constraints, and realistic integration tests, these skills form a strong foundation for enterprise Java development.

Learn More with JustAcademy

Explore the Advanced Java Training Course to continue learning about Java, JDBC, database programming, Spring Boot, and enterprise application development.

Register for a Course Demo to learn more about the training.

Visit the JustAcademy Website for more courses and learning resources.

Additional JDBC References

• Oracle Java Tutorial: Using JDBC Transactions
• Java Connection API Documentation
• Microsoft JDBC Driver: Performing Transactions