20
Sample Applications

This chapter presents sample applications covering a range of both standard and Oracle-specific JDBC features, categorized as follows:

• Basic Samples

• Samples of PL/SQL in JDBC

• Intermediate Samples

• Samples for JDBC 2.0 Types

• Samples for Oracle Type Extensions

• Samples for Custom Object Classes

• JDBC 2.0 Result Set Enhancement Samples

• Performance Enhancement Samples

• Samples for Connection Pooling and Distributed Transactions

• Sample Applet

• JDBC versus SQLJ Sample Code

These samples are located in subdirectories under the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples

Note: Aside from the sample applet, which requires the Thin driver, all samples in this chapter work with any JDBC driver. Do not be misled by the fact that most of the samples are located under the oci8 directory on the product CD.

Basic Samples

This section provides elementary samples that print employee information from a table and insert employee information into the table.

• Listing Names from the EMP Table--Employee.java

• Inserting Names into the EMP Table--InsertExample.java

These samples are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

For a step-by-step discussion of basic JDBC functionality, see "First Steps in JDBC".

Listing Names from the EMP Table--Employee.java

This example retrieves and prints all the employee names from the EMP table.

Note: Do not confuse this Employee.java with the one used later as an example of a ORAData implementation of a custom Java class.

/*
 * This sample shows how to list all the names from the EMP table
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

class Employee
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();   
  }
}

Inserting Names into the EMP Table--InsertExample.java

This sample uses a prepared statement to insert new employee rows into the EMP table.

/*
 * This sample shows how to insert data in a table.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

class InsertExample
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Prepare a statement to cleanup the emp table
    Statement stmt = conn.createStatement ();
    try
    {
      stmt.execute ("delete from EMP where EMPNO = 1500");
    }
    catch (SQLException e)
    {
      // Ignore an error here
    }

    try
    {
      stmt.execute ("delete from EMP where EMPNO = 507");
    }
    catch (SQLException e)
    {
      // Ignore an error here too
    }

    // Close the statement
    stmt.close();

    // Prepare to insert new names in the EMP table
    PreparedStatement pstmt = 
      conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");

    // Add LESLIE as employee number 1500
    pstmt.setInt (1, 1500);          // The first ? is for EMPNO
    pstmt.setString (2, "LESLIE");   // The second ? is for ENAME
    // Do the insertion
    pstmt.execute ();

    // Add MARSHA as employee number 507
    pstmt.setInt (1, 507);           // The first ? is for EMPNO
    pstmt.setString (2, "MARSHA");   // The second ? is for ENAME
    // Do the insertion
    pstmt.execute ();

    // Close the statement
    pstmt.close();

    // Close the connecion
    conn.close();

  }
}

Samples of PL/SQL in JDBC

The following examples demonstrate the interoperability between PL/SQL and JDBC, contrasting standard SQL92 calling syntax with Oracle PL/SQL block syntax:

• Executing Procedures in PL/SQL Blocks--PLSQL.java

• Calling PL/SQL Stored Procedures--PLSQLExample.java

These samples are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

For related discussion, see "PL/SQL Stored Procedures".

Calling PL/SQL Stored Procedures--PLSQLExample.java

This sample defines a stored function and executes it using SQL92 CALL syntax in a callable statement. The function takes an employee name and salary as input and raises the salary by a set amount.

/*
 * This sample shows how to call a PL/SQL stored procedure using the SQL92
 * syntax.  See also the other sample PLSQL.java.
 */

import java.sql.*;
import java.io.*;

class PLSQLExample
{
  public static void main (String args [])
       throws SQLException, IOException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a statement
    Statement stmt = conn.createStatement ();

    // Create the stored function
    stmt.execute ("create or replace function RAISESAL (name CHAR, raise NUMBER)
                 return NUMBER is begin return raise + 100000; end;");

    // Close the statement
    stmt.close();

    // Prepare to call the stored procedure RAISESAL.
    // This sample uses the SQL92 syntax
    CallableStatement cstmt = conn.prepareCall ("{? = call RAISESAL (?, ?)}");

    // Declare that the first ? is a return value of type Int
    cstmt.registerOutParameter (1, Types.INTEGER);

    // We want to raise LESLIE's salary by 20,000
    cstmt.setString (2, "LESLIE");  // The name argument is the second ?
    cstmt.setInt (3, 20000);        // The raise argument is the third ?
  
    // Do the raise
    cstmt.execute ();

    // Get the new salary back
    int new_salary = cstmt.getInt (1);

    System.out.println ("The new salary is: " + new_salary);

    // Close the statement
    cstmt.close();

    // Close the connection
    conn.close();
  }
}

Executing Procedures in PL/SQL Blocks--PLSQL.java

This sample defines PL/SQL stored procedures and functions and executes them from within Oracle PL/SQL BEGIN...END blocks in callable statements. Stored procedures and functions with input, output, input-output, and return parameters are shown.

/*
 * This sample shows how to call PL/SQL blocks from JDBC.
 */

import java.sql.*;

class PLSQL
{
  public static void main (String args [])
       throws SQLException, ClassNotFoundException
  {
    // Load the driver
    Class.forName ("oracle.jdbc.OracleDriver");

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create the stored procedures
    init (conn);

    // Cleanup the plsqltest database
    Statement stmt = conn.createStatement ();
    stmt.execute ("delete from plsqltest");
 
    // Close the statement
    stmt.close();

    // Call a procedure with no parameters
    {
      CallableStatement procnone = conn.prepareCall ("begin procnone; end;");
      procnone.execute ();
      dumpTestTable (conn);
      procnone.close();
    }

    // Call a procedure with an IN parameter
    {
      CallableStatement procin = conn.prepareCall ("begin procin (?); end;");
      procin.setString (1, "testing");
      procin.execute ();
      dumpTestTable (conn);
      procin.close();
    }

    // Call a procedure with an OUT parameter
    {
      CallableStatement procout = conn.prepareCall ("begin procout (?); end;");
      procout.registerOutParameter (1, Types.CHAR);
      procout.execute ();
      System.out.println ("Out argument is: " + procout.getString (1));
      procout.close();
    }
    
    // Call a procedure with an IN/OUT prameter
    {
      CallableStatement procinout = conn.prepareCall 
                                    ("begin procinout (?); end;");
      procinout.registerOutParameter (1, Types.VARCHAR);
      procinout.setString (1, "testing");
      procinout.execute ();
      dumpTestTable (conn);
      System.out.println ("Out argument is: " + procinout.getString (1));
      procinout.close();
    }

    // Call a function with no parameters
    {
      CallableStatement funcnone = conn.prepareCall 
                                   ("begin ? := funcnone; end;");
      funcnone.registerOutParameter (1, Types.CHAR);
      funcnone.execute ();
      System.out.println ("Return value is: " + funcnone.getString (1));
      funcnone.close();
    }

    // Call a function with an IN parameter
    {
      CallableStatement funcin = conn.prepareCall 
                                 ("begin ? := funcin (?); end;");
      funcin.registerOutParameter (1, Types.CHAR);
      funcin.setString (2, "testing");
      funcin.execute ();
      System.out.println ("Return value is: " + funcin.getString (1));
      funcin.close();
    }

    // Call a function with an OUT parameter
    {
      CallableStatement funcout = conn.prepareCall 
                                  ("begin ? := funcout (?); end;");
      funcout.registerOutParameter (1, Types.CHAR);
      funcout.registerOutParameter (2, Types.CHAR);
      funcout.execute ();
      System.out.println ("Return value is: " + funcout.getString (1));
      System.out.println ("Out argument is: " + funcout.getString (2));
      funcout.close();
    }

    // Close the connection
    conn.close();
  }

