最近一次開發中涉及clob字段的存儲。以前沒使用過，結果發現它不能像普通的varchar2、date等字段那樣存儲。上網查詢了一下，結果發現有些資料不太準確。最后找了個能用的解決。特此寫個demo與大家分享。（PS：測試時用的是Oracle9i）
import java.io.Writer;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.CLOB;
public class Test {
/**
* 更新Clob字段
* @param sourceConn Connection 源數據庫的連接詞
* @param targetConn Connection 目標數據庫的連接詞
*/
public void updateClob(Connection sourceConn, Connection targetConn)
{
ResultSet rsSource = null;
ResultSet oracleRS = null;
Connection connTarget = null; // 目標數據庫的連接
Connection connSource = null; // 源數據庫的連接
PreparedStatement psmtInsert = null;
PreparedStatement psmtSelect = null;
Statement oracleStmt = null;
String sInsertSQL = "INSERT INTO tableTarget(id,context) VALUES (?,empty_clob())";
String sSelectSQL = "SELECT context FROM tableSource WHERE id = ? ";
String sID = "1";
connTarget = targetConn; // 獲取數據庫連接
connSource = sourceConn; //
// 查詢源數據庫中表tableSource的clob字段context
psmtSelect = connSource.prepareStatement(sSelectSQL);
psmtSelect.setString(1, sID);
psmtSelect.executeQuery();
rsSource = psmtSelect.getResultSet();
// 將clob字段插入目標數據庫中的表tableTarget
psmtInsert = connTarget.prepareStatement(sInsertSQL);
connTarget.setAutoCommit(false); // 設置為不即時提交，待會后面一起提交
psmtInsert.setString(1, sID);
psmtInsert.executeUpdate();
// 更新CLOB字段
Clob clobSource = rsSource.getClob("context"); // 源數據庫的Clob字段
String sClob = clobSource.getSubString((long)1, (int)clobSource.length());
CLOB clobTarget = null; // 目標數據庫的字段
oracleStmt = connTarget.createStatement();
oracleRS = oracleStmt.executeQuery("SELECT context FROM tableTarget WHERE id = '"+sID+"'");
// 寫入Clob字段
if(oracleRS.next() && sClob != null)
{
clobTarget = ((OracleResultSet)oracleRS).getCLOB("context");
Writer writer = clobTarget.getCharacterOutputStream() ;
writer.write(sClob);
writer.flush();
writer.close();
connTarget.commit(); // 事務提交
}
}
}