oracle复制blob字段,Oracle中Blob字段的写入处理（一）_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);

…

(待续)

欢迎大家阅读《Oracle中Blob字段的写入处理(一)_oracle》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码

微信 赏一包辣条吧~

支付宝 赏一听可乐吧~