  // Utility function to dump the contents of the PLSQLTEST table and
  // clear it
  static void dumpTestTable (Connection conn)
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from plsqltest");
    while (rset.next ())
      System.out.println (rset.getString (1));
    stmt.execute ("delete from plsqltest");
    rset.close();
    stmt.close();
  }

  // Utility function to create the stored procedures
  static void init (Connection conn)
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    try { stmt.execute ("drop table plsqltest"); } catch (SQLException e) { }
    stmt.execute ("create table plsqltest (x char(20))");
    stmt.execute ("create or replace procedure procnone 
                   is begin insert into plsqltest values ('testing'); end;");
    stmt.execute ("create or replace procedure procin (y char) 
                   is begin insert into plsqltest values (y); end;");
    stmt.execute ("create or replace procedure procout (y out char) 
                   is begin y := 'tested'; end;");
    stmt.execute ("create or replace procedure procinout (y in out varchar) 
                  is begin insert into plsqltest values (y); 
                  y := 'tested'; end;");

    stmt.execute ("create or replace function funcnone return char 
                   is begin return 'tested'; end;");
    stmt.execute ("create or replace function funcin (y char) return char 
                  is begin return y || y; end;");
    stmt.execute ("create or replace function funcout (y out char) return char
                 is begin y := 'tested'; return 'returned'; end;");
    stmt.close();
  }
}

Accessing a PL/SQL Index-by Table from JDBC--PLSQLIndexTab.java

This code example shows special input binding and output registration methods in the IN, OUT (including function return values), and IN OUT parameter modes.

For a complete discussion of this topic, see "Accessing PL/SQL Index-by Tables".

/* 
 * This sample demonstrates how to make PL/SQL calls with index-by table 
 * parameters
 */ 

// You need to import java.sql, oracle.sql and oracle.jdbc packages to use
import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;

class PLSQLIndexTab
{
  public static void main (String args [])
       throws SQLException
  {

    String [] plSqlIndexArrayIn = {"string1","string2","string3"};
    int currentLen = plSqlIndexArrayIn.length;
    int maxLen = currentLen;
    int elementMaxLen = 20;

    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    String url = "jdbc:oracle:oci8:@";
    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }
    
    // Connect to the database
    Connection conn =
      DriverManager.getConnection (url, "scott", "tiger");

    // Create the procedures which use Index-by Table as IN/OUT parameters
    createProc_Func(conn);

    // Call a procedure with an IN parameter
    System.out.println ("Call a procedure with an IN parameter");
    OracleCallableStatement cs = 
        (OracleCallableStatement) conn.prepareCall ("begin proc_in (?, ?); 
end;");

    // Use setPlsqlIndexTable() to set the Index-by Table parameter
    cs.setPlsqlIndexTable (1, plSqlIndexArrayIn, maxLen, 
                               currentLen, OracleTypes.VARCHAR, elementMaxLen);

    // Register OUT paramater
    cs.registerOutParameter (2, Types.CHAR);

    // Call the procedure
    cs.execute ();

    // Display the Status
    System.out.println ("Status = " + cs.getString (2));

    // Call a procedure with an OUT parameter
    System.out.println ("Call a procedure with an OUT parameter");
    cs = (OracleCallableStatement) conn.prepareCall ("begin proc_out (?); 
end;");

    // Use setPlsqlIndexTable() to set the Index-by Table parameter
    cs.registerIndexTableOutParameter (1, maxLen, OracleTypes.VARCHAR,
    elementMaxLen);

    // Call the procedure
    cs.execute ();

    // Display the OUT value
    Datum[] val = cs.getOraclePlsqlIndexTable (1);
    for (int i = 0; i < val.length; i++)
        System.out.println ("Value = " + val[i].stringValue());

    // Call a procedure with IN/OUT parameter
    System.out.println ("Call a procedure with IN/OUT parameter");

    cs = (OracleCallableStatement) conn.prepareCall ("begin proc_inout (?, ?); 
end;");

    // Use setPlsqlIndexTable() to set the Index-by Table parameter
    cs.setPlsqlIndexTable (1, plSqlIndexArrayIn, maxLen, 
                               currentLen, OracleTypes.VARCHAR, elementMaxLen);

    // Register OUT paramater
    cs.registerIndexTableOutParameter (1, maxLen, OracleTypes.VARCHAR,
    elementMaxLen);
    cs.registerOutParameter (2, Types.CHAR);

    // Call the procedure
    cs.execute ();

    // Display the Status
    System.out.println ("Status = " + cs.getString (2));

    // Display the OUT value
    val = cs.getOraclePlsqlIndexTable (1);
    for (int i = 0; i < val.length; i++)
        System.out.println ("Value = " + val[i].stringValue());

    // Call the Function 
    System.out.println ("Call the function");

    cs = (OracleCallableStatement) conn.prepareCall ("begin ? := func (?); 
end;");

    // Use setPlsqlIndexTable() to set the Index-by Table parameter
    cs.setPlsqlIndexTable (2, plSqlIndexArrayIn, maxLen,
                               currentLen, OracleTypes.VARCHAR, elementMaxLen);
  
    // Register OUT paramater
    cs.registerIndexTableOutParameter (1, maxLen, OracleTypes.VARCHAR, 
    elementMaxLen);

    // Call the procedure
    cs.execute ();

    val = cs.getOraclePlsqlIndexTable (1);
    for (int i = 0; i < val.length; i++)
        System.out.println ("Value = " + val[i].stringValue());

    // Close the Callable Statement 
    cs.close(); 

    // Dump the contents of the demo_tab
    System.out.println ("Dump the demo_tab table");
    dumpTable(conn);

    // Clean up the schema
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  private static void createProc_Func (Connection conn)
      throws SQLException
  {
    // Cleanup the schema
    cleanup(conn);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Create the Table
    stmt.execute("CREATE TABLE demo_tab (col1 VARCHAR2(20))");

    // Use PL/SQL to create the Package
    String plsql1 = "CREATE OR REPLACE PACKAGE pkg AS " +
                    "  TYPE indexByTab IS TABLE OF VARCHAR2(20) INDEX BY 
                       BINARY_INTEGER; " +
                    "END;";

    stmt.execute(plsql1);

    // Create a procedure to use the Index-by Table as IN paramater
    String plsql2 = "CREATE OR REPLACE PROCEDURE proc_in (p1 IN pkg.indexByTab, 
    status OUT VARCHAR2) IS " +
                    "BEGIN " +
                    "  FOR i in 1..3 LOOP " +
                    "    INSERT INTO demo_tab VALUES (p1(i)); " +
                    "  END LOOP; " +
                    "  IF ((p1(1)='string1') AND (p1(2)='string2') AND 
                          (p1(3)='string3')) " +
                    "     THEN status := 'Values passed in correctly'; " +
                    "  ELSE " +
                    "      status := 'Values passed in are incorrect'; " +
                    "  END IF; " +
                    "END;";

    stmt.execute(plsql2);

    // Create a procedure to use the Index-by Table as OUT paramater
    String plsql3 = "CREATE OR REPLACE PROCEDURE proc_out (p1 OUT
                     pkg.indexByTab) IS " +
                    "BEGIN " +
                    "  p1(1) := 'string1'; " +
                    "  p1(2) := 'string2'; " +
                    "  p1(3) := 'string3'; " +
                    "END;";

    stmt.execute(plsql3);

    // Create a procedure to use the Index-by Table as both IN and OUT paramater
    String plsql4 = "CREATE OR REPLACE PROCEDURE proc_inout (p1 IN OUT 
                     pkg.indexByTab, status OUT VARCHAR2) IS " +
                    "BEGIN " +
                    "  FOR i in 1..3 LOOP " +
                    "    INSERT INTO demo_tab VALUES (p1(i)); " +
                    "  END LOOP; " +
                    "  IF ((p1(1)='string1') AND (p1(2)='string2') AND
                         (p1(3)='string3')) " +
                    "     THEN status := 'Values passed in correctly'; " +
                    "  ELSE " +
                    "      status := 'Values passed in are incorrect'; " +
                    "  END IF; " +
                    "  p1(1) := 'string4'; " +
                    "  p1(2) := 'string5'; " +
                    "  p1(3) := 'string6'; " +
                    "END;";

    stmt.execute(plsql4);

    String plsql5 = "CREATE OR REPLACE FUNCTION func (p1 pkg.indexByTab) RETURN
                     pkg.indexByTab IS " +
                    "  n pkg.indexByTab; " +
                    "BEGIN " +
                    "  FOR i in 1..3 LOOP " +
                    "    INSERT INTO demo_tab VALUES (p1(i)); " +
                    "  END LOOP; " +
                    "  IF ((p1(1)='string1') AND (p1(2)='string2') AND
                          (p1(3)='string3')) THEN " +
                    "     n(1) := 'p1(1) correct'; " +
                    "     n(2) := 'p1(2) correct'; " +
                    "     n(3) := 'p1(3) correct'; " +
                    "  ELSE " +
                    "     n(1) := 'p1(1) wrong'; " +
                    "     n(2) := 'p1(2) wrong'; " +
                    "     n(3) := 'p1(3) wring'; " +
                    "  END IF; " +
                    "  RETURN n; " +
                    "END;";

    stmt.execute(plsql5);

    // Close the statement
    stmt.close();
  }
 
  /**
   * Utility function to dump the contents of the "demo_tab" table
   */
  static void dumpTable (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    ResultSet rset = stmt.executeQuery ("select * from demo_tab");
    while (rset.next ())
      System.out.println (rset.getString (1));
    rset.close();
    stmt.close();
  }

  /**
   * Cleanup data structures created in this example
   */
  static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();

    try {
      stmt.execute ("DROP TABLE demo_tab");
    } catch (SQLException e) {}

    try {
      stmt.execute ("DROP PROCEDURE proc_in");
    } catch (SQLException e) {}

    try {
      stmt.execute ("DROP PROCEDURE proc_out");
    } catch (SQLException e) {}

    try {
      stmt.execute ("DROP PROCEDURE proc_inout");
    } catch (SQLException e) {}

    try {
      stmt.execute ("DROP PROCEDURE func");
    } catch (SQLException e) {}

    try {
      stmt.execute ("DROP PACKAGE pck");
    } catch (SQLException e) {}

    stmt.close ();
  }

}

Intermediate Samples

Samples in this section demonstrate intermediate-level JDBC functionality.

• Streams--StreamExample.java

• Multithreading--JdbcMTSample.java

These samples are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

Streams--StreamExample.java

The JDBC drivers support the manipulation of data streams in both directions between client and server. The code sample in this section demonstrates this by connecting to a database and inserting and fetching LONG data using standard JDBC stream API.

For a complete discussion of this topic, see "Java Streams in JDBC".

/*
 * This example shows how to stream data from the database
 */

import java.sql.*;
import java.io.*;

class StreamExample
{
  public static void main (String args [])
       throws SQLException, IOException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // It's faster when you don't commit automatically
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Create the example table
    try
    {
      stmt.execute ("drop table streamexample");
    }
    catch (SQLException e)
    {
      // An exception would be raised if the table did not exist
      // We just ignore it
    }

    // Create the table
    stmt.execute ("create table streamexample 
                   (NAME varchar2 (256), DATA long)");

    // Let's insert some data into it.  We'll put the source code
    // for this very test in the database.
    File file = new File ("StreamExample.java");
    InputStream is = new FileInputStream ("StreamExample.java");
    PreparedStatement pstmt = 
      conn.prepareStatement ("insert into streamexample 
                              (data, name) values (?, ?)");
    pstmt.setAsciiStream (1, is, (int)file.length ());
    pstmt.setString (2, "StreamExample");
    pstmt.execute ();

    // Do a query to get the row with NAME 'StreamExample'
    ResultSet rset = 
      stmt.executeQuery ("select DATA from streamexample where
                          NAME='StreamExample'");
    
    // Get the first row
    if (rset.next ())
    {
      // Get the data as a Stream from Oracle to the client
      InputStream gif_data = rset.getAsciiStream (1);

      // Open a file to store the gif data
      FileOutputStream os = new FileOutputStream ("example.out");
      
      // Loop, reading from the gif stream and writing to the file
      int c;
      while ((c = gif_data.read ()) != -1)
        os.write (c);

      // Close the file
      os.close ();
    }
  
    // Close all the resources
    if (rset != null)
      rset.close();
    
    if (stmt != null)
      stmt.close();
    
    if (pstmt != null)
      pstmt.close();

    if (conn != null)
      conn.close();
  }
}

Multithreading--JdbcMTSample.java

The Oracle JDBC drivers provide full support for programs that use Java multithreading. The following sample program creates a specified number of threads and lets you determine whether or not the threads will share a connection. If you choose to share the connection, then the same JDBC connection object will be used by all threads (each thread will have its own statement object, however).

Because all Oracle JDBC API methods (except the cancel() method) are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use.

The program displays each thread ID and the employee name and employee ID associated with that thread.

This sample is repeated in "JDBC and Multithreading".

/*
 * This sample is a  multi-threaded JDBC program.
 */

import java.sql.*;
import oracle.jdbc.OracleStatement;

public class JdbcMTSample extends Thread
{
  // Default no of threads to 10
  private static int NUM_OF_THREADS = 10;

  int m_myId;

  static  int c_nextId = 1;
  static  Connection s_conn = null;
  static  boolean   share_connection = false;

  synchronized static int getNextId()
  {
      return c_nextId++;
  }

  public static void main (String args [])
  {
    try  
    {  
      /* Load the JDBC driver */
      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
  
      // If NoOfThreads is specified, then read it
      if ((args.length > 2)  || 
           ((args.length > 1) && !(args[1].equals("share"))))
      {
         System.out.println("Error: Invalid Syntax. ");
         System.out.println("java JdbcMTSample [NoOfThreads] [share]");
         System.exit(0);
      }

      if (args.length > 1) 
      {
         share_connection = true;
         System.out.println
                ("All threads will be sharing the same connection");
      }
  
      // get the no of threads if given
      if (args.length > 0)
         NUM_OF_THREADS = Integer.parseInt (args[0]);
  
      // get a shared connection
      if (share_connection)
          s_conn = DriverManager.getConnection
                                 ("jdbc:oracle:oci8:@", "scott","tiger");
  
      // Create the threads
      Thread[] threadList = new Thread[NUM_OF_THREADS];

      // spawn threads
      for (int i = 0; i < NUM_OF_THREADS; i++)
      {
          threadList[i] = new JdbcMTSample();
          threadList[i].start();
      }
    
      // Start everyone at the same time
      setGreenLight ();

      // wait for all threads to end
      for (int i = 0; i < NUM_OF_THREADS; i++)
      {
          threadList[i].join();
      }

      if (share_connection)
      {
          s_conn.close();
          s_conn = null;
      }
          
    }
    catch (Exception e)
    {
       e.printStackTrace();
    }
  
  }  

  public JdbcMTSample()
  {
     super();
     // Assign an Id to the thread
     m_myId = getNextId();
  }

  public void run()
  {
    Connection conn = null;
    ResultSet     rs   = null;
    Statement  stmt = null;

    try
    {    
      // Get the connection

      if (share_connection)
        stmt = s_conn.createStatement (); // Create a Statement
      else
      {
        conn = DriverManager.getConnection("jdbc:oracle:oci8:@", 
                                           "scott","tiger");
        stmt = conn.createStatement (); // Create a Statement
      }

      while (!getGreenLight())
        yield();
          
      // Execute the Query
      rs = stmt.executeQuery ("select * from EMP");
          
      // Loop through the results
      while (rs.next())
      {
        System.out.println("Thread " + m_myId + 
                           " Employee Id : " + rs.getInt(1) + 
                           " Name : " + rs.getString(2));
        yield();  // Yield To other threads
      }
          
      // Close all the resources
      rs.close();
      rs = null;
  
      // Close the statement
      stmt.close();
      stmt = null;
  
      // Close the local connection
      if ((!share_connection) && (conn != null))
      {
         conn.close();
         conn = null;
      }
      System.out.println("Thread " + m_myId +  " is finished. ");
    }
    catch (Exception e)
    {
      System.out.println("Thread " + m_myId + " got Exception: " + e);
      e.printStackTrace();
      return;
    }
  }

  static boolean greenLight = false;
  static synchronized void setGreenLight () { greenLight = true; }
  synchronized boolean getGreenLight () { return greenLight; }

}

Samples for JDBC 2.0 Types

This section contains sample code for the Oracle implementations of standard JDBC 2.0 types:

• BLOBs and CLOBs--LobExample.java

• Weakly Typed Objects--PersonObject.java

• Weakly Typed Object References--StudentRef.java

• Weakly Typed Arrays--ArrayExample.java

These samples are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/object-samples

BLOBs and CLOBs--LobExample.java

This sample demonstrates basic JDBC support for LOBs. It illustrates how to create a table containing LOB columns and includes utility programs to read from a LOB, write to a LOB, and dump the LOB contents. For more information on LOBs, see "Working with BLOBs and CLOBs".

/* 
 * This sample demonstrate basic LOB support.
 */

import java.sql.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.driver.*;

//needed for new CLOB and BLOB classes
import oracle.sql.*;

public class LobExample
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection
URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott",
      "tiger");

    // Need to set auto commit off to update LOBs
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table basic_lob_table");
    }
    catch (SQLException e)
    {
      // An exception could be raised here if the table did not exist
         already.
    }

    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table basic_lob_table (x varchar2 (30), b
    blob, c clob)");
    
    // Populate the table
    stmt.execute ("insert into basic_lob_table values ('one',
    '010101010101010101010101010101', 'onetwothreefour')");
    stmt.execute ("insert into basic_lob_table values ('two',
    '0202020202020202020202020202', 'twothreefourfivesix')");

    // commit set up
    conn.commit();

    System.out.println ("Dumping lobs");

    // Select the lobs 
    // note that the FOR UPDATE clause is needed for updating LOBs  
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table
    for update");
    while (rset.next ())
    {
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);

      // Print the lob contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);

      // Change the lob contents
      fillClob (conn, clob, 2000);
      fillBlob (conn, blob, 4000);

    }
    // You could rollback the changes made by fillClob() and fillBlob()
    // by issuing a rollback here
    // conn.rollback();

    System.out.println ("Dumping lobs again");

    // No need to have FOR UPDATE clause just to do selects
    rset = stmt.executeQuery ("select * from basic_lob_table");
    while (rset.next ())
    {
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);

      // Print the lobs contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);
    }
    // Close all resources
    rset.close();
    stmt.close();
    conn.close(); 
  }

  // Utility function to dump Clob contents
  static void dumpClob (Connection conn, CLOB clob)
    throws Exception
  {
    // get character stream to retrieve clob data
    Reader instream = clob.getCharacterStream();

    // create temporary buffer for read
    char[] buffer = new char[10];

    // length of characters read
    int length = 0;

    // fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " chars: ");

      for (int i=0; i<length; i++)
        System.out.print(buffer[i]);
      System.out.println();
    }

    // Close input stream
    instream.close();
  }

  // Utility function to dump Blob contents
  static void dumpBlob (Connection conn, BLOB blob)
    throws Exception
  {
    // Get binary output stream to retrieve blob data
    InputStream instream = blob.getBinaryStream();

    // Create temporary buffer for read
    byte[] buffer = new byte[10];

    // length of bytes read
    int length = 0;

    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " bytes: ");

      for (int i=0; i<length; i++)
        System.out.print(buffer[i]+" ");
      System.out.println();
    }

    // Close input stream
    instream.close();
  }

  // Utility function to put data in a Clob
  static void fillClob (Connection conn, CLOB clob, long length)
    throws Exception
  {
    Writer outstream = clob.getCharacterOutputStream();

    int i = 0;
    int chunk = 10;

    while (i < length)
    {
      outstream.write(i + "hello world", 0, chunk);

      i += chunk;
      if (length - i < chunk)
      chunk = (int) length - i;
    }
    outstream.close();
  }

  // Utility function to put data in a Blob
  static void fillBlob (Connection conn, BLOB blob, long length)
    throws Exception
  {
    OutputStream outstream = blob.getBinaryOutputStream();

    int i = 0;
    int chunk = 10;

    byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

    while (i < length)
    {
      data [0] = (byte)i;
      outstream.write(data, 0, chunk);

      i += chunk;
      if (length - i < chunk)
      chunk = (int) length - i;
    }
    outstream.close();
  }
}

Weakly Typed Objects--PersonObject.java

This sample demonstrates the functionality of the Oracle classes oracle.sql.STRUCT and oracle.sql.StructDescriptor for weakly typed support of SQL structured objects. It defines the SQL object types PERSON and ADDRESS (an attribute of PERSON).

For a complete discussion of weakly typed STRUCT class functionality, see "Using the Default STRUCT Class for Oracle Objects".

/* 
 * This sample demonstrate basic Object support
 */

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.*;

public class PersonObject
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ sign in 
    // the connection URL.
    //
    // The sample retrieves an object of type "STUDENT",
    // materializes the object as an object of type ADT.
    // The Object is then modified and inserted back into the database.

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@",
                                   "scott", "tiger");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table people");
      stmt.execute ("drop type PERSON FORCE");
      stmt.execute ("drop type ADDRESS FORCE");
    }
    catch (SQLException e)
    {
      // the above drop and create statements will throw exceptions
      // if the types and tables did not exist before
    }

    stmt.execute ("create type ADDRESS as object 
                   (street VARCHAR (30), num NUMBER)");
    stmt.execute ("create type PERSON as object 
                   (name VARCHAR (30), home ADDRESS)");
    stmt.execute ("create table people (empno NUMBER, empid PERSON)");

    stmt.execute ("insert into people values 
                   (101, PERSON ('Greg', ADDRESS ('Van Ness', 345)))");
    stmt.execute ("insert into people values 
                   (102, PERSON ('John', ADDRESS ('Geary', 229)))");

    ResultSet rs = stmt.executeQuery ("select * from people");
    showResultSet (rs);
    rs.close();

    //now insert a new row

    // create a new STRUCT object with a new name and address
    // create the embedded object for the address
    Object [] address_attributes = new Object [2];
    address_attributes [0] = "Mission";
    address_attributes [1] = new BigDecimal (346);

    StructDescriptor addressDesc = 
      StructDescriptor.createDescriptor ("ADDRESS", conn);
    STRUCT address = new STRUCT (addressDesc, conn, address_attributes);

    Object [] person_attributes = new Object [2];
    person_attributes [0] = "Gary";
    person_attributes [1] = address;
    
    StructDescriptor personDesc = 
      StructDescriptor.createDescriptor("PERSON", conn);
    STRUCT new_person = new STRUCT (personDesc, conn, person_attributes);

    PreparedStatement ps = 
      conn.prepareStatement ("insert into people values (?,?)");
    ps.setInt (1, 102);
    ps.setObject (2, new_person);

    ps.execute ();
    ps.close();

    rs = stmt.executeQuery ("select * from people");
    System.out.println ();
    System.out.println (" a new row has been added to the people table");
    System.out.println ();
    showResultSet (rs);

    rs.close();
    stmt.close();
    conn.close();    
  }

  public static void showResultSet (ResultSet rs)
    throws SQLException
  {
    while (rs.next ())
    {
      int empno = rs.getInt (1);
      // retrieve the STRUCT 
      STRUCT person_struct = (STRUCT)rs.getObject (2);
      Object person_attrs[] = person_struct.getAttributes();

      System.out.println ("person name:  " + (String) person_attrs[0]);

      STRUCT address = (STRUCT) person_attrs[1];

      System.out.println ("person address: ");

      Object address_attrs[] = address.getAttributes();

      System.out.println ("street:  " + (String) address_attrs[0]);
      System.out.println ("number:  " + 
                         ((BigDecimal) address_attrs[1]).intValue());
      System.out.println ();
    }
  }
}

Weakly Typed Object References--StudentRef.java

This sample demonstrates the functionality of the Oracle class oracle.sql.REF for weakly typed support of SQL object references. It defines the SQL object type STUDENT and uses references to that object type.

For a complete discussion of weakly typed REF class functionality, see Chapter 9, "Working with Oracle Object References".

/* 
 * This sample demonstrate basic Ref support 
 */

import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.*;

public class StudentRef
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ sign in 
    // the connection URL.
    //
    // The sample retrieves an object of type "person",
    // materializes the object as an object of type ADT.
    // The Object is then modified and inserted back into the database.

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", 
                                   "scott", "tiger");
    
    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop table student_table");
      stmt.execute ("drop type STUDENT");   
    }
    catch (SQLException e)
    {      
      // the above drop and create statements will throw exceptions
      // if the types and tables did not exist before
    }

    stmt.execute ("create type STUDENT as object 
                   (name VARCHAR (30), age NUMBER)");
    stmt.execute ("create table student_table of STUDENT");
    stmt.execute ("insert into student_table values ('John', 20)");

    ResultSet rs = stmt.executeQuery ("select ref (s) from student_table s");
    rs.next ();

    // retrieve the ref object
    REF ref = (REF) rs.getObject (1);

    //retrieve the object value that the ref points to in the
    // object table

    STRUCT student = (STRUCT) ref.getValue ();
    Object attributes[] = student.getAttributes();

    System.out.println ("student name: " + (String) attributes[0]);
    System.out.println ("student age:  " + ((BigDecimal)
                         attributes[1]).intValue());

    rs.close();
    stmt.close();
    conn.close();
  }
}

