oracle
Oracle中Blob字段的写入处理（一）

韦  伟

 

Oracle中的Bloc字段的处理较其他字段来说，会有一些特殊性。现就对在java和pl/sql下，Blob字段的处理和大家做一些交流。

下面，简单介绍一下Blob在以下两种环境中的写入处理，

u      java环境

u      PL/SQL环境

 

 

一、java环境

1、 Blob的插入操作

1)       插入一空的Blob。

2)       更新该纪录的Blob。

 

例子：

比如表结构如下：

       CREATE TABLE Student (

              Name VARCHAR2(30),

              Age   Int,

              Picture    Blob);

i.插入一空的Blob。

String command=”INSERT INTO Student VALUES(?,?,?)”;

Connection conn=null;

PreparedStatement ps=null;

try

{

   conn=….;

ps=conn. prepareStatement(command);

ps.setString(1,”ZhangSan”);

ps.setInt(2,20);

ps.setBlob(3, BLOB.empty_lob());

ps.executeUpdate();

…

ii.更新该纪录

 

…

byte[] data=null;

Connection conn=null;

Try

{

  data=…  //图片信息

   conn=..

   ByteArrayInputStream in=new ByteArrayInputStream(data);

  updateBlob(in, conn, “Student”,”Picture”,”Name”,”zhangsan”);

   …

   …

 

 

public static void updateBlob(  InputStream instream,

                          Connection conn,

String table,

                        String blobColumn,

        String keyColumn,

                          String keyValue)   

throws SQLException, IOException

    {

      Statement stmt = null;

      OracleResultSet rs = null;

      BLOB blob = null;

 

      boolean oldAutoCommit = conn.getAutoCommit();

      StringBuffer sqlBuffer = new StringBuffer();

 

      try {

          conn.setAutoCommit(false);

 

          sqlBuffer.append("select ");

          sqlBuffer.append(blobColumn);

          sqlBuffer.append(" from ");

          sqlBuffer.append(table);

          sqlBuffer.append(" where ");

          sqlBuffer.append(keyColumn);

          sqlBuffer.append("='");

          sqlBuffer.append(keyValue);

                 //注意这里的”for update”

          sqlBuffer.append("' for update ");

 

          stmt = conn.createStatement();

          rs = (OracleResultSet) stmt.executeQuery(sqlBuffer.toString());

 

          if (!rs.next())

          {

              rs.close();

              stmt.close();

              throw new IllegalArgumentException(

                  "no record found for keyValue: '" + keyValue + "'");

          }

          blob = rs.getBLOB(1);

          OutputStream outstream = blob.getBinaryOutputStream();

          int bufferSize = blob.getChunkSize();

          byte[] buffer = new byte[bufferSize];

          int bytesRead = -1;

          while ((bytesRead = instream.read(buffer)) != -1)

          {

              outstream.write(buffer, 0, bytesRead);

          }

          instream.close();

          outstream.close();

          rs.close();

          stmt.close();

      }

      catch (SQLException e)

      {

          throw e;

      }

      catch (IOException e)

      {

          throw e;

      }

 

      finally {

          conn.setAutoCommit(oldAutoCommit);

      }

  }

 

二、PL/SQL下

(例子表结构如一所示)

i.插入一空的Blob。

       …

       declare

              bufferBlob BLOB;

              data        RAW(…)

…

       INSERT  INTO Student VALUES(‘zhangsan’, 20 , empty_blob() );

       …

 

ii.更新该纪录的Blob。

       …

       SELECT Picture INTO bufferBlob FROM Student WHERE Name=’zhangsan’ FOR UPDATE;

       DBMS_LOB.OPEN(bufferBlob , dbms_lob.lob_readwrite);

       dbms_lob.write(bufferBlob , utl_raw.length(data) , data);

       …

(待续)