关于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();
}
............