BLOB i Personal Oracle 8.1

Using Java Classes loaded into the database from  Java Applications
--------------------------------------------------------------------


You could have Java Classes loaded into the database using  \"loadjava\"
or you could have them stored into a BLOB column of a user defined table.

The Java classes loaded into the database by any of the above methods could
be used by a Java application by creating a user defined ClassLoader,
similar to the ClassLoader used by browsers for retreiving & executing
Applet classes from a remote system.

Advatages of storing Java classes in the database --
a. Could be used by different clients.
b. The Classes could be secured using database userid & password.

Explained below are the code for using Classes stored in the database using
both of the above methods.

1. Classes stored into a user defined table.

-- Create a table with a BLOB column for storing classes

  SQL>create table java_classes ( classname varchar2(2000), classdata BLOB);

-- For Loading the java classes into the table, create a DIRECTORY object
  to specify the OS directory from where the java classes are to be upload

  SQL>create directory TEMPDIR as \'c:\\temp\';

  Copy the java classes to be uploaded into the above directory (ie c:\\temp)

  Create the procedure mentioned below

  SQL>create or replace procedure upload_java_class (class varchar2)  as
      ablob blob;
      abfile bfile ;  -- Get a pointer to the file.
      amount integer;
      asize integer;
      class_name varchar2(2000);
      cursor1  integer;
      rows_processed integer;
      begin
      cursor1 := dbms_sql.open_cursor;
      dbms_sql.parse(cursor1,\'delete from java_classes where classname = :x\', dbms_sql.v7);
      dbms_sql.bind_variable(cursor1, \'x\', class);
      rows_processed := dbms_sql.execute (cursor1);
      dbms_sql.parse(cursor1,\'insert into java_classes values (:x, empty_blob())\', dbms_sql.v7);
      dbms_sql.bind_variable(cursor1, \'x\', class);
      rows_processed := dbms_sql.execute (cursor1);
      class_name := class || \'.class\';
      abfile:=bfilename(\'TEMPDIR\', class_name);
      dbms_sql.parse(cursor1,\'select classdata from java_classes where classname = :x for update\',dbms_sql.v7);
      dbms_sql.define_column(cursor1,1,ablob);
      dbms_sql.bind_variable(cursor1, \'x\', class);
      rows_processed := dbms_sql.execute (cursor1);
      if dbms_sql.fetch_rows (cursor1) > 0 then
        dbms_sql.column_value (cursor1, 1, ablob);
      end if;
      dbms_sql.close_cursor(cursor1);
      dbms_lob.fileopen(abfile);
      asize := dbms_lob.getlength(abfile);
      dbms_lob.loadfromfile(ablob, abfile, asize);
      asize := dbms_lob.getlength(ablob);
      commit;
      exception
      when others then
        dbms_output.put_line(\'An exception occurred\');
        dbms_output.put_line(sqlcode || sqlerrm);
      end;

    Upload the Class file using the above procedure

    SQL> exec upload_java_class(\'Tester\');-- To Upload Tester.class under c:\\temp
    SQL> select classname from java_classes; -- Should show Tester
   
--- Create a ClassLoader to retreive the BLOB contents for a particular Class.
    ( Attached below is the code for retreving the contents from the
      java_classes table shown above ).
    You need to have the JDBC drivers in the CLASSPATH
    ( $ORACLE_HOME\\jdbc\\lib\\classes111.zip )

    import java.sql.*;
    import oracle.sql.*;
    import oracle.jdbc.driver.*;

    public class DatabaseClassLoader extends ClassLoader {
    private Connection conn;
    private String root;

    public  DatabaseClassLoader (String username, String password, String
    connect_string) {
    try {
      DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(\"jdbc:oracle:oci8:@\"+connect_string,
      username, password); }
      catch(Exception e){System.out.println(\" Error in connecting \"); }
        }

    protected Class loadClass (String name, boolean resolve)
    throws ClassNotFoundException {
    Class c = findLoadedClass (name);
    if (c == null) {
      try {
        c = findSystemClass (name);
      } catch (Exception e) {
      }
    }
    if (c == null) {
      try {
        byte data[] = loadClassData(name);
        c = defineClass (name, data, 0, data.length);
        if (c == null)
          throw new ClassNotFoundException (name);
      } catch (Exception e) {
        throw new ClassNotFoundException (\"Error reading from database \" + e );
      }
    }
    if (resolve)
      resolveClass (c);
    return c;
    }

    private byte[] loadClassData (String filename) throws SQLException
    {
    String cmd=\"select * from java_classes where classname=\'\"+filename+\"\'\";
    Statement stmt= conn.createStatement();
    ResultSet rset = stmt.executeQuery (cmd );
    oracle.sql.BLOB blob = null;
    while(rset.next())
    {
    blob = ((OracleResultSet)rset).getBLOB (2);
    }
    int size = (int)blob.length();
    byte bytes[] = blob.getBytes(1, size);
    stmt.close();
    rset.close();
    return bytes;
    }
    }


--- You could now use the above ClassLoader to load the classes in  your
    Java applications.

    eg : To Use the \"Tester\" class loaded into the scott schema.

    public class CLTester {
    public static void main (String args[]) throws Exception {
    DatabaseClassLoader loader = new DatabaseClassLoader(\"scott\",\"tiger\",
    \"orcl8i\");
    Class c = loader.loadClass (\"Tester\",true);
    // Call the empty construtor of the loaded class
    Object tester = c.newInstance();   
    }
    }



2.  Classes loaded into the Oracle JServer using loadjava.


--- Use the loadjava executable to load the class file

    c:\\temp> loadjava -oci8 -user scott/tiger@orcl8i -resolve Tester.class
    ( To load Tester.class into scott schema).

    Check if the class is loaded  & is valid

    SQL>connect scott/tiger@orcl8i
    SQL>select object_name, status  from user_objects where object_name like \'Tester%\' and
        object_type like  \'JAVA CLASS\';



--- Create a Class Loader for loading the Class

    Create the procedure retreive_class in the database, this would be used by
    the Class Loader to retreive the Class contents

    SQL>create or replace procedure retreive_class(v1 IN varchar2, b1 IN OUT blob)
        as
        begin
        dbms_lob.createtemporary(b1,TRUE);
        dbms_java.export_class(v1, b1);
        end;

    Create the ClassLoader class similar to the above.The only change would be
    in the loadClassdata method.Use the loadClassdata method as given below

    private byte[] loadClassData (String filename) throws SQLException
    {
      OracleCallableStatement stmt = (OracleCallableStatement)conn.prepareCall
      ( \"begin retreive_class(?,?); end;\"  ) ;
      stmt.setString(1, filename);
      stmt.registerOutParameter(2, OracleTypes.BLOB);
      stmt.executeUpdate();
      oracle.sql.BLOB blob= stmt.getBLOB(2);
      int size = (int)blob.length();
      byte bytes[] = blob.getBytes(1, size);
      stmt.close();
      return bytes;
    }