在JDBC中如何Insert/Retrieve BLOB对象呢？
示例代码如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
private static void testWriteReadObjectInBlob() {
try {
// table blob_test : create table blob_test(id number, test blob);
System.out.println("@testWriteReadObjectInBlob:");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String id = "" + 6;
System.out.println("current id : " + id);
String sql1 = "insert into blob_test(id, test) values ([id], empty_blob())".replace("[id]", id);
stmt.executeUpdate(sql1);
String sql2 = "select test from blob_test where id=[id] for update".replace("[id]", id);
ResultSet rs = stmt.executeQuery(sql2);
Object master = any_of_my_class_object;//TODO
ByteArrayOutputStream bos = new ByteArrayOutputStream();
ObjectOutputStream oos = new ObjectOutputStream(bos);
oos.writeObject(master);
byte[] data = bos.toByteArray();
while(rs.next()) {
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("test");
OutputStream outStream = blob.getBinaryOutputStream();
outStream.write(data, 0, data.length);
outStream.flush();
outStream.close();
}
conn.commit();
//read out
String sql3 = "select * from blob_test where id=[id]".replace("[id]", id);
rs = stmt.executeQuery(sql3);
while(rs.next()) {
byte[] byteBuffer = rs.getBytes("test");
ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteBuffer);
ObjectInputStream objectInputStream = new ObjectInputStream(byteArrayInputStream);
Object obj = objectInputStream.readObject();
System.out.println("--------------------");
System.out.println(obj);
System.out.println("--------------------");
System.out.println(obj.getClass().getName());
}
} catch (Exception e) {
e.printStackTrace();
}
}
|
参考链接：
- Inserting BLOB data through JDBC link;
- I get good answer in this SOF post link;
- 洛显臃肿的多种BLOB读值方式：Store Precedure、DBMS_LOB、Oracle Blob：link
- Reading a blob from MySQL link;
另note：
- Oracle中blob类型为blob；
- SqlServer中blob类型为image。