JDBC Presentation

/*
 * 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.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:oci7:@", "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;");

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

/*
 * 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:oci7:@", "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 ();
    }
  }
}

/*
 *
 * This example demonstrate the use of the "sendBatch" API.
 * This allows the user to actually execute a set of batched
 * execute commands.
 *
 */

import java.sql.*;

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

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

    // (2) Connect to the database
    //     We can put a database name after the @ sign in the connection URL.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci7:@ssdora02:1521:ssd23", "scott", "tiger");

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

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

     // (3) Create a SQL Statement with one or more IN parameters.....
    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);

    // (4) 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 ();
  }
}

/*
 * This sample shows how to list all the names from the EMP table
 *
 * It uses the JDBC THIN driver.  See the same program in the
 * oci7 or oci8 samples directories to see how to use the other drivers.
 */

// 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.driver.OracleDriver());

    // Connect to the database
    // You must put a database name after the @ sign in the connection URL.
    // You can use either the fully specified SQL*net syntax or a short cut
    // syntax as ::.  The example uses the short cut syntax.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:thin:@ssdora02:1521:ssd23",
				   "ah_appl", "ah_appl");

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

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

    // Iterate through the result and print the employee names
    while (rset.next ())
      System.out.println (rset.getString (1));
  }
}
/*
JDBC applet
This page contains an example of an applet that uses the Thin JDBC driver to connect to Oracle.
The source code for the applet is in JdbcApplet.java. Please check carefully the driver class name and the connect string in the code.

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.

As distributed it will *not* work because the classes111.zip are not in this directory.
*/

/*
 * 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 driver to load
  static final String driver_class = "oracle.jdbc.driver.OracleDriver";

  // The connect string
  static final String connect_string =
                  "jdbc:oracle:thin:scott/tiger@dlsun511:1721:dbms733";

  // This is the kind of string you would use if going through the
  // Oracle 8 connection manager which lets you run the database on a
  // different host than the Web Server.  See the on-line documentation
  // 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 {
         // Clear the output area
         output.setText (null);

         // See if we need to open the connection to the database
         if (conn == null)
         {
           // Load the JDBC driver
           output.appendText ("Loading JDBC driver " + driver_class + "\n");
           Class.forName (driver_class);

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