Weakly Typed Arrays--ArrayExample.java

This sample program uses JDBC to create a table with a VARRAY. It inserts a new array object into the table, then prints the contents of the table. For more information on arrays, see Chapter 10, "Working with Oracle Collections".

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.oracore.Util;
import oracle.jdbc.*;
import java.math.BigDecimal;


public class ArrayExample
{
  public static void main (String args[])
    throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You need to put your database name after the @ sign in 
    // the connection URL.
    //
    // The sample retrieves an varray of type "NUM_VARRAY",
    // materializes the object as an object of type ARRAY.
    // A new ARRAY is then inserted into the database.

    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", 
                                   "scott", "tiger");
   
    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("DROP TABLE varray_table");
      stmt.execute ("DROP TYPE num_varray");     
    }
    catch (SQLException e)
    {
      // the above drop statements will throw exceptions
      // if the types and tables did not exist before. Just ingore it.
    }
 
    stmt.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)");
    stmt.execute ("CREATE TABLE varray_table (col1 num_varray)");
    stmt.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");

    ResultSet rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);

    //now insert a new row

    // create a new ARRAY object    
    int elements[] = { 300, 400, 500, 600 };
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", conn);
    ARRAY newArray = new ARRAY(desc, conn, elements);
    
    PreparedStatement ps = 
      conn.prepareStatement ("insert into varray_table values (?)");
    ((OraclePreparedStatement)ps).setARRAY (1, newArray);

    ps.execute ();

    rs = stmt.executeQuery("SELECT * FROM varray_table");
    showResultSet (rs);

    // Close all the resources
    rs.close();
    ps.close();
    stmt.close();
    conn.close();

  }   

  public static void showResultSet (ResultSet rs)
    throws SQLException
  {       
    int line = 0;
    while (rs.next())
    {
      line++;
      System.out.println("Row "+line+" : ");
      ARRAY array = ((OracleResultSet)rs).getARRAY (1);

      System.out.println ("Array is of type "+array.getSQLTypeName());
      System.out.println 
                 ("Array element is of typecode "+array.getBaseType()); 
      System.out.println ("Array is of length "+array.length());

      // get Array elements            
      BigDecimal[] values = (BigDecimal[]) array.getArray();

      for (int i=0; i<values.length; i++) 
      {
        BigDecimal value = (BigDecimal) values[i];
        System.out.println(">> index "+i+" = "+value.intValue());
      }
    }
  }
}

Samples for Oracle Type Extensions

This section contains sample code for some of the Oracle type extensions:

• REF CURSORs--RefCursorExample.java

• BFILEs--FileExample.java

The REF CURSOR sample is located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

The BFILE example is in the object-samples directory.

REF CURSORs--RefCursorExample.java

This sample program shows Oracle JDBC REF CURSOR functionality, creating a PL/SQL package that includes a stored function that returns a REF CURSOR type. The sample retrieves the REF CURSOR into a result set object. For information on REF CURSORs, see "Oracle REF CURSOR Type Category".

/*
 * This sample shows how to call a PL/SQL function that opens
 * a cursor and get the cursor back as a Java ResultSet.
 */

import java.sql.*;
import java.io.*;
import oracle.jdbc.*;

class RefCursorExample
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create the stored procedure
    init (conn);

    // Prepare a PL/SQL call
    CallableStatement call =
      conn.prepareCall ("{ ? = call java_refcursor.job_listing (?)}");

    // Find out all the SALES person
    call.registerOutParameter (1, OracleTypes.CURSOR);
    call.setString (2, "SALESMAN");
    call.execute ();
    ResultSet rset = (ResultSet)call.getObject (1);

    // Dump the cursor
    while (rset.next ())
      System.out.println (rset.getString ("ENAME"));

    // Close all the resources
    rset.close();
    call.close();
    conn.close();

  }

  // Utility function to create the stored procedure
  static void init (Connection conn)
       throws SQLException
  {
    Statement stmt = conn.createStatement ();

    stmt.execute ("create or replace package java_refcursor as " +
                  "  type myrctype is ref cursor return EMP%ROWTYPE; " +
                  "  function job_listing (j varchar2) return myrctype; " +
                  "end java_refcursor;");

    stmt.execute ("create or replace package body java_refcursor as " +
              "  function job_listing (j varchar2) return myrctype is " +
              "    rc myrctype; " +
              "  begin " +
              "    open rc for select * from emp where job = j; " +
              "    return rc; " +
              "  end; " +
              "end java_refcursor;");
    stmt.close();
  }
}

BFILEs--FileExample.java

This sample demonstrates Oracle JDBC BFILE support. It illustrates filling a table with BFILEs and includes a utility for dumping the contents of a BFILE. For information on BFILEs, see "Working with BFILEs".

/* 
 * This sample demonstrate basic File support
 */

import java.sql.*;
import java.io.*;
import java.util.*;

//including this import makes the code easier to read
import oracle.jdbc.*;

// needed for new BFILE class
import oracle.sql.*;

public class FileExample
{
  public static void main (String args [])
       throws Exception
  {
    // Register the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    //
    // The sample creates a DIRECTORY and you have to be connected as
    // "system" to be able to run the test.
    // I you can't connect as "system" have your system manager
    // create the directory for you, grant you the rights to it, and
    // remove the portion of this program that drops and creates the directory.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "system", "manager");

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("drop directory TEST_DIR");
    }
    catch (SQLException e)
    {
      // An error is raised if the directory does not exist.  Just ignore it.
    }
    stmt.execute ("create directory TEST_DIR as '/tmp/filetest'");

    try
    {
      stmt.execute ("drop table test_dir_table");
    }
    catch (SQLException e)
    {
      // An error is raised if the table does not exist.  Just ignore it.
    }

    // Create and populate a table with files
    // The files file1 and file2 must exist in the directory TEST_DIR created
    // above as symbolic name for /private/local/filetest.
    stmt.execute ("create table test_dir_table (x varchar2 (30), b bfile)");
    stmt.execute ("insert into test_dir_table values 
                   ('one', bfilename ('TEST_DIR', 'file1'))");
    stmt.execute ("insert into test_dir_table values 
                   ('two', bfilename ('TEST_DIR', 'file2'))");

    // Select the file from the table
    ResultSet rset = stmt.executeQuery ("select * from test_dir_table");
    while (rset.next ())
    {
      String x = rset.getString (1);
      BFILE bfile = ((OracleResultSet)rset).getBFILE (2);
      System.out.println (x + " " + bfile);

      // Dump the file contents
      dumpBfile (conn, bfile);
    }

    // Close all resources
    rset.close();
    stmt.close();
    conn.close();
  }

  // Utility function to dump the contents of a Bfile
  static void dumpBfile (Connection conn, BFILE bfile)
    throws Exception
  {
    System.out.println ("Dumping file " + bfile.getName());
    System.out.println ("File exists: " + bfile.fileExists());
    System.out.println ("File open: " + bfile.isFileOpen());

    System.out.println ("Opening File: ");

    bfile.openFile();

    System.out.println ("File open: " + bfile.isFileOpen());

    long length = bfile.length();
    System.out.println ("File length: " + length);

    int chunk = 10;

    InputStream instream = bfile.getBinaryStream();

    // Create temporary buffer for read
    byte[] buffer = new byte[chunk];

    // Fetch data  
    while ((length = instream.read(buffer)) != -1)
    {
      System.out.print("Read " + length + " bytes: ");

      for (int i=0; i<length; i++)
        System.out.print(buffer[i]+" ");
      System.out.println();
    }

    // Close input stream
    instream.close();
 
    // close file handler
    bfile.closeFile();
  }
}

Samples for Custom Object Classes

This section demonstrates the functionality of custom Java classes to map from SQL structured objects, providing examples of both a standard SQLData implementation and an Oracle ORAData implementation:

• SQLData Implementation--SQLDataExample.java

• ORAData Implementation--ORADataExample.java

This includes examples of the code you must provide to define custom Java classes for Oracle objects, and sample applications that make use of these custom Java class definitions. You create the custom classes by implementing either the standard java.sql.SQLData interface or the Oracle oracle.sql.CustomDatum interface. These interfaces provide a way to create and populate the custom Java class for the Oracle object and its attributes.

SQLData and CustomDatum both populate a Java object from a SQL object, with the SQLData interface providing more portability and the CustomDatum interface providing more utility and flexibility in how you present the data.

The SQLData interface is a JDBC standard. For more information on this interface, see "Understanding the SQLData Interface".

The CustomDatum interface is provided by Oracle. For more information on the CustomDatum interface, see "Understanding the ORAData Interface".

You can write your own code to create custom Java classes that implement either interface, but the Oracle JPublisher utility can generate classes to implement either interface as well.

For more information about JPublisher, see "Using JPublisher to Create Custom Object Classes" and the Oracle9i JPublisher User's Guide.

The sample applications and custom Java class definitions in this section are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/object-samples

SQLData Implementation--SQLDataExample.java

This section contains code that illustrates how to define and use a custom Java type corresponding to a given SQL object type, using a SQLData implementation.

SQL Object Definition

Following is the SQL definition of an EMPLOYEE object. The object has two attributes: a VARCHAR2 attribute EMPNAME (employee name) and an INTEGER attribute EMPNO (employee number).

 -- SQL definition 
CREATE TYPE employee AS OBJECT
(
     empname VARCHAR2(50),
     empno   INTEGER
);

Custom Object Class--SQLData Implementation

The following code defines the custom Java class EmployeeObj (defined in EmployeeObj.java) to correspond to the SQL type EMPLOYEE. Notice that the definition of EmployeeObj contains a string empName (employee name) attribute and an integer empNo (employee number) attribute. Also notice that the Java definition of the EmployeeObj custom Java class implements the SQLData interface and includes the implementations of a get method and the required readSQL() and writeSQL() methods.

import java.sql.*;
import oracle.jdbc.*;

public class EmployeeObj implements SQLData
{
  private String sql_type;

  public String empName;
  public int empNo;

  public EmployeeObj()
  {
  }

  public EmployeeObj (String sql_type, String empName, int empNo)
  {
    this.sql_type = sql_type;
    this.empName = empName;
    this.empNo = empNo;
  }  

  ////// implements SQLData //////
 
  public String getSQLTypeName() throws SQLException
  { 
    return sql_type; 
  } 
 
  public void readSQL(SQLInput stream, String typeName)
    throws SQLException
  {
    sql_type = typeName;
 
    empName = stream.readString();
    empNo = stream.readInt();
  }
 
  public void writeSQL(SQLOutput stream)
    throws SQLException
  { 
    stream.writeString(empName);
    stream.writeInt(empNo);
  }
}

Sample Application Using SQLData Custom Object Class

After you create the EmployeeObj Java class, you can use it in a program. The following program creates a table that stores employee name and number data. The program uses the EmployeeObj object to create a new employee object and insert it in the table. It then applies a SELECT statement to get the contents of the table and prints its contents.

For information about using SQLData implementations to access and manipulate SQL object data, see "Reading and Writing Data with a SQLData Implementation".

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.math.BigDecimal;
import java.util.Dictionary;


public class SQLDataExample
{

  public static void main(String args []) throws Exception
  {

    // Connect
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver ());
    OracleConnection conn = (OracleConnection)
      DriverManager.getConnection("jdbc:oracle:oci8:@",
                                  "scott", "tiger");

    Dictionary map = (Dictionary)conn.getTypeMap();
    map.put("EMPLOYEE", Class.forName("EmployeeObj"));

    // Create a Statement
    Statement stmt = conn.createStatement ();
    try 
    {
      stmt.execute ("drop table EMPLOYEE_TABLE");
      stmt.execute ("drop type EMPLOYEE");
    }
    catch (SQLException e) 
    {      
      // An error is raised if the table/type does not exist. Just ignore it.
    }

    // Create and populate tables
    stmt.execute ("CREATE TYPE EMPLOYEE AS OBJECT
                   (EmpName VARCHAR2(50),EmpNo INTEGER)"); 
    stmt.execute ("CREATE TABLE EMPLOYEE_TABLE (ATTR1 EMPLOYEE)");
    stmt.execute ("INSERT INTO EMPLOYEE_TABLE VALUES 
                   (EMPLOYEE('Susan Smith', 123))");
    stmt.close();

    // Create a SQLData object
    EmployeeObj e = new EmployeeObj("SCOTT.EMPLOYEE", "George Jones", 456);

    // Insert the SQLData object
    PreparedStatement pstmt
      = conn.prepareStatement ("insert into employee_table values (?)");

    pstmt.setObject(1, e, OracleTypes.STRUCT);
    pstmt.executeQuery();
    System.out.println("insert done");
    pstmt.close();

    // Select now
    Statement s = conn.createStatement();
    OracleResultSet rs = (OracleResultSet) 
      s.executeQuery("select * from employee_table");

    while(rs.next())
    {
       EmployeeObj ee = (EmployeeObj) rs.getObject(1);
       System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo);
    }
    rs.close();
    s.close();

    if (conn != null)
    {
      conn.close();
    }
  }
}

ORAData Implementation--ORADataExample.java

This section contains code that illustrates how to define and use a custom Java type corresponding to a given SQL object type, using a ORAData implementation.

SQL Object Definition

Following is the SQL definition of an EMPLOYEE object. The object has two attributes: a VARCHAR2 attribute EMPNAME (employee name) and an INTEGER attribute EMPNO (employee number).

CREATE TYPE employee AS OBJECT 
( 
     empname VARCHAR2(50), 
     empno   INTEGER 
); 

Custom Object Class--CustomDatum Implementation

The following code defines the custom Java class Employee (defined in Employee.java) to correspond to the SQL type EMPLOYEE. Notice that the definition of Employee contains accessor methods for a string empname (employee name) and an integer empno (employee number). Also notice that the Java definition of the Employee custom Java class implements the ORAData and ORADataFactory interfaces. A custom Java class that implements ORAData has a static getFactory() method that returns a ORADataFactory object. The JDBC driver uses the ORADataFactory object's create() method to return a CustomDatum instance.

Note that instead of writing the custom Java class yourself, you can use the JPublisher utility to generate class definitions that implement the ORAData and ORADataFactory interfaces. In fact, the Employee.java code shown here was generated by JPublisher.

import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.sql.CustomDatum;
import oracle.sql.CustomDatumFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.jpub.runtime.MutableStruct;

public class Employee implements CustomDatum, CustomDatumFactory
{
  public static final String _SQL_NAME = "SCOTT.EMPLOYEE";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  MutableStruct _struct;

  static int[] _sqlType =
  {
    12, 4
  };

  static CustomDatumFactory[] _factory = new CustomDatumFactory[2];

  static final Employee _EmployeeFactory = new Employee();
  public static CustomDatumFactory getFactory()
  {
    return _EmployeeFactory;
  }

  /* constructor */      
  public Employee()
  {
    _struct = new MutableStruct(new Object[2], _sqlType, _factory);
  }

  /* CustomDatum interface */ 
  public Datum toDatum(OracleConnection c) throws SQLException
  {
    return _struct.toDatum(c, _SQL_NAME);
  }

