Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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.driver.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.driver.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.driver.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; }

    }

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index