Java用JDBC操作Oracle的CLOB字段也是算最近遇到的一个知识点,也算是基础,在此记录一篇。
因为CLOB类型的特殊性,并不能通过for update进行操作,那么就需要java将要修改的值通过流写入到clob中。
那么更新CLOB之前需要先清空CLOB的值
update xxxx.data_interface_config set SQL_ORDER=empty_clob() where id='79b15cbc011048569e6e64b5bf033dbf'
插入新的CLOB值
Writer outStream = null;
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;
String modifySQL = "CLOB Value";
String updateSQL = "select * from mm_console.data_interface_config where id='79b15cbc011048569e6e64b5bf033dbf' for UPDATE ";
try {
conn = getConnection();
conn.setAutoCommit(autocommit); //关闭自动提交
statement = conn.createStatement();
resultSet = statement.executeQuery(updateSQL);
if (resultSet.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB) resultSet.getClob(columnName);
outStream = clob.getCharacterOutputStream();
char[] c = modifySQL.toCharArray();
outStream.write(c, 0, c.length);
}
outStream.flush();
conn.commit();
System.out.println("commit success!!!");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (outStream != null) {
try {
outStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
closeConnStmtRs(conn, statement, resultSet);
}
如此,就可以对已存在的CLOB字段进行更新操作了。