  /* CustomDatumFactory interface */ 
  public CustomDatum create(Datum d, int sqlType) throws SQLException
  {
    if (d == null) return null;
    Employee o = new Employee();
    o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
    return o;
  }

  /* accessor methods */
  public String getEmpname() throws SQLException
  { return (String) _struct.getAttribute(0); }

  public void setEmpname(String empname) throws SQLException
  { _struct.setAttribute(0, empname); }

  public Integer getEmpno() throws SQLException
  { return (Integer) _struct.getAttribute(1); }

  public void setEmpno(Integer empno) throws SQLException
  { _struct.setAttribute(1, empno); } 
}

Sample Application Using CustomDatum Custom Object Class

This sample program shows how you can use the Employee class generated by JPublisher. The sample code creates a new Employee object, fills it with data, then inserts it into the database. The sample code then retrieves the Employee data from the database.

For information about using CustomDatum implementations to access and manipulate SQL object data, see "Reading and Writing Data with a ORAData Implementation".

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;
import java.math.BigDecimal;


public class CustomDatumExample
{

  public static void main(String args []) throws Exception
  {

    // Connect
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver ());
    OracleConnection conn = (OracleConnection)
      DriverManager.getConnection("jdbc:oracle:oci8:@",
                                  "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement ();
    try 
    {
      stmt.execute ("drop table EMPLOYEE_TABLE");
      stmt.execute ("drop type EMPLOYEE");
    }
    catch (SQLException e) 
    {      
      // An error is raised if the table/type does not exist. Just ignore it.
    }

    // Create and populate tables
    stmt.execute ("CREATE TYPE EMPLOYEE AS OBJECT
                   (EmpName VARCHAR2(50),EmpNo INTEGER)"); 
    stmt.execute ("CREATE TABLE EMPLOYEE_TABLE (ATTR1 EMPLOYEE)");
    stmt.execute ("INSERT INTO EMPLOYEE_TABLE VALUES 
                   (EMPLOYEE('Susan Smith', 123))");
    stmt.close();

    // Create a CustomDatum object
    Employee e = new Employee("George Jones", new BigDecimal("456"));

    // Insert the CustomDatum object
    PreparedStatement pstmt
      = conn.prepareStatement ("insert into employee_table values (?)");

    pstmt.setObject(1, e, OracleTypes.STRUCT);
    pstmt.executeQuery();
    System.out.println("insert done");
    pstmt.close();

    // Select now
    Statement s = conn.createStatement();
    OracleResultSet rs = (OracleResultSet) 
      s.executeQuery("select * from employee_table");

    while(rs.next())
    {
       Employee ee = (Employee) rs.getCustomDatum(1, Employee.getFactory());
       System.out.println("EmpName: " + ee.empName + " EmpNo: " + ee.empNo);
    }
    rs.close();
    s.close();

    if (conn != null)
    {
      conn.close();
    }
  }
}

JDBC 2.0 Result Set Enhancement Samples

This section provides samples that demonstrate the functionality of result set enhancements available with JDBC 2.0. This includes positioning in a scrollable result set, updating a result set, using a scroll-sensitive result set that can automatically see external updates, and explicitly refetching data into a result set:

• Positioning in a Result Set--ResultSet2.java

• Inserting and Deleting Rows in a Result Set--ResultSet3.java

• Updating Rows in a Result Set--ResultSet4.java

• Scroll-Sensitive Result Set--ResultSet5.java

• Refetching Rows in a Result Set--ResultSet6.java

The sample applications in this section are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/jdbc20-samples

Positioning in a Result Set--ResultSet2.java

This section demonstrates scrollable result set functionality--moving to relative and absolute row positions and iterating backwards through the result set.

For discussion on these topics, see "Positioning and Processing in Scrollable Result Sets".

/**
 * A simple sample to demonstrate previous(), absolute() and relative().
 */

import java.sql.*;

public class ResultSet2
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // iterate through the result using next()
    show_resultset_by_next(rset);

    // iterate through the result using previous()
    show_resultset_by_previous(rset);

    // iterate through the result using absolute()
    show_resultset_by_absolute(rset);

    // iterate through the result using relative()
    show_resultset_by_relative(rset);

    // Close the ResultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();   
  }

  /**
   * Iterate through the result using next().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_next(ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.next():");

    // Make sure the cursor is placed right before the first row
    if (!rset.isBeforeFirst())
    {
      // Place the cursor right before the first row
      rset.beforeFirst ();
    }
 
    // Iterate through the rows using next()
    while (rset.next())
      System.out.println (rset.getString (1));

    System.out.println ();
  }

  /**
   * Iterate through the result using previous().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_previous(ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.previous():");

    // Make sure the cursor is placed after the last row
    if (!rset.isAfterLast())
    {
      // Place the cursor after the last row
      rset.afterLast ();
    }
 
    // Iterate through the rows using previous()
    while (rset.previous())
      System.out.println (rset.getString (1));

    System.out.println ();
  }

  /**
   * Iterate through the result using absolute().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_absolute (ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.absolute():");

    // The begin index for ResultSet.absolute (idx)
    int idx = 1;

    // Loop through the result set until absolute() returns false.
    while (rset.absolute(idx))
    {
      System.out.println (rset.getString (1));
      idx ++;
    }
    System.out.println (); 
  }
     
  /**
   * Iterate through the result using relative().
   *
   * @param rset a result set object
   */ 
  public static void show_resultset_by_relative (ResultSet rset) 
    throws SQLException
  {
    System.out.println ("List the employee names using ResultSet.relative():");

    // getRow() returns 0 if there is no current row
    if (rset.getRow () == 0 || !rset.isLast())
    {
      // place the cursor on the last row
      rset.last ();
    }

    // Calling relative(-1) is similar to previous(), but the cursor 
    // has to be on a valid row before calling relative().
    do
    {
      System.out.println (rset.getString (1));
    }
    while (rset.relative (-1));

    System.out.println (); 
  }
}

Inserting and Deleting Rows in a Result Set--ResultSet3.java

This sample shows some of the functionality of an updatable result set--inserting and deleting rows that will in turn be inserted into or deleted from the database.

For discussion on these topics, see "Performing an INSERT Operation in a Result Set" and "Performing a DELETE Operation in a Result Set".

/**
 * A simple sample to to demonstrate ResultSet.insertRow() and 
 * ResultSet.deleteRow().
 */

import java.sql.*;

public class ResultSet3
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Cleanup
    cleanup (conn);

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME from EMP");

    // Add three new employees using ResultSet.insertRow()
    addEmployee (rset, 1001, "PETER");
    addEmployee (rset, 1002, "MARY");
    addEmployee (rset, 1003, "DAVID");

    // Close the result set
    rset.close ();

    // Verify the insertion
    System.out.println ("\nList EMPNO and ENAME in the EMP table: ");

    rset = stmt.executeQuery ("select EMPNO, ENAME from EMP");
    while (rset.next())
    {
      // We expect to see the three new employees
      System.out.println (rset.getInt(1)+" "+rset.getString(2));
    }
    System.out.println ();

    // Delete the new employee 'PETER' using ResultSet.deleteRow()
    removeEmployee (rset, 1001);
    rset.close ();

    // Verify the deletion
    System.out.println ("\nList EMPNO and ENAME in the EMP table: ");
    rset = stmt.executeQuery ("select EMPNO, ENAME from EMP");
    while (rset.next())
    {
      // We expect "PETER" is removed
      System.out.println (rset.getInt(1)+" "+rset.getString(2));
    }
    System.out.println ();

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Add a new employee to EMP table.
   */
  public static void addEmployee (ResultSet rset, 
                                  int employeeId,
                                  String employeeName) 
    throws SQLException
  {
    System.out.println ("Adding new employee: "+employeeId+" "+employeeName);

    // Place the cursor on the insert row
    rset.moveToInsertRow();

    // Assign the new values
    rset.updateInt (1, employeeId);
    rset.updateString (2, employeeName);

    // Insert the new row to database
    rset.insertRow();
  }

  /**
   * Remove the employee from EMP table.
   */
  public static void removeEmployee (ResultSet rset, 
                                     int employeeId)
    throws SQLException
  {
    System.out.println ("Removing the employee: id="+employeeId);

    // Place the cursor right before the first row if it doesn't
    if (!rset.isBeforeFirst())
    {
      rset.beforeFirst();
    }

    // Iterate the result set
    while (rset.next())
    {
      // Place the cursor the row with matched employee id
      if (rset.getInt(1) == employeeId)
      {
        // Delete the current row
        rset.deleteRow();
        break;
      }
    }
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) 
    throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute 
         ("DELETE FROM EMP WHERE EMPNO=1001 OR EMPNO=1002 OR EMPNO=1003");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Updating Rows in a Result Set--ResultSet4.java

This sample shows some of the functionality of an updatable result set--updating rows that will in turn be updated in the database.

For a discussion on this topic, see "Performing an UPDATE Operation in a Result Set".

/**
 * A simple sample to demonstrate ResultSet.udpateRow().
 */

import java.sql.*;

public class ResultSet4
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");

    // Give everybody a $500 raise
    adjustSalary (rset, 500);

