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; }

}

---