plsql_fileexample.java

Java示例100

/* 
 * This sample demonstrate basic File support
 *
 * It shows
 * 1. how to use DIRECTORY object
 * 2. how to use PL/SQL package -- dbms_lob to
 *    open, access, retrieve information from,
 *    close object of BFILE which is stored at DIRECTORY.
 *
 * usage: java PLSQL_FileExample <test_dir>
 *        <test_dir> is a location where binary files
 *        file1 and file2 are stored
 *
 * note: 1. It needs jdk1.2 or later version and classes12.zip
 *       2. It drops, creates, and populates table
 *          test_dir_table in the database
 */

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 PLSQL_FileExample
{
  public static void main (String args [])
       throws Exception
  {
    if ( args.length != 1 )
    {
         System.out.println("usage: java PLSQL_FileExample <test_dr>");
         System.exit(0);
    }
    
    
    // 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.

    // 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, "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 '" + args[0] + "'");

    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 args[0]
    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 the open 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
  {
    OracleCallableStatement read =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.read (?, ?, ?, ?); end;");

    System.out.println ("Dumping file " + filegetname (conn, bfile));
    System.out.println ("File exists: " + fileexists (conn, bfile));
    System.out.println ("File open: " + fileisopen (conn, bfile));

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

    bfile = fileopen (conn, bfile);

    System.out.println ("File open: " + fileisopen (conn, bfile));

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

    long i = 0;
    int chunk = 10;

    while (i < length)
    {
      read.setBFILE (1, bfile);
      read.setLong (2, chunk);
      read.registerOutParameter (2, Types.NUMERIC);
      read.setLong (3, i + 1);
      read.registerOutParameter (4, Types.VARBINARY);
      read.execute ();

      long read_this_time = read.getLong (2);
      byte [] bytes_this_time = read.getBytes (4);

      System.out.print ("Read " + read_this_time + " bytes: ");

      int j;
      for (j = 0; j < read_this_time; j++)
	System.out.print (bytes_this_time [j] + " ");
      System.out.println ();

      i += read_this_time;
    }

    fileclose (conn, bfile);
    fileisopen (conn, bfile);

    read.close ();
  }

  // Utility function to get the length of a Bfile
  static long getLength (Connection conn, BFILE bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin ? := dbms_lob.getLength (?); end;");
    try
    {
      cstmt.registerOutParameter (1, Types.NUMERIC);
      cstmt.setBFILE (2, bfile);
      cstmt.execute ();
      return cstmt.getLong (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to test if a Bfile exists
  static boolean fileexists (Connection conn, BFILE bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin ? := dbms_lob.fileexists (?); end;");

    try
    {
      cstmt.registerOutParameter (1, Types.NUMERIC);
      cstmt.setBFILE (2, bfile);
      cstmt.execute ();
      return cstmt.getBoolean (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to return the filename of a Bfile
  static String filegetname (Connection conn, BFILE bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.filegetname (?, ?, ?); end;");

    try
    {
      cstmt.setBFILE (1, bfile);
      cstmt.registerOutParameter (2, Types.VARCHAR);
      cstmt.registerOutParameter (3, Types.VARCHAR);
      cstmt.execute ();
      
      return cstmt.getString (3);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to open a Bfile.
  // Note that an open Bfile is a different object from the
  // closed one.  The fileopen entrypoint returns the
  // open file object which is the one you have to use to
  // read the file contents.
  static BFILE fileopen (Connection conn, BFILE bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.fileopen (?, 0); end;");

    try
    {
      cstmt.setBFILE (1, bfile);
      cstmt.registerOutParameter (1, OracleTypes.BFILE);
      cstmt.execute ();
      return cstmt.getBFILE (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to test if a Bfile is open
  static boolean fileisopen (Connection conn, BFILE bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin ? := dbms_lob.fileisopen (?); end;");

    try
    {
      cstmt.registerOutParameter (1, Types.NUMERIC);
      cstmt.setBFILE (2, bfile);
      cstmt.execute ();
      return cstmt.getBoolean (1);
    }
    finally
    {
      cstmt.close ();
    }
  }

  // Utility function to close a Bfile
  static void fileclose (Connection conn, BFILE bfile)
    throws SQLException
  {
    OracleCallableStatement cstmt =
      (OracleCallableStatement)
        conn.prepareCall ("begin dbms_lob.fileclose (?); end;");

    System.out.println ("Closing bfile.");
    cstmt.setBFILE (1, bfile);
    cstmt.execute ();
    cstmt.close ();
  }
}