DBA Tips Archive for Oracle

  


Serializing Java Objects to Oracle

by Jeff Hunter, Sr. Database Administrator

Overview

In Java, to "serialize" an object is to either write it to a file, store it in a database or simply send it over the network to another program.

NOTE: The object itself has to be "serializable". The object is saved as a serial stream of bytes with the ability to load it at a later time. In the case of a database, the object can be saved to a BLOB column.

The Java code example (below) Creates an object (a simple Linked List), Serializes it to the database, Reads it out of the database and Prints the value of the object.

A primary key is used for each object loaded into the table: java_objects, as well as its Class Name.


Create Required Database Objects

connect scott/tiger

CREATE SEQUENCE java_obj_seq
       INCREMENT BY 1
       START WITH 1
       NOMAXVALUE
       NOCYCLE
/

CREATE TABLE java_objects (
    obj_id_no   NUMBER
  , obj_name    VARCHAR2(2000)
  , obj_value   BLOB   DEFAULT empty_blob()
)
/


SerialOracle.java

/*
** PROGRAM NAME  : SerialOracle.java
** AUTHOR        : Jeffrey Hunter
** DATE          : 08-NOV-2001
** COMPILE       : javac SerialOracle.java
** RUN           : java SerialOracle
*/

import java.io.*;
import java.lang.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class SerialOracle {

  static final String driver_class = "oracle.jdbc.driver.OracleDriver";
  static final String connectionURL = "jdbc:oracle:thin:@linux1:1521:ORCL1";
  static final String userID = "scott";
  static final String userPassword = "tiger";
  static final String getSequenceSQL = "SELECT java_obj_seq.nextval FROM dual";
  static final String writeObjSQL    = "BEGIN " +
                                       "  INSERT INTO java_objects(obj_id_no, obj_name, obj_value) " +
                                       "  VALUES (?, ?, empty_blob()) " +
                                       "  RETURN obj_value INTO ?; " +
                                       "END;";
  static final String readObjSQL     = "SELECT obj_value FROM java_objects WHERE obj_id_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 {

      /*
      ** LOAD AND REGISTER THE JDBC DRIVER
      ** There are two basic ways to handle this:
      **   - DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      **   - Class.forName ("oracle.jdbc.driver.OracleDriver").newInstance();
      */
      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);

      LinkedList obj = new LinkedList();
      obj.add("This is a test");
      obj.add(new Long(123456789));
      obj.add(new java.util.Date());

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

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

      System.out.print("OBJECT VALUES  => " + readObj(conn, obj_id_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: SerialOracle


Example Output

$ javac SerialOracle.java

$ java SerialOracle

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

Done serializing: java.util.LinkedList
Serialized OBJECT_ID => 1

Done de-serializing: java.util.LinkedList
OBJECT VALUES  => [This is a test, 123456789, Sat May 22 14:35:57 EDT 2004]

Closing down all connections...




Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Sunday, 01-Feb-2004 00:00:00 EST
Page Count: 43267