对于Oracle 9i前，要储存数据至Blob/Clob栏位前，JDBC必须先获得Blob/Clob游标，在实际存入数据前，我们必须先建立空的Blob/Clob栏位，然后从空栏位中取得Blob/Clob游标。
您可以使用Oracle的EMPTY_BLOB()与EMPTY_CLOB()来存入空的Blob/Clob栏位，例如：
pstmt = conn.prepareStatement(
"INSERT INTO files(id, des, image) VALUES(?, EMPTY_CLOB(),
EMPTY_BLOB())");
pstmt.setInt(1, 1);
pstmt.executeUpdate();
或者是使用oracle.sql.Blob、oracle.sql.Clob来存入Blob/Clob空栏位，例如：
pstmt = conn.prepareStatement(
"INSERT INTO files(id, des, image) VALUES(?, ?, ?)");
pstmt.setInt(1, 1);
pstmt.setClob(2, CLOB.empty_lob());
pstmt.setBlob(3, BLOB.empty_lob());
pstmt.executeUpdate();
接下来重新查询栏位，取得Blob/Clob栏位游标，然后用更新的方式将Blob/Clob数据更新至数据库，以下是个简单的储存Blob/Clob的程序示范：
package onlyfun.caterpillar;
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
public class Demo {
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:demo";
String user = "caterpillar";
String password = "123456";
try {
Class.forName(driver);
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DriverManager.getConnection(
url, user, password);
conn.setAutoCommit(false);
// 先存入Blob/Clob空栏位
pstmt = conn.prepareStatement(
"INSERT INTO files(id, des, image) " +
"VALUES(?, EMPTY_CLOB(), EMPTY_BLOB())");
pstmt.setInt(1, 1);
pstmt.executeUpdate();
pstmt.close();
// 查询Blob/Clob栏位以取得游标
pstmt= conn.prepareStatement(
"SELECT des, image FROM files where id= ? for update");
pstmt.setInt(1, 1);
ResultSet result = pstmt.executeQuery();
result.next();
CLOB clob = (CLOB) result.getClob(1); // oracle.sql.CLOB
BLOB blob = (BLOB) result.getBlob(2); // oracle.sql.BLOB
clob.putString(1, "...lalalala...");
// 取得文件
File file = new File("c:/workspace/Wind.bmp");
InputStream fin = new FileInputStream(file);
OutputStream os = blob.getBinaryOutputStream();
int len = 0;
byte[] buf = new byte[1024];
while((len = fin.read(buf)) > 0) {
os.write(buf);
}
fin.close();
os.close();
// 用更新的方式新增Blob/Clob数据
pstmt = conn.prepareStatement(
"UPDATE files set des=?, image=? where id = ?");
pstmt.setClob(1, clob);
pstmt.setBlob(2, blob);
pstmt.setInt(3, 1);
pstmt.executeUpdate();
pstmt.close();
conn.commit();
}
catch(SQLException e) {
e.printStackTrace();
}
catch(IOException e) {
e.printStackTrace();
}
finally {
if(pstmt != null) {
try {
pstmt.close();
}
catch(SQLException e) {
e.printStackTrace();
}
}
}
}
catch(ClassNotFoundException e) {
System.out.println("找不到驱动程序");
e.printStackTrace();
}
}
}
|
|