Sample Applications for Basic JDBC Features

This section contains code samples that demonstrate basic JDBC features.

Streaming Data

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 using the JDBC OCI driver for connecting to a database, and inserting and fetching LONG data using Java streams.

import java.sql.*;                                           // line 1
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);                             // line 18
 
    // 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                                     // line 34
    stmt.execute ("create table streamexample (NAME varchar2 (256), 
          DATA long)"); 
 
    File file = new File ("StreamExample.java");            // line 37
    InputStream is = new FileInputStream ("StreamExample.java"); 
    PreparedStatement pstmt = 
      conn.prepareStatement ("insert into streamexample (name, data) 
           values (?, ?)"); 
     pstmt.setString (1, "StreamExample"); 
     pstmt.setAsciiStream (2, is, (int)file.length ()); 
     pstmt.execute ();                                      // line 44
 
    // 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                                    // line 51
    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 ();                                          // line 66
    } 
  } 
}

Lines 1-18:

Import the necessary classes. Load the JDBC OCI driver with the DriverManager.registerDriver() method. Connect to the database with the getConnection(), as user scott with password tiger. Use the database URL jdbc:oracle:oci8:@. You can optionally enter a database name after the @ symbol. Disable AUTOCOMMIT to enhance performance. If you do not, the driver will issue execute and commit commands after every SQL statement.

Line 34:

Create a table STREAMEXAMPLE with a NAME column of type VARCHAR and a DATA column of type LONG.

Lines 37-44:

Insert the contents of the StreamExample.java into the table. To do this, create an input stream object for the Java file. Then, prepare a statement to insert character data into the NAME column and the stream data into the DATA column. Insert the NAME data with the setString(); insert the stream data with setAsciiStream().

Line 46:

Query the table to get the contents of the DATA column into a result set.

Line 51-66:

Get the data from the first row of the result set into the InputStream object gif_data. Create a FileOutputStream to write to the specified file object. Then, read the contents of the gif stream and write it to the file example.out.

---