Distributed transaction examples|V2.4.16|OceanBase Connector/J |docs|OceanBase Documentations

This topic describes examples of how to use the Oracle eXtended Architecture (XA) feature to implement distributed transactions.

Import Oracle XA

To use the Oracle XA feature, import the following content:

import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

The oracle.jdbc.pool package has classes for the connection pool feature, with XA-related classes as subclasses.

Alternatively, if the code is to be run inside the database and you need to access the database for SQL operations, import oracle.jdbc.xa.server instead of oracle.jdbc.xa.client. The syntax is as follows:

import oracle.jdbc.xa.server.*;

Both the client and server packages provide versions of the OracleXADataSource, OracleXAConnection, and OracleXAResource classes. The abstract versions of these three classes are in the top-level oracle.jdbc.xa package.

Oracle XA code examples

The following example demonstrates how to process an XA transaction:

Use XA START to start an XA transaction and set it to the ACTIVE state.
For an XA transaction in the ACTIVE state, execute the SQL statements that constitute the transaction and then execute the XA END statement. The XA END statement sets the transaction to the IDLE state.
For an XA transaction in the IDLE state, you can execute the XA PREPARE or XA COMMIT ... ONE PHASE statement.
- The XA PREPARE statement sets the transaction to the PREPARED state. In this case, the XA RECOVER statement includes the value of xid of the transaction in its output, because XA RECOVER lists all XA transactions in the PREPARED state.
- The XA COMMIT ... ONE PHASE statement prepares and commit the transaction. In this case, the XA RECOVER statement does not list the value of xid because the transaction is terminated.
For an XA transaction in the PREPARED state, you can execute the XA COMMIT statement to commit and terminate the transaction, or execute XA ROLLBACK to roll back and terminate the transaction.

public static void initClass() throws SQLException {
    createTable(tableName1, "c1 int,c2 int");
    createTable(tableName2, "c1 int,c2 int");
    createTable(tableName3, "c1 varchar(200)");
}

public void obOracleXAOne() throws Exception {
    Assume.assumeTrue(sharedUsePrepare());
    Connection conn = setConnection();  
    conn.createStatement().execute(" insert into " + tableName1 + "  values(1,2)");
    JDBC4MysqlXAConnection mysqlXAConnection = new JDBC4MysqlXAConnection(
        (OceanBaseConnection) conn);
    String gtridStr = "gtrid_test_wgs_ob_oracle_xa_one";
    String bqualStr = "bqual_test_wgs_ob_oracle_xa_one";
    Xid xid = new MysqlXid(gtridStr.getBytes(), bqualStr.getBytes(), 123);
    try {
        mysqlXAConnection.start(xid, XAResource.TMNOFLAGS);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        pstmt = conn.prepareStatement("select c1 from " + tableName1);
        rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }
        pstmt.close();
        pstmt = conn.prepareStatement("insert into " + tableName1 + " (c1, c2) values(?, ?)");
        pstmt.setInt(1, 12);
        pstmt.setInt(2, 12);
        pstmt.executeUpdate();
        mysqlXAConnection.end(xid, XAResource.TMSUCCESS);
        mysqlXAConnection.prepare(xid);
        mysqlXAConnection.commit(xid, false);
    } catch (Exception e) {
        e.printStackTrace();
        mysqlXAConnection.rollback(xid);
        throw e;
    }
}

public void obOracleXAOnePhase() throws Exception {
    Assume.assumeTrue(sharedUsePrepare());
    Connection conn = null;

    conn = setConnection();
    conn.createStatement().execute(" insert into " + tableName2 + "  values(1,2)");

    JDBC4MysqlXAConnection mysqlXAConnection = new JDBC4MysqlXAConnection(
        (OceanBaseConnection) conn);
    String gtridStr = "gtrid_test_wgs_ob_oracle_xa_one_phase";
    String bqualStr = "bqual_test_wgs_ob_oracle_xa_one_phase";

    Xid xid = new MysqlXid(gtridStr.getBytes(), bqualStr.getBytes(), 123);
    try {
        mysqlXAConnection.start(xid, XAResource.TMNOFLAGS);
        // ps test
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        pstmt = conn.prepareStatement("select c1 from " + tableName2 + "");
        rs = pstmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getInt(1));
        }

        pstmt.close();

        pstmt = conn.prepareStatement("insert into " + tableName2 + " (c1, c2) values(?, ?)");
        pstmt.setInt(1, 12);
        pstmt.setInt(2, 12);
        pstmt.executeUpdate();

        mysqlXAConnection.end(xid, XAResource.TMSUCCESS);
        mysqlXAConnection.commit(xid, true);
    } catch (Exception e) {
        mysqlXAConnection.rollback(xid);
        throw e;
    }
}

Note

Configure useServerPrepStmts=true in setConnection(), similar to how it appears in the URL format: jdbc:oceanbase://xx:xx/xx?useServerPrepStmts=true&....