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=http://www.qnr.cn/pc/ora/jsfd/201007/…//图片信息
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);
Oracle应用技术：Oracle中Blob字段的写入处理介绍
</p> //注重这里的”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;
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(…)
…
INSERTINTO 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);
页:
[1]