From Gossip@Openhome

Java Gossip: 將檔案存入資料庫 - Oracle

對於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的程式示範:

  • BlobClobDemo.java
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();
}
}
}