关于Oracle9i中BLOB类型的操作

关于Oracle9i中BLOB类型的操作

因为JDBC2.0没有完全实现对BLOB的操作，所以在读写Oracle中的BLOB类型的数据时必须使用Oracle的扩展包（classes12.jar）。

1．BLOB写操作

1．1 写BLOB的java操作

      ............

        //调用写BLOB的存储过程

       String sql ="{call INSERT_BLOB(?)} ";

        //取得数据库连接

       Connection conn = dao.createCoon();

       CallableStatement proc = conn.prepareCall(sql);

        //设置字节数组

       proc.setBytes(1, blob);

        //执行存储过程

       proc.execute();

        //释放连接

       proc.close();

       conn.close();

      ............

 

1．2 写BLOB的ORACLE存储过程

PROCEDURE INSERT_BLOB

  (BLOBDATA IN BLOB_TABLE_XX%TYPE)   --BLOB

AS

lobd  blob;

BEGIN

  SET TRANSACTION NAME 'insertblob';

  --往BLOB_TABLE_XX表内的BLOB_ROW_XX列插入空的BLOB

  INSERT INTO BLOB_TABLE_XX (BLOB_ROW_XX) VALUES (EMPTY_BLOB());

  --定位刚插入的BLOB

  SELECT SERVICE_IMG INTO lobd FROM BLOB_TABLE_XX WHERE XXX = XXX FOR UPDATE;

  --将内容写进刚插入的BLOB

  dbms_lob.write(lobd, utl_raw.length(BLOBDATA),1, BLOBDATA);

  --事务提交

  COMMIT;

  EXCEPTION

   WHEN OTHERS THEN

      BEGIN

      ROLLBACK;--出错后进行事务回滚

      END;

END;

 

2．BLOB读操作

2．1 读BLOB的ORACLE存储过程

create or replace package body BLOB_PKG_XXX is

 

  TYPE CURSORTYPE IS REF CURSOR;

 

FUNCTION QUERY_BLOB 

   RETURN BLOB_PKG_XXX.CURSORTYPE

AS

  L_CURSOR BLOB_PKG_XXX.CURSORTYPE

BEGIN

  OPEN L_CURSOR FOR SELECT BLOB_ROW_XX

                      FROM BLOB_TABLE_XX

                     WHERE XXX = XXX ;

  RETURN L_CURSOR;

 

END;

 

END BLOB_PKG_XXX;

2．2 读BLOB的java操作

       /**

        * Method getBlobBytes.把BLOB内的内容读出并存入一个字节数组中

        * @param blob

        * @return byte[]

        * @throws SQLException

        * @throws IOException

        */

       public byte[] getBlobBytes(BLOB blob) throws SQLException, IOException {

              //取得BLOB的IO流

BufferedInputStream ins =

                     new BufferedInputStream(blob.getBinaryStream());

        //取得BLOB的长度

              int bufferSize = (int) blob.length();

              //建立字节缓存

byte[] bt = new byte[bufferSize];

//把BLOB内容写入缓存

              ins.read(bt, 0, bufferSize);

              return bt;

       }

 

............

    try {

       String sql =  "{? = call BLOB_PKG_XXX.QUERY_BLOB()} ";

 

       //取得数据库连接

       Connection conn = dao.createCoon();

       CallableStatement proc = conn.prepareCall(sql);

        //设置返回对象类型为结果集

       proc.registerOutParameter(1, OracleTypes.CURSOR);

        //执行存储过程

       proc.execute();

        //获得结果集

       ResultSet rset = (ResultSet) proc.getObject(1);

       while (rset.next()) {

            //获得结果集中的BLOB对象

           BLOB blob = (BLOB) rset.getBlob(BLOB_ROW_XX);

           byte[] bt = dao.getBlobBytes(blob);

       }

//释放连接     

       proc.close();

       conn.close();

    } catch (NamingException e) {

       e.printStackTrace();

    } catch (SQLException e) {

       e.printStackTrace();

    } catch (Exception e) {

       e.printStackTrace();

    }

............