    // Verify the sarlary changes
    System.out.println ("Verify the changes with a new query: ");
    rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
    while (rset.next())
    {
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }
    System.out.println ();

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Update the ResultSet content using updateRow().
   */
  public static void adjustSalary (ResultSet rset, int raise) 
    throws SQLException
  {
    System.out.println ("Give everybody in the EMP table a $500 raise\n");

    int salary = 0;

    while (rset.next ())
    {
      // save the old value
      salary = rset.getInt (3);

      // update the row 
      rset.updateInt (3, salary + raise);
      
      // flush the changes to database
      rset.updateRow ();

      // show the changes
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          salary+" -> "+rset.getInt(3));
    }
    System.out.println ();
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Scroll-Sensitive Result Set--ResultSet5.java

This sample shows the functionality of a scroll-sensitive result. Such a result set can implicitly see updates to the database that were made externally.

For more information about scroll-sensitive result sets and how they are implemented, see "Oracle Implementation of Scroll-Sensitive Result Sets".

/**
 * A simple sample to demonstrate scroll sensitive result set.
 */

import java.sql.*;

public class ResultSet5
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, 
                                         ResultSet.CONCUR_UPDATABLE);

    // Set the statement fetch size to 1
    stmt.setFetchSize (1);

    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
 
    // List the result set's type, concurrency type, ..., etc
    showProperty (rset);
 
    // List the query result 
    System.out.println ("List ENO, ENAME and SAL from the EMP table: ");
    while (rset.next())
    {
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }
    System.out.println ();

    // Do some changes outside the result set
    doSomeChanges (conn);
    
    // Place the cursor right before the first row
    rset.beforeFirst ();

    // List the employee information again
    System.out.println ("List ENO, ENAME and SAL again: ");
    while (rset.next())
    {
      // We expect to see the changes made in "doSomeChanges()"
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Update the EMP table.
   */ 
  public static void doSomeChanges (Connection conn)
    throws SQLException
  {
    System.out.println ("Update the employee salary outside the result set\n");
   
    Statement otherStmt = conn.createStatement ();
    otherStmt.execute ("update emp set sal = sal + 500");
    otherStmt.execute ("commit");
    otherStmt.close ();
  }

  /**
   * Show the result set properties like type, concurrency type, fetch 
   * size,..., etc.
   */
  public static void showProperty (ResultSet rset) throws SQLException
  {
    // Verify the result set type
    switch (rset.getType())
    {
      case ResultSet.TYPE_FORWARD_ONLY:
        System.out.println ("Result set type: TYPE_FORWARD_ONLY");
        break;
      case ResultSet.TYPE_SCROLL_INSENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_INSENSITIVE");
        break;
      case ResultSet.TYPE_SCROLL_SENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_SENSITIVE");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the result set concurrency
    switch (rset.getConcurrency())
    {
      case ResultSet.CONCUR_UPDATABLE:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_UPDATABLE");
        break;
      case ResultSet.CONCUR_READ_ONLY:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_READ_ONLY");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the fetch size
    System.out.println ("fetch size: "+rset.getFetchSize ());

    System.out.println ();
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Refetching Rows in a Result Set--ResultSet6.java

This sample shows how to explicitly refetch data from the database to update the result set. This functionality is available in scroll-sensitive and scroll-insensitive/updatable result sets.

For more information, see "Refetching Rows".

/**
 * A simple sample to demonstrate ResultSet.refreshRow().
 */

import java.sql.*;

public class ResultSet6
{
  public static void main(String[] args) throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Create a Statement
    Statement stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                        ResultSet.CONCUR_UPDATABLE);

    // Set the statement fetch size to 1
    stmt.setFetchSize (1);
      
    // Query the EMP table
    ResultSet rset = stmt.executeQuery ("select EMPNO, ENAME, SAL from EMP");
 
    // List the result set's type, concurrency type, ..., etc
    showProperty (rset);

    // List the query result 
    System.out.println ("List ENO, ENAME and SAL from the EMP table: ");
    while (rset.next())
    {
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          rset.getInt(3));
    }
    System.out.println ();

    // Do some changes outside the result set
    doSomeChanges (conn);

    // Place the cursor right before the first row
    rset.beforeFirst ();

    // List the employee information again
    System.out.println ("List ENO, ENAME and SAL again: ");
    int salary = 0;
    while (rset.next())
    {
      // save the original salary
      salary = rset.getInt (3);

      // refresh the row
      rset.refreshRow ();
      
      // We expect to see the changes made in "doSomeChanges()"
      System.out.println (rset.getInt(1)+" "+rset.getString(2)+" "+
                          salary+" -> "+rset.getInt(3));
    }

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Cleanup
    cleanup(conn);

    // Close the connection
    conn.close();   
  }

  /**
   * Update the EMP table.
   */ 
  public static void doSomeChanges (Connection conn)
    throws SQLException
  {
    System.out.println ("Update the employee salary outside the result set\n");
   
    Statement otherStmt = conn.createStatement ();
    otherStmt.execute ("update emp set sal = sal + 500");
    otherStmt.execute ("commit");
    otherStmt.close ();
  }

  /**
   * Show the result set properties like type, concurrency type, fetch 
   * size,..., etc.
   */
  public static void showProperty (ResultSet rset) throws SQLException
  {
    // Verify the result set type
    switch (rset.getType())
    {
      case ResultSet.TYPE_FORWARD_ONLY:
        System.out.println ("Result set type: TYPE_FORWARD_ONLY");
        break;
      case ResultSet.TYPE_SCROLL_INSENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_INSENSITIVE");
        break;
      case ResultSet.TYPE_SCROLL_SENSITIVE:
        System.out.println ("Result set type: TYPE_SCROLL_SENSITIVE");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the result set concurrency
    switch (rset.getConcurrency())
    {
      case ResultSet.CONCUR_UPDATABLE:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_UPDATABLE");
        break;
      case ResultSet.CONCUR_READ_ONLY:
        System.out.println 
                   ("Result set concurrency: ResultSet.CONCUR_READ_ONLY");
        break;
      default: 
        System.out.println ("Invalid type");
        break;
    }

    // Verify the fetch size
    System.out.println ("fetch size: "+rset.getFetchSize ());

    System.out.println ();
  }

  /**
   * Generic cleanup.
   */
  public static void cleanup (Connection conn) throws SQLException
  {
    Statement stmt = conn.createStatement ();
    stmt.execute ("UPDATE EMP SET SAL = SAL - 500");
    stmt.execute ("COMMIT");
    stmt.close ();
  }
}

Performance Enhancement Samples

This section provides sample applications for performance enhancement features such as update batching:

• Standard Update Batching--BatchUpdates.java

• Oracle Update Batching with Implicit Execution--SetExecuteBatch.java

• Oracle Update Batching with Explicit Execution--SendBatch.java

• Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java

• Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java

• Oracle Column Type Definitions--DefineColumnType.java

The sample applications for Oracle-specific performance enhancements are located in the following directory on the product CD:

[Oracle Home]/jdbc/demo/samples/oci8/basic-samples

The standard update batching sample is located in the jdbc20-samples directory.

Standard Update Batching--BatchUpdates.java

This sample shows how to use standard update batching as specified by JDBC 2.0. For more information, see "Standard Update Batching".

For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".

/**
 * A simple sample to demonstrate standard JDBC 2.0 update batching.
 */

import java.sql.*;

public class BatchUpdates
{
  public static void main(String[] args)
  {
    Connection          conn = null;
    Statement           stmt = null;
    PreparedStatement   pstmt = null;
    ResultSet           rset = null;
    int                 i = 0;

    try
    {
      DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

      conn = DriverManager.getConnection(
               "jdbc:oracle:oci8:@", "scott", "tiger");

      stmt = conn.createStatement();
      try { stmt.execute(
            "create table mytest_table (col1 number, col2 varchar2(20))");
      } catch (Exception e1) {}

      //
      // Insert in a batch.
      //
      pstmt = conn.prepareStatement("insert into mytest_table values (?, ?)");

      pstmt.setInt(1, 1);
      pstmt.setString(2, "row 1");
      pstmt.addBatch();

      pstmt.setInt(1, 2);
      pstmt.setString(2, "row 2");
      pstmt.addBatch();

      pstmt.executeBatch();

      //
      // Select and print results.
      //
      rset = stmt.executeQuery("select * from mytest_table");
      while (rset.next())
      {
        System.out.println(rset.getInt(1) + ", " + rset.getString(2));
      }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    finally
    {
      if (stmt != null)
      {
        try { stmt.execute("drop table mytest_table"); } catch (Exception e) {}
        try { stmt.close(); } catch (Exception e) {}
      }
      if (pstmt != null)
      {
        try { pstmt.close(); } catch (Exception e) {}
      }
      if (conn != null)
      {
        try { conn.close(); } catch (Exception e) {}
      }
    }
  }
}

Oracle Update Batching with Implicit Execution--SetExecuteBatch.java

This sample shows how to use Oracle update batching, with the batch being executed implicitly when the batch value (the number of statements to collect before sending them to the database) is reached.

For information about Oracle update batching, see "Oracle Update Batching".

For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".

/*
 * This sample shows how to use the batching extensions.
 * In this example, we set the defaultBatch value from the
 * connection object. This affects all statements created from
 * this connection. 
 * It is possible to set the batch value individually for each 
 * statement. The API to use on the statement object is setExecuteBatch().
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.* in order to use the
// API extensions.
import oracle.jdbc.*;

class SetExecuteBatch
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // Default batch value set to 2 for all prepared statements belonging
    // to this connection.
    ((OracleConnection)conn).setDefaultExecuteBatch (2);

    PreparedStatement ps =
      conn.prepareStatement ("insert into dept values (?, ?, ?)");
    
    ps.setInt (1, 12);
    ps.setString (2, "Oracle");
    ps.setString (3, "USA");

    // No data is sent to the database by this call to executeUpdate
    System.out.println ("Number of rows updated so far: "
                        + ps.executeUpdate ());     

    ps.setInt (1, 11);
    ps.setString (2, "Applications");
    ps.setString (3, "Indonesia");

    // The number of batch calls to executeUpdate is now equal to the
    // batch value of 2.  The data is now sent to the database and
    // both rows are inserted in a single roundtrip.
    int rows = ps.executeUpdate ();
    System.out.println ("Number of rows updated now: " + rows);      
  
    ps.close ();
    conn.close();
  }
}

Oracle Update Batching with Explicit Execution--SendBatch.java

This sample shows how to use Oracle update batching, with the batch being executed explicitly with a sendBatch() call.

For information about Oracle update batching, see "Oracle Update Batching".

For comparison and contrast between the standard and Oracle-specific update batching models, see "Overview of Update Batching Models".

/*
 * This sample shows how to use the batching extensions.
 * In this example, we demonstrate the use of the "sendBatch" API.
 * This allows the user to actually execute a set of batched
 * execute commands.
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.* in order to use the
// API extensions.
import oracle.jdbc.*;

class SendBatch
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    Statement stmt = conn.createStatement ();

    // Default batch value set to 50 for all prepared statements belonging
    // to this connection.
    ((OracleConnection)conn).setDefaultExecuteBatch (50);

    PreparedStatement ps =
      conn.prepareStatement ("insert into dept values (?, ?, ?)");
    
    ps.setInt (1, 32);
    ps.setString (2, "Oracle");
    ps.setString (3, "USA");

    // this execute does not actually happen at this point
    System.out.println (ps.executeUpdate ());     

    ps.setInt (1, 33);
    ps.setString (2, "Applications");
    ps.setString (3, "Indonesia");

    // this execute does not actually happen at this point
    int rows = ps.executeUpdate ();  
    
    System.out.println ("Number of rows updated before calling sendBatch: "
                                       + rows);

    // Execution of both previously batched executes will happen
    // at this point. The number of rows updated will be
    // returned by sendBatch.
    rows = ((OraclePreparedStatement)ps).sendBatch ();

    System.out.println ("Number of rows updated by calling sendBatch: "
                                          + rows);
  
    ps.close ();
    conn.close ();
  }
}

Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java

This section demonstrates how to use Oracle row prefetching-functionality, setting the row prefetch value in the connection object and thereby affecting every statement produced from that connection.

Note that Oracle row prefetching is fundamentally similar to JDBC 2.0 fetch size functionality.

For information about Oracle row prefetching, see "Oracle Row Prefetching". For information about JDBC 2.0 fetch size and some comparison with row prefetching, see "Fetch Size".

/*
 * This sample shows how to use the Oracle performance extensions
 * for row-prefetching. This allows the driver to fetch multiple
 * rows in one round-trip, saving unecessary round-trips to the database.
 *
 * This example shows how to set the rowPrefetch for the connection object,
 * which will be used for all statements created from this connection.
 * Please see RowPrefetch_statement.java for examples of how to set
 * the rowPrefetch for statements individually.
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oacle.jdbc.driver in order to use the oracle extensions.
import oracle.jdbc.*;

class RowPrefetch_connection
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // set the RowPrefetch value from the Connection object
    // This sets the rowPrefetch for *all* statements belonging
    // to this connection.
    // The rowPrefetch value can be overriden for specific statements by
    // using the setRowPrefetch API on the statement object. Please look
    // at RowPrefetch_statement.java for an example.

    
    // Please note that any statements created *before* the connection
    // rowPrefetch was set, will use the default rowPrefetch.

    ((OracleConnection)conn).setDefaultRowPrefetch (30);
    
    Statement stmt = conn.createStatement ();
    
    // Check to verify statement rowPrefetch value is 30.
    int row_prefetch = ((OracleStatement)stmt).getRowPrefetch ();
    System.out.println ("The RowPrefetch for the statement is:  "
                                         + row_prefetch + "\n");

    ResultSet rset = stmt.executeQuery ("select ename from emp");
    
    while(rset.next ())
    {
      System.out.println (rset.getString (1));
    }
    rset.close ();
    stmt.close ();
    conn.close (); 
  }
}

Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java

This section demonstrates how to use Oracle row prefetching functionality, setting the row prefetch value in a particular statement object to override the value in the connection object producing the statement.

Note that Oracle row prefetching is fundamentally similar to JDBC 2.0 fetch size functionality.

For information about Oracle row prefetching, see "Oracle Row Prefetching". For information about JDBC 2.0 fetch size and some comparison with row prefetching, see "Fetch Size".

/*
 * This sample shows how to use the Oracle performance extensions
 * for row-prefetching. This allows the driver to fetch multiple
 * rows in one round-trip, saving unecessary round-trips to the database.
 *
 * This example shows how to set the rowPrefetch for individual
 * statements.
 *
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc in order to use the
// Oracle extensions
import oracle.jdbc.*;

class RowPrefetch_statement
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    // get the value of the default row prefetch from the connection object

    int default_row_prefetch =
      ((OracleConnection)conn).getDefaultRowPrefetch ();
    System.out.println ("The Default RowPrefetch for the connection is: "
                                        + default_row_prefetch);

    Statement stmt = conn.createStatement ();

    // set the RowPrefetch value from the statement object
    // This sets the rowPrefetch only for this particular statement.
    // All other statements will use the default RowPrefetch from the
    // connection.

    ((OracleStatement)stmt).setRowPrefetch (30);
    
    // Check to verify statement rowPrefetch value is 30.
    int row_prefetch = ((OracleStatement)stmt).getRowPrefetch ();
    System.out.println ("The RowPrefetch for the statement is: "
                                         + row_prefetch + "\n");

    ResultSet rset = stmt.executeQuery ("select ename from emp");
    
    while(rset.next ())
    {
      System.out.println (rset.getString (1));
    }
    rset.close ();
    stmt.close ();
    stmt.close ();
  }
}

Oracle Column Type Definitions--DefineColumnType.java

This sample shows how to use Oracle extensions to predefine result set column types to reduce round trips to the database for a query.

For information about column type definitions, see "Defining Column Types".

/*
 * This sample shows how to use the "define" extensions.
 * The define extensions allow the user to specify the types
 * under which to retrieve column data in a query. 
 *
 * This saves round-trips to the database (otherwise necessary to
 * gather information regarding the types in the select-list) and
 * conversions from native types to the types under which the user
 * will get the data.
 *
 * This can also be used to avoid streaming of long columns, by defining
 * them as CHAR or VARCHAR types.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;

// You need to import oracle.jdbc.* in order to use the
// API extensions.
import oracle.jdbc.*;

class DefineColumnType
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver( new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    Statement stmt = conn.createStatement ();

    // Call DefineColumnType to specify that the column will be
    // retrieved as a String to avoid conversion from NUMBER to String
    // on the client side.  This also avoids a round-trip to the
    // database to get the column type.
    //
    // There are 2 defineColumnType API.  We use the one with 3 arguments.
    // The 3rd argument allows us to specify the maximum length
    // of the String.  The values obtained for this column will
    // not exceed this length.

    ((OracleStatement)stmt).defineColumnType (1, Types.VARCHAR, 7);

    ResultSet rset = stmt.executeQuery ("select empno from emp");
    while (rset.next ())
    {
      System.out.println (rset.getString (1));
    }

    // Close the resultSet
    rset.close();

    // Close the statement
    stmt.close ();
 
    // Close the connection
    conn.close();
  }
}

Implicit Statement Caching--StmtCache1.java

This sample application uses implicit statement caching to create a result set from a database and then print out various information, including employee names.

For more information on implicit statement caching, see "Using Implicit Statement Caching" and the other pertinent sections in Chapter 13, "Statement Caching".

/*
 * This sample to demonstrate Implicit Statement Caching. This can be
 * enabled by calling setStmtCacheSize on the Connection Object.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import oracle.jdbc.*;

class StmtCache1
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    ((OracleConnection)conn).setStmtCacheSize(1);
    
    Connection sysconn = DriverManager.getConnection("jdbc:oracle:oci8:@", 
                                                     "system", "manager");
    String sql = "select ENAME from EMP";

    System.out.println("Beging of 1st execution");
    getOpenCursors (sysconn);

    // Create a Statement
    PreparedStatement stmt = conn.prepareStatement (sql);
    System.out.println("1. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    System.out.println("End of 1st execution");
    getOpenCursors (sysconn);

    System.out.println("Reexecuting the same SQL");

    stmt = conn.prepareStatement (sql);

    System.out.println("2. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();
  
    System.out.println("End of 2nd execution");
    getOpenCursors (sysconn);

    // Close the connection
    conn.close();   

    System.out.println("After close of connection");
    getOpenCursors (sysconn);

    sysconn.close();
  }

  private static void getOpenCursors (Connection conn)
     throws SQLException
  {
     System.out.println("Open Cusrors are : ");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery ("select SQL_TEXT from V$OPEN_CURSOR");
     while (rs.next())
       System.out.println("Cursor's sql text is " + rs.getString(1));
     rs.close();
     rs = null;
     stmt.close();
     stmt = null;
  }
}

Explicit Statement Caching--StmtCache2.java

This sample application uses explicit statement caching to create a result set from a database and then print out various information including employee names.

For more information on implicit statement caching, see "Using Explicit Statement Caching" and the other pertinent sections in Chapter 13, "Statement Caching".

/*
 * This sample to demonstrate Explicit Statement Caching. This can be
 * enabled by calling Oracle Specific calls like closeWithKey, 
 * prepareStatementWithKey etc.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import oracle.jdbc.*;

class StmtCache2
{
  public static void main (String args [])
       throws SQLException
  {
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

    ((OracleConnection)conn).setStmtCacheSize(1);
    
    Connection sysconn = DriverManager.getConnection("jdbc:oracle:oci8:@", 
                                                     "system", "manager");
    String sql = "select ENAME from EMP";

    System.out.println("Beging of 1st execution");
    getOpenCursors (sysconn);

    // Create a Statement
    PreparedStatement stmt = conn.prepareStatement (sql);
    System.out.println("1. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    //stmt.close();
    ((OracleStatement)stmt).closeWithKey ("mysql");

    System.out.println("End of 1st execution");
    getOpenCursors (sysconn);

    System.out.println("Reexecuting the same SQL");

    stmt = ((OracleConnection)conn).prepareStatementWithKey ("mysql");

    System.out.println("2. Stmt is " + stmt);

    // Select the ENAME column from the EMP table
    rset = stmt.executeQuery ();

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();
  
    System.out.println("End of 2nd execution");
    getOpenCursors (sysconn);

    // Close the connection
    conn.close();   

    System.out.println("After close of connection");
    getOpenCursors (sysconn);

    sysconn.close();
  }

  private static void getOpenCursors (Connection conn)
     throws SQLException
  {
     System.out.println("Open Cusrors are : ");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery ("select SQL_TEXT from V$OPEN_CURSOR");
     while (rs.next())
       System.out.println("Cursor's sql text is " + rs.getString(1));
     rs.close();
     rs = null;
     stmt.close();
     stmt = null;
  }
}

Samples for Connection Pooling and Distributed Transactions

This section includes samples of JDBC 2.0 extension features for data sources, connection pooling, connection caching, and distributed transactions (XA), as follows:

• Data Source without JNDI--DataSource.java

• Data Source with JNDI--DataSourceJNDI.java

• Pooled Connection--PooledConnection.java

• Oracle Connection Cache (dynamic)--CCache1.java

• Oracle Connection Cache ("fixed with no wait")--CCache2.java

• XA with Suspend and Resume--XA2.java

• XA with Two-Phase Commit Operation--XA4.java

Data Source without JNDI--DataSource.java

This example shows how to use JDBC 2.0 data sources without JNDI. For general information about data sources, including how to use them with or without JNDI, see "Data Sources".

/**
 * A Simple DataSource sample without using JNDI.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

public class DataSource
{
  public static void main (String args [])
    throws SQLException
  {
    // Create a OracleDataSource instance explicitly
    OracleDataSource ods = new OracleDataSource();

    // Set the user name, password, driver type and network protocol
    ods.setUser("scott");
    ods.setPassword("tiger");
    ods.setDriverType("oci8");
    ods.setNetworkProtocol("ipc");

    // Retrieve a connection
    Connection conn = ods.getConnection();
    getUserName(conn);
    // Close the connection
    conn.close();
    conn = null;
  }

  static void getUserName(Connection conn)
       throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select USER from dual");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println ("User name is " + rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset =  null;

    // Close the Statement
    stmt.close();
    stmt = null;
  }
}

Data Source with JNDI--DataSourceJNDI.java

This example shows how to use JDBC 2.0 data sources with JNDI. For general information about data sources, including how to use them with or without JNDI, see "Data Sources".

This class includes do_bind() and do_lookup() methods for JNDI functionality, as well as a getUserName() method.

/**
 * A Simple DataSource sample with JNDI.
 * This is tested using File System based reference 
 * implementation of JNDI SPI driver from JavaSoft.
 * You need to download fscontext1_2beta2.zip from
 * JavaSoft site.
 * Include providerutil.jar & fscontext.jar extracted
 * from the above ZIP in the classpath. 
 * Create a directory /tmp/JNDI/jdbc
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;
import javax.naming.*;
import javax.naming.spi.*;
import java.util.Hashtable;

public class DataSourceJNDI
{
  public static void main (String args [])
    throws SQLException, NamingException
  {
    // Initialize the Context
    Context ctx = null;
    try {
      Hashtable env = new Hashtable (5);
      env.put (Context.INITIAL_CONTEXT_FACTORY,
               "com.sun.jndi.fscontext.RefFSContextFactory");
      env.put (Context.PROVIDER_URL, "file:/tmp/JNDI");
      ctx = new InitialContext(env);
    } catch (NamingException ne)
    {
      ne.printStackTrace();
    }

    do_bind(ctx, "jdbc/sampledb");
    do_lookup(ctx, "jdbc/sampledb");

  }

  static void do_bind (Context ctx, String ln)
    throws SQLException, NamingException
  {
    // Create a OracleDataSource instance explicitly
    OracleDataSource ods = new OracleDataSource();

    // Set the user name, password, driver type and network protocol
    ods.setUser("scott");
    ods.setPassword("tiger");
    ods.setDriverType("oci8");
    ods.setNetworkProtocol("ipc");

    // Bind it 
    System.out.println ("Doing a bind with the logical name : " + ln);
    ctx.bind (ln,ods);
  }

  static void do_lookup (Context ctx, String ln)
    throws SQLException, NamingException
  {

    System.out.println ("Doing a lookup with the logical name : " + ln);
    OracleDataSource ods = (OracleDataSource) ctx.lookup (ln);

    // Retrieve a connection
    Connection conn = ods.getConnection();
    getUserName(conn);
    // Close the connection
    conn.close();
    conn = null;
  }

  static void getUserName(Connection conn)
       throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select USER from dual");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println ("User name is " + rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset =  null;

    // Close the Statement
    stmt.close();
    stmt = null;
  }
}

Pooled Connection--PooledConnection.java

This is a simple example of how to use JDBC 2.0 pooled connection functionality. For general information about connection pooling, see "Connection Pooling".

/*
 * A simple Pooled Connection Sample
 */

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

class PooledConnection1
{
  public static void main (String args [])
       throws SQLException
  {

    // Create a OracleConnectionPoolDataSource instance
    OracleConnectionPoolDataSource ocpds =
                               new OracleConnectionPoolDataSource();

    // Set connection parameters
    ocpds.setURL("jdbc:oracle:oci8:@");
    ocpds.setUser("scott");
    ocpds.setPassword("tiger");

    // Create a pooled connection
    PooledConnection pc  = ocpds.getPooledConnection();

    // Get a Logical connection
    Connection conn = pc.getConnection();

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset = null;

    // Close the Statement
    stmt.close();
    stmt = null;

    // Close the logical connection
    conn.close();
    conn = null;

    // Close the pooled connection
    pc.close();
    pc = null;
  }
}

OCI Connection Pool--OCIConnectionPool.java

This sample demonstrates OCI connection pooling.

For information on OCI connection pooling, see "OCI Driver Connection Pooling".

/*
 * A simple OCI Connection Pool Sample
 */

import java.sql.*;
import javax.sql.*;
import java.util.Properties;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.oci.*;

class OCIConnectionPool
{
  public static void main (String args [])
       throws SQLException
  {

    String url = "jdbc:oracle:oci:@";
    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }

    // Create an OracleOCIConnectionPool instance with default configuration
    OracleOCIConnectionPool cpool = new OracleOCIConnectionPool("scott", 
      "tiger", url, null);

    // Print out the default configuration for the OracleOCIConnectionPool
    System.out.println ("-- The default configuration for the  
    OracleOCIConnectionPool --");
    displayPoolConfig(cpool);

    // Get a connection from the pool
    OracleOCIConnection conn1 = (OracleOCIConnection) 
    cpool.getConnection("scott", "tiger");

    // Create a Statement
    Statement stmt = conn1.createStatement ();

    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

    // Iterate through the result and print the employee names
    System.out.println ("-- Use the connection from the OracleOCIConnectionPool
      --");
    while (rset.next ())
      System.out.println (rset.getString (1));

    System.out.println ("-- Use another connection from the 
    OracleOCIConnectionPool --");

    // Get another connection from the pool with different userID and password
    OracleOCIConnection conn2 = (OracleOCIConnection) 
    cpool.getConnection("system", "manager");

    // Create a Statement
    stmt = conn2.createStatement ();

    // Select the USER from DUAL to test the connection
    rset = stmt.executeQuery ("select USER from DUAL");

    // Iterate through the result and print the employee names
    rset.next ();
    System.out.println (rset.getString (1));

    // Reconfigure the OracleOCIConnectionPool in case the performance is too 
    bad.
    // This might happen when many users are trying to connect at the same time.
    // In this case, increase MAX_LIMIT to some larger number, and also increase 
    // INCREMENT to a positive number.

    Properties p  = new Properties();
    p.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, 
    Integer.toString(cpool.getMinLimit()));
    p.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, 
    Integer.toString(cpool.getMaxLimit() * 2)) ;
    if (cpool.getConnectionIncrement() > 0)
        // Keep the old value
        p.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, 
    Integer.toString(cpool.getConnectionIncrement()));
    else
        // Set it to a number larger than 0
        p.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1") ;
    
    // Enable the new configuration
    cpool.setPoolConfig(p);

    // Print out the current configuration for the OracleOCIConnectionPool
    System.out.println ("-- The new configuration for the 
    OracleOCIConnectionPool --");
    displayPoolConfig(cpool);

    // Close the RseultSet
    rset.close();
    rset = null;

    // Close the Statement
    stmt.close();
    stmt = null;

    // Close the connections
    conn1.close();
    conn2.close();
    conn1 = null;
    conn2 = null;

    // Close the OracleOCIConnectionPool
    cpool.close();
    cpool = null;
  }

  // Display the current status of the OracleOCIConnectionPool
  private static void displayPoolConfig (OracleOCIConnectionPool cpool)
      throws SQLException
  {
    System.out.println (" Min poolsize Limit: " + cpool.getMinLimit());
    System.out.println (" Max poolsize Limit: " + cpool.getMaxLimit());
    System.out.println (" Connection Increment: " + 
    cpool.getConnectionIncrement());
    System.out.println (" NoWait: " + cpool.getNoWait());
    System.out.println (" Timeout: " + cpool.getTimeout());
    System.out.println (" PoolSize: " + cpool.getPoolSize());
    System.out.println (" ActiveSize: " + cpool.getActiveSize());
  }
}

Middle-Tier Authentication--NtierAuth.java

This sample demonstrates middle-tier authentication through proxy connections.

For information on middle-tier authentication, see "Middle-Tier Authentication Through Proxy Connections".

/*
 * A Ntier Authentication Sample
 *
 */

import java.sql.*;
import javax.sql.*;
import java.util.Properties;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.oci.*;

class NtierAuth
{
  public static void main (String args [])
       throws SQLException
  {
    // Step 1: Connect as system/manager to create the users, setup roles and 
    proxies.

    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    String url = "jdbc:oracle:oci8:@";
    try {
      String url1 = System.getProperty("JDBC_URL");
      if (url1 != null)
        url = url1;
    } catch (Exception e) {
      // If there is any security exception, ignore it
      // and use the default
    }
    
    // Connect to the database as system/manager
    Connection sysConn = DriverManager.getConnection (url, "system", "manager");

    // Do some cleanup
    trySQL (sysConn, "drop role role1");
    trySQL (sysConn, "drop role role2");
    trySQL (sysConn, "drop user client cascade");
    trySQL (sysConn, "drop user proxy cascade");

    // Create a Statement
    Statement sysStmt = sysConn.createStatement ();

    // Create client and proxy
    sysStmt.execute("create user proxy identified by mehul");
    sysStmt.execute("create user client identified by ding");

    // Grant privilages to client and proxy
    sysStmt.execute("grant create session, connect, resource to proxy");
    sysStmt.execute("grant create session, connect, resource to client");

    // Create and setup roles with system connection
    sysStmt.execute("create role role1");
    sysStmt.execute("create role role2");

    // Connect to the database as proxy
    Connection proxyConn = DriverManager.getConnection (url, "proxy", "mehul");

    // Create a table with proxy connection
    Statement proxyStmt = proxyConn.createStatement ();
    proxyStmt.execute("create table account (purchase number)");
    proxyStmt.execute("insert into account values (6)");

    // Grant privilages to role1, role2
    proxyStmt.execute("grant select on account to role1");
    proxyStmt.execute("grant insert on account to role2");

    // Close the proxy statement and connection
    proxyStmt.close();
    proxyConn.close();

    // Grant role1, role2 to client
    sysStmt.execute("grant role1, role2 to client");

    // Grant proxy privilage to connect as client
    sysStmt.execute("alter user client grant connect through proxy with role 
    role1");

    // Step 2: Use OCIConnectionPool to get the proxy connection

    // Create an OracleOCIConnectionPool instance with default configuration 
    using proxy/mehul
    OracleOCIConnectionPool cpool = new OracleOCIConnectionPool("proxy", 
    "mehul", url, null);

    Properties prop = new Properties();
    String[] roles = {"role1"};
    prop.put(OracleOCIConnectionPool.PROXY_USER_NAME,"client" );
    prop.put(OracleOCIConnectionPool.PROXY_ROLES, roles);

    // Get the proxy connection
    OracleOCIConnection conn = (OracleOCIConnection) 
         cpool.getProxyConnection(OracleOCIConnectionPool.PROXYTYPE_USER_NAME, 
    prop);
   
    // Create a Statement
    Statement stmt = conn.createStatement ();
    
    // Select the ENAME column from the EMP table
    ResultSet rset = stmt.executeQuery ("select * from proxy.account");

    // Iterate through the result and print the purchase number
    System.out.println ("-- Do a Select from the proxy connection --");
    while (rset.next ())
      System.out.println (rset.getString (1));

    // Close the RseultSet
    rset.close();
    rset = null;

    // Now, try to do an Insert. This shouldn't be authorized
    System.out.println ("-- Now, try to do an Insert with the proxy connection 
    --");
    try {
      stmt.execute("insert into proxy.account values (9)");
    } catch (SQLException e) {
      System.out.println ("Exception thrown: " + e.getMessage());
    }
    finally {
        if (stmt != null)
           stmt.close();
    }

    // Close the connection
    conn.close();
    conn = null;

    // Close the OracleOCIConnectionPool
    cpool.close();
    cpool = null;

    // Make the cleanup
    trySQL (sysConn, "drop role role1");
    trySQL (sysConn, "drop role role2");
    trySQL (sysConn, "drop user client cascade");
    trySQL (sysConn, "drop user proxy cascade");

    // Close the system statement and connection
    sysStmt.close();
    sysConn.close();

  }

  // Used for Cleaning up the database
  private static void trySQL (Connection conn, String sqlString)
      throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    try {
        stmt.execute(sqlString);
        stmt.close();
    } catch (SQLException e) {
        // In case the user or role hasn't been created, ignore it.
    }
    finally {
        if (stmt != null)
           stmt.close();
    }
  }
}

JDBC OCI Application Failover Callbacks--OCIFailOver.java

This sample demonstrates the registration and operation of JDBC OCI application failover callbacks.

For information on Transparent Application Failover (TAF) and failover events, see "OCI Driver Transparent Application Failover".

/* 
 * This sample demonstrates the registration and operation of
 * JDBC OCI application failover callbacks
 * 
 * Note: Before you run this sample, set up the following
 *       service in tnsnames.ora: 
 *       inst_primary=(DESCRIPTION=
 *             (ADDRESS=(PROTOCOL=tcp)(Host=hostname)(Port=1521))
 *             (CONNECT_DATA=(SERVICE_NAME=ORCL)
 *                           (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC))
 *             )
 *           )
 *       Please see the Oracle Net Administrator's Guide for more detail about 
 *       failover_mode
 *
 * To demonstrate the the functionality, first compile and start up the sample,
 *    then log into sqlplus and connect /as sysdba. While the sample is still 
 *    running, shutdown the database with "shutdown abort;". At this moment, 
 *    the failover callback functions should be invoked. Now, the database can
 *    be restarted, and the interupted query will be continued.
 */

// You need to import java.sql and oracle.jdbc packages to use
// JDBC OCI failover callback 

import java.sql.*;
import java.net.*;
import java.io.*;
import java.util.*;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleOCIFailover;


public class OCIFailOver {

  static final String user = "scott";
  static final String password = "tiger";
  static final String driver_class = "oracle.jdbc.OracleDriver";

  static final String URL = "jdbc:oracle:oci8:@inst_primary"; 


  public static void main (String[] args) throws Exception {

    Connection conn = null;
    CallBack   fcbk= new CallBack();
    String     msg = null;
    Statement  stmt = null;
    ResultSet rset = null; 
 
    // Load JDBC driver
    try {
      Class.forName(driver_class);
    }
    catch(Exception e) {
      System.out.println(e);
    }

    // Connect to the database
    conn = DriverManager.getConnection(URL, user, password);

    // register TAF callback function
    ((OracleConnection) conn).registerTAFCallback(fcbk, msg);

    // Create a Statement
    stmt = conn.createStatement ();

    for (int i=0; i<30; i++) {
      // Select the ENAME column from the EMP table
      rset = stmt.executeQuery ("select ENAME from EMP");

      // Iterate through the result and print the employee names
      while (rset.next ()) 
        System.out.println (rset.getString (1));

      // Sleep one second to make it possible to shutdown the DB.
      Thread.sleep(1000);
    } // End for
 
    // Close the RseultSet
    rset.close();

    // Close the Statement
    stmt.close();

    // Close the connection
    conn.close();


  } // End Main()

} // End class jdemofo 


/*
 * Define class CallBack
 */
class CallBack implements OracleOCIFailover {
   
   // TAF callback function 
   public int callbackFn (Connection conn, Object ctxt, int type, int event) {

     /*********************************************************************
      * There are 7 possible failover event
      *   FO_BEGIN = 1   indicates that failover has detected a 
      *                  lost conenction and faiover is starting.
      *   FO_END = 2     indicates successful completion of failover.
      *   FO_ABORt = 3   indicates that failover was unsuccessful, 
      *                  and there is no option of retrying.
      *   FO_REAUTH = 4  indicates that a user handle has been re-
      *                  authenticated. 
      *   FO_ERROR = 5   indicates that failover was temporarily un-
      *                  successful, but it gives the apps the opp-
      *                  ortunity to handle the error and retry failover.
      *                  The usual method of error handling is to issue 
      *                  sleep() and retry by returning the value FO_RETRY
      *   FO_RETRY = 6
      *   FO_EVENT_UNKNOWN = 7  It is a bad failover event
      *********************************************************************/
     String failover_type = null;

     switch (type) {
         case FO_SESSION: 
                failover_type = "SESSION";
                break;
         case FO_SELECT:
                failover_type = "SELECT";
                break;
         default:
                failover_type = "NONE";
     }

     switch (event) {
      
       case FO_BEGIN:
            System.out.println(ctxt + ": "+ failover_type + " failing over...");
            break;
       case FO_END:
            System.out.println(ctxt + ": failover ended");
            break;
       case FO_ABORT:
            System.out.println(ctxt + ": failover aborted.");
            break;
       case FO_REAUTH:
            System.out.println(ctxt + ": failover.");
            break;
       case FO_ERROR:
            System.out.println(ctxt + ": failover error gotten. Sleeping...");
            // Sleep for a while 
            try {
              Thread.sleep(100);
            }
            catch (InterruptedException e) {
               System.out.println("Thread.sleep has problem: " + e.toString());
            }
            return FO_RETRY;
       default:
            System.out.println(ctxt + ": bad failover event.");
            break;
       
     }  

     return 0;

   }
}

Oracle Connection Cache (dynamic)--CCache1.java

This is the first of two examples of connection caching using the Oracle sample implementation available with class OracleConnectionCacheImpl.

This example uses the dynamic scheme for situations where the maximum number of pooled connections has already been reached--new pooled connection instances are created as needed, but each one is automatically closed and freed as soon as the JDBC application is done using the logical connection instance that the pooled connection instance provided.

For information about connection caching in general and Oracle's sample implementation in particular, see "Connection Caching".

/**
 *  JDBC 2.0 Spec doesn't mandate that JDBC vendors implement a 
 *  Connection Cache. However, we implemented a basic one with two
 *  schemes as an example.
 *  A Sample demo to illustrate DYNAMIC_SCHEME of OracleConnectionCacheImpl.
 *  Dynamic Scheme :  This is the default scheme. New connections could be 
 *  created beyond the Max limit upon request but closed and freed when the 
 *  logical connections are closed. When all the connections are active and 
 *  busy, requests for new connections willend up creating new physical 
 *  connections. But these physical connections are closed when the
 *  corresponding logical connections are closed. A typical grow and shrink 
 *  scheme. 
 */

import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

class CCache1
{
  public static void main (String args [])
       throws SQLException
  {
    OracleConnectionCacheImpl ods = new OracleConnectionCacheImpl();
    ods.setURL("jdbc:oracle:oci8:@");
    ods.setUser("scott");
    ods.setPassword("tiger");

     // Set the Max Limit
    ods.setMaxLimit (3);

    Connection conn1 = null;
    conn1 = ods.getConnection();
    if (conn1 != null)
      System.out.println("Connection 1 " + " Succeeded!");
    else
      System.out.println("Connection 1 " + " Failed !!!");

    Connection conn2 = null;
    conn2 = ods.getConnection();
    if (conn2 != null)
      System.out.println("Connection 2 " + " Succeeded!");
    else
      System.out.println("Connection 2 " + " Failed !!!");

    Connection conn3 = null;
    conn3 = ods.getConnection();
    if (conn3 != null)
      System.out.println("Connection 3 " + " Succeeded!");
    else
      System.out.println("Connection 3 " + " Failed !!!");

    Connection conn4 = null;
    conn4 = ods.getConnection();
    if (conn4 != null)
      System.out.println("Connection 4 " + " Succeeded!");
    else
      System.out.println("Connection 4 " + " Failed !!!");

    Connection conn5 = null;
    conn5 = ods.getConnection();
    if (conn5 != null)
      System.out.println("Connection 5 " + " Succeeded!");
    else
      System.out.println("Connection 5 " + " Failed !!!");


    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

    // Close 3 logical Connections
    conn1.close();
    conn2.close();
    conn3.close();

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

    // close the Data Source
    ods.close();

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());
  }
}

Oracle Connection Cache ("fixed with no wait")--CCache2.java

This is the second of two examples of connection caching using the Oracle sample implementation available with class OracleConnectionCacheImpl.

This example uses the "fixed with no wait" scheme for situations where the maximum number of pooled connections has already been reached--a null is returned when a connection is requested.

For information about connection caching in general and Oracle's sample implementation in particular, see "Connection Caching".

/**
 *  JDBC 2.0 Spec doesn't mandate that JDBC vendors implement a
 *  Connection Cache. However, we implemented a basic one with 2
 *  schemes as an Example.
 *  A Sample demo to illustrate FIXED_RETURN_NULL_SCHEME of 
 *  OracleConnectionCacheImpl.
 *  Fixed with NoWait :  At no instance there will be more active 
 *  connections than the Maximum limit. Request for new connections 
 *  beyond the max limit will return null. 
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;

public class CCache2 {

  public static void main (String args [])
       throws SQLException
  {

    // Create a OracleConnectionPoolDataSource as an factory
    // of PooledConnections for the Cache to create.
    OracleConnectionPoolDataSource ocpds =
                               new OracleConnectionPoolDataSource();
    ocpds.setURL("jdbc:oracle:oci8:@");
    ocpds.setUser("scott");
    ocpds.setPassword("tiger");

    // Associate it with the Cache
    OracleConnectionCacheImpl ods = new OracleConnectionCacheImpl(ocpds);

    // Set the Max Limit
    ods.setMaxLimit (3);

    // Set the Scheme
    ods.setCacheScheme (OracleConnectionCacheImpl.FIXED_RETURN_NULL_SCHEME);

    Connection conn = null;
    for (int i=0; i < 5; ++i )
    {
      conn = ods.getConnection();
      if (conn != null)
        System.out.println("Connection " + i + " Succeeded!");
      else
        System.out.println("Connection " + i + " Failed !!!");
    }

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

    // close the Data Source
    ods.close();

    System.out.println("Active size : " + ods.getActiveSize());
    System.out.println("Cache Size is " + ods.getCacheSize());

  }
}

XA with Suspend and Resume--XA2.java

This sample shows how to suspend and resume a transaction. It uses standard XA resource functionality to suspend and resume the transaction, but includes comments about how to use the Oracle extension suspend() and resume() methods as an alternative.

This class includes a createXid() method to form transaction IDs for purposes of this example.

For general information about distributed transactions and XA functionality, see Chapter 15, "Distributed Transactions".

/*
    A simple XA demo with suspend and resume. Opens 2 global
    transactions each of one branch. Does some DML on the first one
    and suspends it and does some DML on the 2nd one and resumes the
    first one and commits. Basically, to illustrate interleaving
    of global transactions.
    Need a java enabled 8.1.6 database to run this demo.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

class XA2
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        DriverManager.registerDriver(new OracleDriver());

        // You can put a database name after the @ sign in the connection URL.
        Connection conn =
          DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmt = conn.createStatement ();

        try
        {
          // Drop the test table
          stmt.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmt.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        try
        {
          // Drop the test table
          stmt.execute ("drop table my_tab");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmt.execute ("create table my_tab (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create a XADataSource instance
        OracleXADataSource oxds = new OracleXADataSource();
        oxds.setURL("jdbc:oracle:oci8:@");
        oxds.setUser("scott");
        oxds.setPassword("tiger");
    
        // get a XA connection
        XAConnection pc  = oxds.getXAConnection();
        // Get a logical connection
        Connection conn1 = pc.getConnection();
       
        // Get XA resource handle
        XAResource oxar = pc.getXAResource();
        Xid xid1 = createXid(111,111);
    
        // Start a transaction branch
        oxar.start (xid1, XAResource.TMNOFLAGS);
    
        // Create a Statement
        Statement stmt1 = conn1.createStatement ();
    
        // Do some DML
        stmt1.executeUpdate ("insert into my_table values (2727)");
    
        // Suspend the first global transaction
        // ((OracleXAResource)oxar).suspend (xid1); or
        oxar.end (xid1, XAResource.TMSUSPEND);

        Xid xid2 = createXid(222,222);
        oxar.start (xid2, XAResource.TMNOFLAGS);
        Statement stmt2 = conn1.createStatement ();
        stmt2.executeUpdate ("insert into my_tab values (7272)");
        oxar.commit (xid2, true);
        stmt2.close();
        stmt2 = null;

        // Close the Statement
        stmt1.close();
        stmt1 = null;

        // Resume the first global transaction
        // ((OracleXAResource)oxar).resume (xid1); or
        oxar.start (xid1, XAResource.TMRESUME);

        // End the branch
        oxar.end(xid1, XAResource.TMSUCCESS);
    
        // Do a 1 phase commit
        oxar.commit (xid1, true);

        // Close the connection
        conn1.close();   
        conn1 = null;

        // close the XA connection
        pc.close();
        pc = null;

        ResultSet rset = stmt.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));
  
        rset.close();
        rset = null;

        rset = stmt.executeQuery ("select col1 from my_tab");
        while (rset.next())
          System.out.println("Col1 is " + rset.getString(1));
  
        rset.close();
        rset = null;

        stmt.close();
        stmt = null;

        conn.close();
        conn = null;

    } catch (SQLException sqe)
    {
      sqe.printStackTrace();
    } catch (XAException xae)
    {
      if (xae instanceof OracleXAException)
      {
        System.out.println("XA error is " +
                          ((OracleXAException)xae).getXAError());
        System.out.println("SQL error is " +
                          ((OracleXAException)xae).getOracleError());
      }
      xae.printStackTrace();
    }
  }

  static Xid createXid(int gd, int bd)
    throws XAException
  {
    byte[] gid = new byte[1]; gid[0]= (byte) gd;
    byte[] bid = new byte[1]; bid[0]= (byte) bd;
    byte[] gtrid = new byte[64];
    byte[] bqual = new byte[64];
    System.arraycopy (gid, 0, gtrid, 0, 1);
    System.arraycopy (bid, 0, bqual, 0, 1);
    Xid xid = new OracleXid(0x1234, gtrid, bqual);
    return xid;
  }
}

XA with Two-Phase Commit Operation--XA4.java

This example shows basic two-phase COMMIT functionality for a distributed transaction.

This class includes a createXid() method to form transaction IDs for purposes of this example. It also includes doSomeWork1() and doSomeWork2() methods to perform SQL operations.

For general information about distributed transactions and XA functionality, see Chapter 15, "Distributed Transactions".

/*
    A simple 2 phase XA demo. Both the branches talk to different RMS
    Need 2 java enabled 8.1.6 databases to run this demo.
      -> start-1
      -> start-2
      -> Do some DML on 1
      -> Do some DML on 2
      -> end 1
      -> end 2
      -> prepare-1
      -> prepare-2
      -> commit-1
      -> commit-2
    Please change the URL2 before running this.
 */

// You need to import the java.sql package to use JDBC
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.xa.OracleXid;
import oracle.jdbc.xa.OracleXAException;
import oracle.jdbc.xa.client.*;
import javax.transaction.xa.*;

class XA4
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        String URL1 = "jdbc:oracle:oci8:@";
        String URL2 =
                    "jdbc:oracle:thin:@
                    (description=(address=(host=dlsun991)(protocol=tcp)
                    (port=5521))(connect_data=(sid=rdbms2)))";

        DriverManager.registerDriver(new OracleDriver());

        // You can put a database name after the @ sign in the connection URL.
        Connection conna =
          DriverManager.getConnection (URL1, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmta = conna.createStatement ();

        Connection connb =
          DriverManager.getConnection (URL2, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmtb = connb.createStatement ();

        try
        {
          // Drop the test table
          stmta.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmta.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        try
        {
          // Drop the test table
          stmtb.execute ("drop table my_tab");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmtb.execute ("create table my_tab (col1 char(30))");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create a XADataSource instance
        OracleXADataSource oxds1 = new OracleXADataSource();
        oxds1.setURL("jdbc:oracle:oci8:@");
        oxds1.setUser("scott");
        oxds1.setPassword("tiger");

        OracleXADataSource oxds2 = new OracleXADataSource();

        oxds2.setURL
              ("jdbc:oracle:thin:@(description=(address=(host=dlsun991)
              (protocol=tcp)(port=5521))(connect_data=(sid=rdbms2)))");
        oxds2.setUser("scott");
        oxds2.setPassword("tiger");
    
        // Get a XA connection to the underlying data source
        XAConnection pc1  = oxds1.getXAConnection();

        // We can use the same data source 
        XAConnection pc2  = oxds2.getXAConnection();

        // Get the Physical Connections
        Connection conn1 = pc1.getConnection();
        Connection conn2 = pc2.getConnection();

        // Get the XA Resources
        XAResource oxar1 = pc1.getXAResource();
        XAResource oxar2 = pc2.getXAResource();

        // Create the Xids With the Same Global Ids
        Xid xid1 = createXid(1);
        Xid xid2 = createXid(2);

        // Start the Resources
        oxar1.start (xid1, XAResource.TMNOFLAGS);
        oxar2.start (xid2, XAResource.TMNOFLAGS);

        // Do  something with conn1 and conn2
        doSomeWork1 (conn1);
        doSomeWork2 (conn2);

        // END both the branches -- THIS IS MUST
        oxar1.end(xid1, XAResource.TMSUCCESS);
        oxar2.end(xid2, XAResource.TMSUCCESS);

        // Prepare the RMs
        int prp1 =  oxar1.prepare (xid1);
        int prp2 =  oxar2.prepare (xid2);

        System.out.println("Return value of prepare 1 is " + prp1);
        System.out.println("Return value of prepare 2 is " + prp2);

        boolean do_commit = true;

        if (!((prp1 == XAResource.XA_OK) || (prp1 == XAResource.XA_RDONLY)))
           do_commit = false;

        if (!((prp2 == XAResource.XA_OK) || (prp2 == XAResource.XA_RDONLY)))
           do_commit = false;

       System.out.println("do_commit is " + do_commit);
        System.out.println("Is oxar1 same as oxar2 ? " + oxar1.isSameRM(oxar2));

        if (prp1 == XAResource.XA_OK)
          if (do_commit)
             oxar1.commit (xid1, false);
          else
             oxar1.rollback (xid1);

        if (prp2 == XAResource.XA_OK)
          if (do_commit)
             oxar2.commit (xid2, false);
          else
             oxar2.rollback (xid2);

         // Close connections
        conn1.close();
        conn1 = null;
        conn2.close();
        conn2 = null;

        pc1.close();
        pc1 = null;
        pc2.close();
        pc2 = null;

        ResultSet rset = stmta.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));
  
        rset.close();
        rset = null;

        rset = stmtb.executeQuery ("select col1 from my_tab");
        while (rset.next())
          System.out.println("Col1 is " + rset.getString(1));
  
        rset.close();
        rset = null;

        stmta.close();
        stmta = null;
        stmtb.close();
        stmtb = null;

        conna.close();
        conna = null;
        connb.close();
        connb = null;

    } catch (SQLException sqe)
    {
      sqe.printStackTrace();
    } catch (XAException xae)
    {
      if (xae instanceof OracleXAException) {
        System.out.println("XA Error is " +
                      ((OracleXAException)xae).getXAError());
        System.out.println("SQL Error is " +
                      ((OracleXAException)xae).getOracleError());
      }
    }
  }

  static Xid createXid(int bids)
    throws XAException
  {
    byte[] gid = new byte[1]; gid[0]= (byte) 9;
    byte[] bid = new byte[1]; bid[0]= (byte) bids;
    byte[] gtrid = new byte[64];
    byte[] bqual = new byte[64];
    System.arraycopy (gid, 0, gtrid, 0, 1);
    System.arraycopy (bid, 0, bqual, 0, 1);
    Xid xid = new OracleXid(0x1234, gtrid, bqual);
    return xid;
  }

  private static void doSomeWork1 (Connection conn)
   throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    int cnt = stmt.executeUpdate ("insert into my_table values (4321)");

    System.out.println("No of rows Affected " + cnt);

    stmt.close();
    stmt = null;
  }

  private static void doSomeWork2 (Connection conn)
    throws SQLException
  {
    // Create a Statement
    Statement stmt = conn.createStatement ();

    int cnt = stmt.executeUpdate ("insert into my_tab values ('test')");

    System.out.println("No of rows Affected " + cnt);

    stmt.close();
    stmt = null;
  }

}

