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