TransactionGuardExample.java

import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.LogicalTransactionId;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import java.util.Properties;

public class TransactionGuardExample
{
        private static OracleConnection Connect()
        throws SQLException
        {
                OracleDataSource ods = new OracleDataSource();

                String url = "jdbc:oracle:thin:@//192.168.1.17:1521/wailua_hr";
                ods.setURL(url);
                ods.setUser("hr");
                ods.setPassword("hr123");

                OracleConnection conn = (OracleConnection) ods.getConnection();
                System.out.println("connected as hr");
                conn.setAutoCommit(false);

                displayLogicalTransactionId( conn );

                return conn;
        }

        public static void displayLogicalTransactionId( LogicalTransactionId ltxid )
        throws SQLException
        {
                if ( ltxid == null )
                {
                        throw new RuntimeException ("Can't get LogicalTransactionId. Set the service COMMIT_OUTCOME to true and try again.");
                }
                byte[] barray = ltxid.getBytes();

                StringBuilder sb = new StringBuilder();
                for(byte b : barray){
                        sb.append(String.format("%02x", b & 0xff));
                }
                System.out.println( "trans id " + sb );
        }

        public static void displayLogicalTransactionId( OracleConnection conn )
        throws SQLException
        {
                displayLogicalTransactionId( conn.getLogicalTransactionId() );
        }

        private static void killSession ( OracleConnection conn )
        throws SQLException
        {
                OracleConnection sconn = ConnectSys();
                Integer sidary[] = Sid( conn );

                String sql = "alter system kill session '" + sidary[ 0 ] + "," + sidary[ 1 ] + "' immediate";
                OraclePreparedStatement pstmt = ( OraclePreparedStatement ) sconn.prepareStatement( sql );

                pstmt.execute();
                System.out.println( "sid " + sidary[ 0 ] + " ser " + sidary[ 1 ] + " killed" );
        }

        private static OracleConnection ConnectSys()
        throws SQLException
        {
                OracleDataSource ods = new OracleDataSource();

                String url = "jdbc:oracle:thin:@//192.168.1.17:1521/wailua";
                ods.setURL(url);
                Properties prop = new Properties();

                prop.setProperty("user","sys");
                prop.setProperty("password","sys.n1te");
                prop.setProperty("internal_logon","sysdba");
                ods.setConnectionProperties(prop);
                OracleConnection sconn = (OracleConnection) ods.getConnection();
                System.out.println("connected as sys");

                return sconn;
        }

        private static Integer[] Sid ( OracleConnection conn )
        throws SQLException
        {
                String sql = "select sid, serial# ser from v$session where sid = ( select sid from v$mystat where rownum <= 1 )";
                OraclePreparedStatement pstmt = ( OraclePreparedStatement ) conn.prepareStatement( sql );
                OracleResultSet rs = ( OracleResultSet ) pstmt.executeQuery();
                Integer sid = null;
                Integer ser = null;
                while ( rs.next() )
                {
                        sid = rs.getInt( 1 );
                        ser = rs.getInt( 2 );

                }

                Integer sidary[] = { sid, ser };
                return sidary;
        }

        private static void displayOutcome( OracleConnection conn, LogicalTransactionId ltxid )
        throws SQLException
        {
                String sql = "declare"
                        + "     l_comm boolean;"
                        + "     l_compl boolean;"
                        + "begin"
                        + "     dbms_app_cont.get_ltxid_outcome ( ?, l_comm, l_compl);"
                        + "     ? := case when l_comm then 1 else 0 end;"
                        + "     ? := case when l_compl then 1 else 0 end;"
                        + "end;";

                OracleCallableStatement cstmt = ( OracleCallableStatement ) conn.prepareCall(sql);
                cstmt.setBytes(1, ltxid.getBytes());
                cstmt.registerOutParameter(2, OracleTypes.NUMBER);
                cstmt.registerOutParameter(3, OracleTypes.NUMBER);

                cstmt.execute();

                Boolean committed = cstmt.getInt(2) == 1;
                Boolean callcompleted = cstmt.getInt(3) == 1;

                System.out.println("LTXID committed? " + committed);
                System.out.println("User call completed? " + callcompleted);
        }

        public static void main(String[] args)
        throws Exception
        {
                OracleConnection oconn = Connect();

                String sql = "update employees set first_name = ? where employee_id = ?";
                OraclePreparedStatement pstmt = ( OraclePreparedStatement ) oconn.prepareStatement( sql );

                pstmt.setString( 1 , "Taylor" );
                pstmt.setInt( 2 , 107 );
                pstmt.execute();

                System.out.println("after execute:");
                displayLogicalTransactionId( oconn );
                LogicalTransactionId saveltxid = oconn.getLogicalTransactionId();
                System.out.println("saveltxid:");
                displayLogicalTransactionId( saveltxid );
                // oconn.commit();
                killSession( oconn );

                OracleConnection nconn = Connect();
                System.out.println("after reconnect, saveltxid:");
                displayLogicalTransactionId( saveltxid );
                displayOutcome( nconn, saveltxid );
        }
}