How do you use JDBC to manage distributed transactions across multiple databases?

JDBC provides an interface for managing distributed transactions across multiple databases using the Java Transaction API (JTA). JTA allows a transaction to be coordinated across multiple resources, which in this case would be databases.

Here are the steps to use JDBC and JTA to manage distributed transactions across multiple databases:

1. Initialize the JTA transaction manager: Use a JTA-compliant transaction manager to coordinate and manage distributed transactions. There are several available, including JBoss Transaction Service, Oracle WebLogic Server Transaction Manager, and Apache Geronimo Transaction Manager.

2. Create connection pools: For each database that participates in the transaction, define a connection pool where connections to that database can be obtained. Each connection pool must be configured with a unique name and a JDBC driver.

3. Establish connections: Obtain connections to the databases in the transaction using the connection pools. The connections must be obtained from the same thread of control that began the transaction.

4. Begin the transaction: Before any database operations can be performed, the transaction must be started using the JTA transaction manager.

5. Perform database operations: Execute SQL statements or stored procedures on the databases as needed.

6. Commit the transaction: If all database operations complete successfully, commit the transaction using the JTA transaction manager. The transaction manager will ensure that all changes are committed atomically across all the participating databases.

7. Roll back the transaction: If any database operation fails, roll back the transaction using the JTA transaction manager. This will undo all changes made to the participating databases.

Here is some example code to illustrate how this process works:

    // Initialize the JTA transaction manager
    TransactionManager tm = com.arjuna.ats.jta.TransactionManager.transactionManager();
    
    // Create connection pools for each database
    DataSource ds1 = new OracleDataSource();
    ds1.setUser("user1");
    ds1.setPassword("password1");
    ds1.setURL("jdbc:oracle:thin:@database1.example.com:1521:prod");
    
    DataSource ds2 = new MySQLDataSource();
    ds2.setUser("user2");
    ds2.setPassword("password2");
    ds2.setURL("jdbc:mysql://database2.example.com:3306/prod");
    
    // Obtain connections to the databases
    Connection conn1 = ds1.getConnection();
    Connection conn2 = ds2.getConnection();
    
    // Begin the transaction
    tm.begin();
    
    // Perform database operations
    PreparedStatement ps1 = conn1.prepareStatement("UPDATE customers SET balance = balance - 10 WHERE id = ?");
    ps1.setInt(1, 123);
    ps1.execute();
    
    PreparedStatement ps2 = conn2.prepareStatement("UPDATE orders SET status = 'shipped' WHERE customer_id = ?");
    ps2.setInt(1, 123);
    ps2.execute();
    
    // Commit the transaction
    tm.commit();
    
    // Catch exceptions and roll back the transaction
} catch (Exception e) {
    tm.rollback();
}

In this example, two different databases - an Oracle database and a MySQL database - are updated as part of a single distributed transaction. The transaction is coordinated and managed by the JTA transaction manager. If any error occurs during database operations, the transaction is rolled back and all database updates are undone.