package blobexample;

import java.sql.*;
import java.io.*;
import weblogic.jdbc.common.*;
import weblogic.jdbc.common.*;
import java.util.Properties;



public class LargeObject{

  public static void main(String argv[])
  {
    String user = "scott";//連接oracle數據庫所用的用戶名
    String password = "tiger";//用戶scott的密碼
    String server = "myoracle";//在本地建立的oracle數據庫網絡服務名


    java.sql.Blob myBlob     = null;
    java.sql.Clob myClob     = null;
    java.sql.Connection conn = null;

    Properties props = new Properties();
    props.put("user",     user);
    props.put("password", password);
    props.put("server",   server);

    try {
      //加載JDBC驅動程序：JDriver for oracle
      Driver myDriver = (Driver)
          Class.forName("weblogic.jdbc.oci.Driver").newInstance();
      //建立數據庫連接
      conn = myDriver.connect("jdbc:weblogic:oracle" , props);
      // 在使用BLOB、CLOB時需要設置連接的事務自動提交屬性為false
      conn.setAutoCommit(false);

      // ============== 建立帶有BLOB和CLOB類型字段的數據表lobtest==================
     try {
          // 如果表lobtest不存在，建立該表
          Statement crstmt = conn.createStatement();
          System.out.println("\nCreating table with Blobs and Clobs...");
          crstmt.execute("create table lobtest (id int, blobcol Blob, clobcol Clob)");
          crstmt.close();
      }
      catch (Exception e) {
          System.out.println("Exception: " + e);
          System.out.println("Table already exists. Dropping it and re-creating...");
          Statement crstmt2 = conn.createStatement();
          crstmt2.execute("drop table lobtest");
          crstmt2.execute("create table lobtest (id int, blobcol Blob, clobcol Clob)");
          crstmt2.close();
      }
      System.out.println("table lobtest created.");


      // ============== 初始化表中的BLOB和CLOB字段 ==================
      Statement stmt = conn.createStatement();
      System.out.println("\nInserting row with blank blob and clob columns...");
      stmt.execute("insert into lobtest values (44,EMPTY_BLOB(),EMPTY_CLOB())");
      System.out.println("Row has been inserted.");

      // ============== 生成BLOB對象myBLOB ======================
      stmt.execute("select * from lobtest where id=44");
      ResultSet rs = stmt.getResultSet();
      while ( rs.next() ) {
          myBlob = rs.getBlob("blobcol");
      }

      // 建立一個byte型數組b，在該數組中存放一些數據
      System.out.println("\nCreating the following byte array:");
      int STREAM_SIZE = 10;
      byte[] b = new byte[STREAM_SIZE];
      for (int i=0; i < STREAM_SIZE; i++) {
          b[i] = (byte)(40 + (i%20)); // range 40-60
          System.out.println("byte[" + i + "] = " + b[i]);
      }

      // 將數組b存放到數據流中，然后將數據流的數據寫入myBlob所代表的BLOB列中
      System.out.println
          ("\nWriting the byte array to a stream" +
           " and storing it in the table as a blob...");
      InputStream is = new ByteArrayInputStream(b);
      java.io.OutputStream os =
          ((weblogic.jdbc.common.OracleBlob) myBlob).getBinaryOutputStream();
      byte[] inBytes = new byte[STREAM_SIZE];
      int numBytes = is.read(inBytes);
      while (numBytes > 0) {
          os.write(inBytes, 0, numBytes);
          numBytes = is.read(inBytes);
      }
      os.flush();

      //  將保存到BLOB列中的數據取出，然后存放到byte數組r中，最后將r中數據顯示出來
      System.out.println("\nReading the blob back from the table and displaying:");
      Statement readblob = conn.createStatement();
      readblob.execute("select * from lobtest where id=44");
      ResultSet rsreadblob = readblob.getResultSet();
      byte[] r = new byte[STREAM_SIZE];
      while ( rsreadblob.next() ) {
          Blob myReadBlob =  rsreadblob.getBlob("blobcol");
          java.io.InputStream readis = myReadBlob.getBinaryStream();
          for (int i=0 ; i < STREAM_SIZE ; i++) {
              r[i] = (byte) readis.read();
              System.out.println("output [" + i + "] = " + r[i]);
          }
      }

      //以下代碼演示的是CLOB的操作
      // 首先建立一個字符串，該字符串中的字符將會存入CLOB列中
      String ss = "abcdefghijklmnopqrstuvwxyz";
      System.out.println("\nCreated the following string to be stored as a clob:\n" +
                         ss);

     //將ss中的字符存入CLOB列中
      stmt.execute("select * from lobtest where id=44");
      ResultSet crs = stmt.getResultSet();
      while ( crs.next() ) {
          myClob = crs.getClob("clobcol");

          java.io.OutputStream osss =
              ((weblogic.jdbc.common.OracleClob) myClob).getAsciiOutputStream();
          byte[] bss = ss.getBytes("ASCII");
          osss.write(bss);
          osss.flush();
      }
      conn.commit();

      // 從CLOB列中讀出數據并顯示出來
      System.out.println("\nReading the clob back from the table and displaying:");
      Statement readclob = conn.createStatement();
      readclob.execute("select * from lobtest where id=44");
      ResultSet rsreadclob = readclob.getResultSet();

        while ( rsreadclob.next() ) {
          Clob myReadClob =rsreadclob.getClob("clobcol");
          java.io.InputStream readClobis = myReadClob.getAsciiStream();
          char[] c = new char[26];
          for (int i=0 ; i < 26  ; i++) {
              c[i] = (char) readClobis.read();
              System.out.println("output [" + i + "] = " + c[i]);
          }
      }

      // 刪除建立的表lobtest
      System.out.println("\nDropping table...");
      Statement dropstmt = conn.createStatement();
      dropstmt.execute("drop table lobtest");
      System.out.println("Table dropped.");

    } catch (Exception e) {
        System.out.println("Exception was thrown: " + e.getMessage());
    } finally {
        try {
          if (conn != null)
            conn.close();
        } catch (SQLException sqle) {
            System.out.println("SQLException was thrown: " + sqle.getMessage());
        }
    }
  }

}