最近一次开发中涉及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(); // 事务提交
}
}
}