HeteroRM XA--XA6.java

The following code example shows how to use the HeteroRM XA feature. All the XA-specific features that are available through JDBC XA are available in HeteroRM XA, such as commit, rollback, suspend, and resume.

For a complete description of HeteroRM XA, see "OCI HeteroRM XA".

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

class XA6
{
  public static void main (String args [])
       throws SQLException
  {

    try
    {
        DriverManager.registerDriver(new OracleDriver());

        String url = "jdbc:oracle:oci8:@";
        try {
          String url1 = System.getProperty("JDBC_URL");
          if (url1 != null)
            url = url1;
        } catch (Exception e) {
          // If there is any security exception, ignore it
          // and use the default
        }

        // Connect to the database
        Connection conn =
          DriverManager.getConnection (url, "scott", "tiger");

        // Prepare a statement to create the table
        Statement stmt = conn.createStatement ();

        try
        {
          // Drop the test table
          stmt.execute ("drop table my_table");
        }
        catch (SQLException e)
        {
          // Ignore an error here
        }

        try
        {   
          // Create a test table
          stmt.execute ("create table my_table (col1 int)");
        }
        catch (SQLException e)
        {
          // Ignore an error here too
        }

        // Create a XADataSource instance
        OracleXADataSource oxds = new OracleXADataSource();
        oxds.setURL(url);

        // Set the nativeXA property to use HeteroRM XA feature
        oxds.setNativeXA(true);

        // Set the tnsEntry property to an older DB as required
        oxds.setTNSEntryName("ora805");

        oxds.setUser("scott");
        oxds.setPassword("tiger");
    
        // get a XA connection
        XAConnection pc  = oxds.getXAConnection();
        // Get a logical connection
        Connection conn1 = pc.getConnection();
       
        // Get XA resource handle
        XAResource oxar = pc.getXAResource();
    
        Xid xid = createXid();
    
        // Start a transaction branch
        oxar.start (xid, XAResource.TMNOFLAGS);
    
        // Create a Statement
        Statement stmt1 = conn1.createStatement ();
    
        // Do some DML
        stmt1.executeUpdate ("insert into my_table values (7321)");
    
        // Close the Statement
        stmt1.close();
        stmt1 = null;

        // End the branch
        oxar.end(xid, XAResource.TMSUCCESS);
    
        // Do a 1 phase commit
        oxar.commit (xid, true);

        // Close the connection
        conn1.close();   
        conn1 = null;

        // close the XA connection
        pc.close();
        pc = null;

        ResultSet rset = stmt.executeQuery ("select col1 from my_table");
        while (rset.next())
          System.out.println("Col1 is " + rset.getInt(1));
  
        rset.close();
        rset = null;

        stmt.close();
        stmt = null;

        conn.close();
        conn = null;

    } catch (SQLException sqe)               // check for SQLExceptions
    {
      sqe.printStackTrace();
    } catch (XAException xae)                // check for XAExceptions
    {
      xae.printStackTrace();
    }
  }

