Oracle 使用 clob 與 blob 插入一些比較龐大的文本或者檔案,JDBC 插入時 也比較簡單
表結構
CREATE TABLE test_info (
user_id int NOT NULL,
user_name varchar(64) NULL,
email varchar(32) NULL,
contentText blob not null
);
java 代碼
/**
* 獲得資料庫連接配接
* @param url
* @param username
* @param passwd
* @return
*/
public static Connection createConnection(String url, String username, String passwd) {
Connection conn = null;
try {
DriverManager.registerDriver(new OracleDriver());
conn = DriverManager.getConnection(url, username, passwd);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 寫入 blob
*/
public static void writeBLOB() {
/** inFile path **/
String inPath = "D:/testFile/xstream/test01.jpg";
/** ========== out ========== **/
String xsoutUrl = ConfigInfo.xsoutUrl;
String xsoutUsername = "testStreamUser";
String xsoutPasswd = "admin";
InputStream input = null;
Connection conn = null;
PreparedStatement pst = null;
int userId = 1;
String sqlInsert = "insert into test_info values (" + userId + ", 2, 3, ?) ";
try {
//輸出流
input = new FileInputStream(inPath);
conn = createConnection(xsoutUrl, xsoutUsername, xsoutPasswd);
conn.setAutoCommit(false);
pst = conn.prepareStatement(sqlInsert);
pst.setBinaryStream(1, input);
//插入資料
pst.executeUpdate();
//commit
pst.execute("COMMIT");
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
input.close();
pst.close();
conn.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}