最近一次開發(fā)中涉及clob字段的存儲(chǔ)。以前沒使用過，結(jié)果發(fā)現(xiàn)它不能像普通的varchar2、date等字段那樣存儲(chǔ)。上網(wǎng)查詢了一下，結(jié)果發(fā)現(xiàn)有些資料不太準(zhǔn)確。最后找了個(gè)能用的解決。特此寫個(gè)demo與大家分享。（PS：測試時(shí)用的是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 源數(shù)據(jù)庫的連接詞
* @param targetConn Connection 目標(biāo)數(shù)據(jù)庫的連接詞
*/
public void updateClob(Connection sourceConn, Connection targetConn)
{
ResultSet rsSource = null;
ResultSet oracleRS = null;
Connection connTarget = null; // 目標(biāo)數(shù)據(jù)庫的連接
Connection connSource = null; // 源數(shù)據(jù)庫的連接
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; // 獲取數(shù)據(jù)庫連接
connSource = sourceConn; //
// 查詢源數(shù)據(jù)庫中表tableSource的clob字段context
psmtSelect = connSource.prepareStatement(sSelectSQL);
psmtSelect.setString(1, sID);
psmtSelect.executeQuery();
rsSource = psmtSelect.getResultSet();
// 將clob字段插入目標(biāo)數(shù)據(jù)庫中的表tableTarget
psmtInsert = connTarget.prepareStatement(sInsertSQL);
connTarget.setAutoCommit(false); // 設(shè)置為不即時(shí)提交，待會(huì)后面一起提交
psmtInsert.setString(1, sID);
psmtInsert.executeUpdate();
// 更新CLOB字段
Clob clobSource = rsSource.getClob("context"); // 源數(shù)據(jù)庫的Clob字段
String sClob = clobSource.getSubString((long)1, (int)clobSource.length());
CLOB clobTarget = null; // 目標(biāo)數(shù)據(jù)庫的字段
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(); // 事務(wù)提交
}
}
}