ITEEDU

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