객체직렬화를 통해 직렬화된 객체를 Oracle BLOB에 저장하기

자바의 객체직렬화를 이용하면 직렬화된 객체를 파일, 데이터베이스 또는 원격으로 전송 하는 것이 가능 합니다.

물론 객체직렬화가 되기 위해선 Object는 자바의 Seriablizable 이라는 Interface를 구현해야 합니다. 그래서 이 Object는 Byte Stream 형태로 저장 되어 파일이나 DB에 저장 된 후 나중에 Load되어서 복원이 가능 하게 되는 것입니다.

아래의 예제는 ObjectSerTest 라는 클래스를 직렬화 가능하게 만든 후 오라클의 BLOB에  입력 한 후 읽어내는 예제 입니다.

=====================================================

SQL>conn scott/tiger
SQL> create sequence object_ser_seq increment by 1 start with 1;

주문번호가 생성되었습니다.

SQL> create table object_table (
  2  no number,
  3  obj_name varchar2(2000),
  4  obj_value blob default empty_blob()
  5  );

테이블이 생성되었습니다.

=================================================


/*
* Created on 2005. 2. 11
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package jdbc;

import java.io.*;
import java.sql.*;
import oracle.sql.*;

class ObjectSerTest implements java.io.Serializable{

  static final String driver_class = "oracle.jdbc.driver.OracleDriver";
  static final String connectionURL = "jdbc:oracle:thin:@localhost:1521:wink";
  static final String userID = "scott";
  static final String userPassword = "tiger";
  static final String getSequenceSQL = "SELECT object_ser_seq.nextval FROM dual";
  static final String writeObjSQL    = "BEGIN " +
                                       "  INSERT INTO object_table(no, obj_name, obj_value) " +
                                       "  VALUES (?, ?, empty_blob()) " +
                                       "  RETURN obj_value INTO ?; " +
                                       "END;";
  static final String readObjSQL     = "SELECT obj_value FROM object_table WHERE no = ?";

  /*
   ** +--------------------------------------------------+
   ** | METHOD: writeObj                                 |
   ** +--------------------------------------------------+
  */
  public static long writeObj(Connection conn, Object obj) throws Exception {

    long id = getNextSeqVal(conn);
   
    String className = obj.getClass().getName();
    CallableStatement stmt = conn.prepareCall(writeObjSQL);
   
    stmt.setLong(1, id);
    stmt.setString(2, className);    
    stmt.registerOutParameter(3, java.sql.Types.BLOB);
    stmt.executeUpdate();
   
    BLOB blob = (BLOB) stmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(obj);
    oop.flush();
    oop.close();
    os.close();
    stmt.close();
    System.out.println("Done serializing: " + className);
    return id;

  } // END: writeObj


  /*
   ** +--------------------------------------------------+
   ** | METHOD: readObj                                  |
   ** +--------------------------------------------------+
  */
  public static Object readObj(Connection conn, long id) throws Exception {

    PreparedStatement stmt = conn.prepareStatement(readObjSQL);
    stmt.setLong(1, id);
    ResultSet rs = stmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object obj = oip.readObject();
    String className = obj.getClass().getName();
    oip.close();
    is.close();
    stmt.close();
    System.out.println("Done de-serializing: " + className);
    return obj;

  } // END: readObj

  /*
   ** +--------------------------------------------------+
   ** | METHOD: getNextSeqVal                            |
   ** +--------------------------------------------------+
  */
  private static long getNextSeqVal (Connection conn) throws SQLException {

    Statement stmt = conn.createStatement();
    ResultSet rs   = stmt.executeQuery(getSequenceSQL);
    rs.next();
    long id = rs.getLong(1);
    rs.close();
    stmt.close();
    return id;

  } // END: getNextSeqVal

  /*
   ** +--------------------------------------------------+
   ** | METHOD: main                                     |
   ** +--------------------------------------------------+
  */
  public static void main (String args[]) throws SQLException {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    int insertResults;
    int deleteResults;

    try {
    
      System.out.print("\n");
      System.out.print("Loading JDBC Driver  -> " + driver_class + "\n");
      Class.forName (driver_class).newInstance();

      /*
      ** CONNECT TO THE DATABASE
      */
      System.out.print("Connecting to        -> " + connectionURL + "\n");
      conn = DriverManager.getConnection(connectionURL, userID, userPassword);
      System.out.print("Connected as         -> " + userID + "\n\n");

      /*
      ** TURN OFF AutoCommit
      */
      conn.setAutoCommit(false);

      ObjectSerTest obj = new ObjectSerTest();      

      long no = writeObj(conn, obj);
      conn.commit();

      System.out.print("Serialized OBJECT_ID => " + no + "\n\n");

      System.out.print("OBJECT VALUES  => " + readObj(conn, no) + "\n\n");
      conn.close();

    }  // TRY:

    catch (Exception e) {
      e.printStackTrace();
    }

    finally {
      if (conn != null) {
        try {
          System.out.print("Closing down all connections...\n\n");
          conn.close();
        }
        catch (SQLException e) {
          e.printStackTrace();
        }
      }
    } // FINALLY:

  } // METHOD: main

} // CLASS: ObjectSerTest



[결과]


Loading JDBC Driver  -> oracle.jdbc.driver.OracleDriver
Connecting to        -> jdbc:oracle:thin:@localhost:1521:wink
Connected as         -> scott

Done serializing: jdbc.ObjectSerTest
Serialized OBJECT_ID => 2

Done de-serializing: jdbc.ObjectSerTest
OBJECT VALUES  => jdbc.ObjectSerTest@601bb1

Closing down all connections...