  static Xid createXid()
    throws XAException
  {
    byte[] gid = new byte[1]; gid[0]= (byte) 1;
    byte[] bid = new byte[1]; bid[0]= (byte) 1;
    byte[] gtrid = new byte[64];
    byte[] bqual = new byte[64];
    System.arraycopy (gid, 0, gtrid, 0, 1);
    System.arraycopy (bid, 0, bqual, 0, 1);
    Xid xid = new OracleXid(0x1234, gtrid, bqual);
    return xid;
  }
}

Sample Applet

This section demonstrates the use of the Oracle JDBC Thin driver for a simple applet that selects "Hello World" and the date from the database. Both the HTML page and applet code are shown here. A JDBC applet, like any typical applet, can be deployed using any standard Web server and run from any standard browser.

For a complete discussion of how to use JDBC with applets, see "JDBC in Applets".

In this example, the Web server and database must be on the same host, as this is not a signed applet and does not use Oracle Connection Manager. For more information, see "Connecting to a Database on a Different Host Than the Web Server".

HTML Page--JdbcApplet.htm

Here is the HTML code for the user interface for the applet.

<html>
<head>
<title>JDBC applet</title>
</head>
<body>

<h1>JDBC applet</h1>

This page contains an example of an applet that uses the Thin JDBC
driver to connect to Oracle.<p>

