java oracle setblob lob字段的问题

Oracle的lob字段处理起来和普通的字段差异很大，具体的来说，增加的时候必须先用“空”值填上，然后查询一下获得lob对象，在把数据填到对象中，再更新回去；读的时候和其他普通值差异不大，不过要用流的方式读出；删除一样；修改的时候则和新增差不多，先select,得到lob对象，流写入后再更新回去。相关的java类型，不能用jdbc自己的，用oracle驱动提供的。
下面具体的看看代码，先准备一张表，

create table blobtable(

CHAPTER_DESCR VARCHAR2(40),

DIAGRAM_NO NUMBER(8),
DIAGRAM BLOB--存放图片
);

同时准备2张图片，做测试用。因为是演示，所以类的写法不够精炼。首先看看增加，下面是辅助方法，获得数据库联接，用的是超级用户
public static Connection getConnection() throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:testdb", "sys as sysdba",
"sys");
return con;
}
--增加的方法，过程是先写入空，再select for update,最后update
public static void insert() throws Exception {

Connection con = getConnection();
con.setAutoCommit(false);//很关键，否则下面的update会抱错
PreparedStatement p = null;
p = con
.prepareStatement("INSERT INTO blobtable (chapter_descr,diagram_no,diagram)values(?,?,?)");
p.setString(1, "testname");
p.setInt(2, 27);
p.setBlob(3, BLOB.empty_lob());//此BLOB乃oracle.sql.BLOB类
p.executeUpdate();
p.close();
p = con
.prepareStatement("select diagram from blobtable where chapter_descr=? for update");
// select for update的好处是先锁住此行，防止并发问题
p.setString(1, "testname");
ResultSet rs = p.executeQuery();
rs.next();
oracle.sql.BLOBblob = (oracle.sql.BLOB) rs.getBlob("diagram");
FileInputStream in = new FileInputStream(
"D:\\My Documents\\testjpg.JPG");
OutputStream outB = blob.getBinaryOutputStream();
byte[] buf = new byte[blob.getBufferSize()];//缓冲的大小用这种方式获得性能最好
int len;
while ((len = in.read(buf)) > 0) {
outB.write(buf, 0, len);
}
outB.close();
in.close();
p.close();
p = con
.prepareStatement("update testname set diagram =? where chapter_descr=?");
p.setBlob(1, blob);
p.setString(2, "testname");
p.executeUpdate();
con.commit();
con.close();

}
--读的方法，比较好懂，注意的blob类型的不能直接查询，只能写到外部文件中
public static void read(String fileName) throws Exception {
Connection con = getConnection();

PreparedStatement p = null;

p = con
.prepareStatement("select diagram from testname where chapter_descr=?");
p.setString(1, "testname");
ResultSet rs = p.executeQuery();
rs.next();
oracle.sql.BLOBblob = (oracle.sql.BLOB) rs.getBlob("diagram");
FileOutputStream out = new FileOutputStream(fileName);
InputStream bos = blob .getBinaryStream();

byte[] buf = new byte[blob.getBufferSize()];
int len;
while ((len = bos.read(buf)) > 0) {
out.write(buf, 0, len);
}
bos.close();
out.close();
p.close();

con.close();

}
--修改，和增加类似，少了以前的insert语句的部分
public static void update() throws Exception {

Connection con = getConnection();
con.setAutoCommit(false);
PreparedStatement p = null;
p = con
.prepareStatement("select diagram from testname where chapter_descr=? for update");
p.setString(1, "testname");
ResultSet rs = p.executeQuery();
rs.next();
oracle.sql.BLOB blob= (oracle.sql.BLOB) rs.getBlob("diagram");
FileInputStream in = new FileInputStream("D:\\My Documents\\forest.jpg");
OutputStream outB = blob.getBinaryOutputStream();
byte[] buf = new byte[blob.getBufferSize()];
int len;
while ((len = in.read(buf)) > 0) {
outB.write(buf, 0, len);
}
outB.close();
in.close();
p.close();
p = con
.prepareStatement("update testname set diagram =? where chapter_descr=?");
p.setBlob(1, blob);
p.setString(2, "testname");
p.executeUpdate();
con.commit();
con.close();

}
写个main方法，调用一下看看结果，
public static void main(String[] args) throws Exception {
insert();
read("d:/test.jpg");
update();
read("d:/test2.jpg");
}
此程序在oracle 10g+jdk1.6上调式通过。

<!--无觅插件 -->