The source code for the applet is in <a
href="JdbcApplet.java">JdbcApplet.java</a>.  Please check carefully
the driver class name and the connect string in the code.<p>

The Applet tag in this file contains a CODEBASE entry that must be set
to point to a directory containing the Java classes from the Thin JDBC
distribution *and* the compiled JdbcApplet.class.<p>

As distributed it will *not* work because the classes*.zip files are not
in this directory.<p>

<hr>
<applet codebase="." archive="classes111.zip"
code="JdbcApplet" width=500 height=200>
</applet>
<hr>

Applet Code--JdbcApplet.java

Here is the source code for the applet.

/*
 * This sample applet just selects 'Hello World' and the date from the database
 */

// Import the JDBC classes
import java.sql.*;

// Import the java classes used in applets
import java.awt.*;
import java.io.*;
import java.util.*;

public class JdbcApplet extends java.applet.Applet
{

  // The connect string 
  static final String connect_string = 
                  "jdbc:oracle:thin:scott/tiger@langer:5521:rdbms";

  /* This is the kind of string you would use if going through the 
   * Oracle connection manager which lets you run the database on a 
   * different host than the Web Server. See the Oracle Net Administrator's
   * Guide for more information.
   * static final String connect_string = "jdbc:oracle:thin:scott/tiger@
   *               (description=(address_list=(address=(protocol=tcp)
   *               (host=dlsun511)(port=1610))(address=(protocol=tcp)
   *               (host=pkrishna-pc2)(port=1521)))
   *               (source_route=yes)(connect_data=(sid=orcl)))";
   */

  // The query we will execute
  static final String query = "select 'Hello JDBC: ' || sysdate from dual";
  

  // The button to push for executing the query
  Button execute_button;

  // The place where to dump the query result
  TextArea output;

  // The connection to the database
  Connection conn;

  // Create the User Interface
  public void init ()
  {
    this.setLayout (new BorderLayout ());
    Panel p = new Panel ();
    p.setLayout (new FlowLayout (FlowLayout.LEFT));
    execute_button = new Button ("Hello JDBC");
    p.add (execute_button);
    this.add ("North", p);
    output = new TextArea (10, 60);
    this.add ("Center", output);
  }

  // Do the work
  public boolean action (Event ev, Object arg)
  {
    if (ev.target == execute_button)
    {
      try
      {

      // See if we need to open the connection to the database
      if (conn == null)
      {
        // Load the JDBC driver
        DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

        // Connect to the databse
        output.appendText ("Connecting to " + connect_string + "\n");
        conn = DriverManager.getConnection (connect_string);
        output.appendText ("Connected\n");
      }

      // Create a statement
      Statement stmt = conn.createStatement ();

      // Execute the query
      output.appendText ("Executing query " + query + "\n");
      ResultSet rset = stmt.executeQuery (query);

      // Dump the result
      while (rset.next ())
        output.appendText (rset.getString (1) + "\n");

      // We're done
      output.appendText ("done.\n");
      }
      catch (Exception e)
      {
      // Oops
      output.appendText (e.getMessage () + "\n");
      }
      return true;
    }
    else
      return false;
  }
}

JDBC versus SQLJ Sample Code

This section contains a side-by-side comparison of two versions of the same sample code using Oracle CustomDatum functionality: one version is written in JDBC and the other in SQLJ. The objective of this section is to point out the differences in coding requirements between SQLJ and JDBC.

In the sample, two methods are defined: getEmployeeAddress() selects into a table and returns an employee's address based on the employee's number; updateAddress() takes the retrieved address, calls a stored procedure, and returns the updated address to the database.

In both versions of the sample code, these assumptions have been made:

• The ObjectDemo.sql SQL script (described below) has been run to create the necessary database entities.

• A PL/SQL stored function UPDATE_ADDRESS, which updates a given address, exists.

• The connection object (for JDBC) and default connection context (for SQLJ) have previously been created by the caller.

• Exceptions are handled by the caller.

• The value of the address argument (addr) passed to the updateAddress method can be null.

  Note: The JDBC and SQLJ versions of the code are partial samples only. They cannot be run independently.

SQL Program to Create Tables and Objects

Following is a listing of the ObjectDemo.sql script that creates the tables and objects referenced by the two versions of the sample code. The ObjectDemo.sql script creates a PERSON object, an ADDRESS object, a typed table (PERSONS) of PERSON objects, and a relational table (EMPLOYEES) for employee data.

/*** Using objects in SQLJ ***/
SET ECHO ON;
/**

/*** Clean up ***/
DROP TABLE EMPLOYEES
/
DROP TABLE PERSONS
/
DROP TYPE PERSON FORCE
/
DROP TYPE ADDRESS FORCE
/

/*** Create an address object ***/
CREATE TYPE address AS OBJECT
( 
  street        VARCHAR(60),
  city          VARCHAR(30),
  state         CHAR(2),
  zip_code      CHAR(5)
)
/

/*** Create a person object containing an embedded Address object ***/
CREATE TYPE person AS OBJECT
( 
  name    VARCHAR(30),
  ssn     NUMBER,
  addr    address
)
/

/*** Create a typed table for person objects ***/
CREATE TABLE persons OF person
/

/*** Create a relational table with two columns that are REFs 
     to person objects, as well as a column which is an Address object.***/

CREATE TABLE  employees
( empnumber            INTEGER PRIMARY KEY,
  person_data     REF  person,
  manager         REF  person,
  office_addr          address,
  salary               NUMBER
)

/
/*** insert code for UPDATE_ADDRESS stored procedure here
/

/*** Now let's put in some sample data
     Insert 2 objects into the persons typed table ***/

INSERT INTO persons VALUES (
            person('Wolfgang Amadeus Mozart', 123456,
            address('Am Berg 100', 'Salzburg', 'AU','10424')))
/
INSERT INTO persons VALUES (
            person('Ludwig van Beethoven', 234567,
            address('Rheinallee', 'Bonn', 'DE', '69234')))
/

/** Put a row in the employees table **/

INSERT INTO employees (empnumber, office_addr, salary) " +
            " VALUES (1001, address('500 Oracle Parkway', " +
            " 'Redwood City', 'CA', '94065'), 50000)
/

/** Set the manager and person REFs for the employee **/

UPDATE employees 
  SET manager =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart')
/

UPDATE employees 
  SET person_data =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')
/

COMMIT
/
QUIT

JDBC Version of the Sample Code

Following is the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database. Note, the "TO DOs" in the comment lines indicate where you might want to add additional code to enhance the usefulness of the code sample.

import java.sql.*;
import oracle.jdbc.*;

/**
  This is what we have to do in JDBC
  **/
public class SimpleDemoJDBC                                  // line 7
{

//TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno, Connection conn)
    throws SQLException                                     // line 13
  {
    Address addr;
    PreparedStatement pstmt =                               // line 16
      conn.prepareStatement("SELECT office_addr FROM employees" + 
       " WHERE empnumber = ?");
    pstmt.setInt(1, empno);
    OracleResultSet rs = (OracleResultSet)pstmt.executeQuery();
    rs.next();                                              // line 21
     //TO DO: what if false (result set contains no data)?
    addr = (Address)rs.getCustomDatum(1, Address.getFactory());
    //TO DO: what if additional rows? 
    rs.close();                                             // line 25
    pstmt.close();
    return addr;                                            // line 27
                                                           
  }

  public Address updateAddress(Address addr, Connection conn)
    throws SQLException                                     // line 30
                                                           
  {
    OracleCallableStatement cstmt = (OracleCallableStatement)
      conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }");    //line 34
    cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME);
                                                            // line 36
    if (addr == null) {
      cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME);
    } else {
      cstmt.setCustomDatum(2, addr);
    } 
                                
    cstmt.executeUpdate();                                  // line 43
    addr = (Address)cstmt.getCustomDatum(1, Address.getFactory());
    cstmt.close();                                          // line 45
    return addr;
  }
}   

Line 12:

In the getEmployeeAddress() method definition, you must pass the connection object to the method definition explicitly.

Lines 16-20:

Prepare a statement that selects an employee's address from the EMPLOYEES table on the basis of the employee number. The employee number is represented by a marker variable, which is set with the setInt() method. Note that because the prepared statement does not recognize the "INTO" syntax used in "SQL Program to Create Tables and Objects", you must provide your own code to populate the address (addr) variable. Since the prepared statement is returning a custom object, cast the output to an Oracle result set.

Lines 21-23:

Because the Oracle result set contains a custom object of type Address, use the getCustomDatum() method to retrieve it (the Address object could be created by JPublisher). The getCustomDatum() method requires you to use the static factory method Address.getFactory() to materialize an instance of an Address object. Since getCustomDatum() returns a Datum, cast the output to an Address object.

Note that the routine assumes a one-row result set. The "TO DOs" in the comment statements indicate that you must write additional code for the cases where the result set contains either no rows or more than one row.

Lines 25-27:

Close the result set and prepared statement objects, then return the addr variable.

Line 29:

In the updateAddress() definition, you must pass the connection object and the Address object explicitly.

The updateAddress() method passes an address to the database for update and fetches it back. The actual updating of the address is performed by the UPDATE_ADDRESS stored procedure (the code for this procedure is not illustrated in this example).

Line 33-43:

Prepare an Oracle callable statement that takes an address object (Address) and passes it to the UPDATE_ADDRESS stored procedure. To register an object as an output parameter, you must know the object's SQL typecode and SQL type name.

Before passing the address object (addr) as an input parameter, the program must determine whether addr has a value or is null. Depending on the value of addr, the program calls different set methods. If addr is null, the program calls setNull(); if it has a value, the program calls setCustomDatum().

Line 44:

Fetch the return result addr. Since the Oracle callable statement returns a custom object of type Address, use the getCustomDatum() method to retrieve it (the Address object could be created by JPublisher). The getCustomDatum() method requires you to use the static factory method Address.getFactory to materialize an instance of an Address object. Because getCustomDatum() returns a Datum, cast the output to an Address object.

Lines 45, 46:

Close the Oracle callable statement, then return the addr variable.

Coding Requirements of the JDBC Version

Note the following coding requirements for the JDBC version of the sample code:

• The getEmployeeAddress() and updateAddress() definitions must explicitly include the connection object.

• Long SQL strings must be concatenated with the SQL concatenation character ("+").

• You must explicitly manage resources (for example, close result set and statement objects).

• You must cast datatypes as needed.

• You must know the _SQL_TYPECODE and _SQL_NAME of the factory objects that you are registering as output parameters.

• Null data must be explicitly handled.

• Host variables must be represented by parameter markers in callable and prepared statements.

Maintaining JDBC Programs

JDBC programs have the potential of being expensive in terms of maintenance. For example, in the above code sample, if you add another WHERE clause, then you must change the SELECT string. If you append another host variable, then you must increment the index of the other host variables by one. A simple change to one line in a JDBC program might require changes in several other areas of the program.

SQLJ Version of the Sample Code

Following is the SQLJ version of the sample code that defines methods to retrieve an employee's address from the database, update the address, and return it to the database.

import java.sql.*;

/**
  This is what we have to do in SQLJ
  **/
public class SimpleDemoSQLJ                                  // line 6
{
  //TO DO: make a main that calls this?

  public Address getEmployeeAddress(int empno)              // line 10
    throws SQLException
  {
    Address addr;                                           // line 13
    #sql { SELECT office_addr INTO :addr FROM employees
           WHERE empnumber = :empno };
    return addr;
  }
                                                            // line 18
  public Address updateAddress(Address addr)
    throws SQLException
  {
    #sql addr = { VALUES(UPDATE_ADDRESS(:addr)) };          // line 23
    return addr;
  }
}

Line 10:

The getEmployeeAddress() method does not require a connection object. SQLJ uses a default connection context instance, which would have been defined previously somewhere in your application.

Lines 13-15:

The getEmployeeAddress() method retrieves an employee address according to employee number. Use standard SQLJ SELECT INTO syntax to select an employee's address from the employee table if their employee number matches the one (empno) passed in to getEmployeeAddress(). This requires a declaration of the Address object (addr) that will receive the data. The empno and addr variables are used as input host variables. (Host variables are sometimes also referred to as bind variables.)

Line 16:

The getEmployeeAddress() method returns the addr object.

Line 19:

The updateAddress() method also uses the default connection context instance.

Lines 19-23:

The address is passed to the updateAddress() method, which passes it to the database. The database updates it and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS stored function (the code for this function is not shown here). Use standard SQLJ function-call syntax to receive the address object (addr) output by UPDATE_ADDRESS.

Line 24:

The updateAddress() method returns the addr object.

Coding Requirements of the SQLJ Version

Note the following coding requirements (and lack of requirements) for the SQLJ version of the sample code:

• An explicit connection is not required; a default connection context will have been defined previously in your application.

• No datatype casting is required.

• SQLJ does not require knowledge of _SQL_TYPECODE, _SQL_NAME, or factories.

• Null data is handled implicitly.

• No explicit code for resource management is required (for closing statements or result sets, for example).

• SQLJ embeds host variables, in contrast to JDBC, which uses parameter markers.

• String concatenation for long SQL statements is not required.

• You do not have to register output parameters.

• SQLJ syntax is simpler; for example, SELECT INTO syntax is supported and OBDC-style escapes are not used.