往oralce中写CLOB的一个例子

阳光穿透心脏的1/2处 2022-04-10 08:28 139阅读 0赞

分三步：  
  
1、插入空blob  
  
2、获得游标，必须加for update，这锁定该行，直至该行被修改完毕，保证不产生并发冲突。  
  
3、用cursor往数据库写数据

try \{  
String strTableName = null;  
String strSql = null;  
String strTableId = null;  
String strFieldName = "";  
String strWhere = "";

// 设置不自动提交  
defaultCommit = conn.getAutoCommit();  
if (defaultCommit == true) \{  
conn.setAutoCommit(false);  
\}

// 获取表名称  
splitSql s = new splitSql(sql);  
strTableName = s.getTableName();

// 获得表中的自动增长列名称  
String strTableIdName = null;  
strSql = "SELECT \* FROM " + strTableName + " WHERE rownum=1";  
PreparedStatement ps2 = conn.prepareStatement(strSql);  
ResultSet rs2 = ps2.executeQuery();  
\{  
ResultSetMetaData rsmd = rs2.getMetaData();  
strTableIdName = rsmd.getColumnName(1);  
rs2.close();  
\}  
// 执行insert sql  
PreparedStatement ps = conn.prepareStatement(sql);  
effectLows = ps.executeUpdate();

// 获得刚刚插入表中的记录序号，并拼接成where条件  
strSql = "SELECT max(" + strTableIdName + ") as MAX\_ID FROM "  
\+ strTableName;  
rs2 = ps2.executeQuery(strSql);  
if (rs2.next())  
strTableId = rs2.getString(1);  
else \{  
// 不应该执行到这里  
m\_logger.error("查询序号最大值失败");  
return -1;  
\}  
strWhere = " WHERE " + strTableIdName + "=" + strTableId;  
rs2.close();  
ps2.close();

// 由于已经将空的CLOB对象插入，所以只需要查询CLOB，并锁定即可  
for (int i = 0; i < bindCount; i++) \{  
strFieldName += bindValue.elementAt(i);  
strFieldName += ",";  
\}  
strFieldName = strFieldName.substring(0, strFieldName.length() - 1);

// 获得CLOB字段  
strSql = "SELECT " + strFieldName + " FROM " + strTableName  
\+ strWhere + " FOR UPDATE";

ResultSet rs = ps.executeQuery(strSql);  
ResultSetMetaData rsmd = rs.getMetaData();  
// int numberOfColumns = rsmd.getColumnCount();  
if (rs.next()) \{  
for (int i = 0; i < bindCount; i++) \{  
String strBuff = SqlTemplateFile.getValue(  
(String) bindValue.elementAt(i), internal);  
if (strBuff == null || strBuff.length() == 0) \{  
strBuff = SqlTemplateFile.getValue((String) bindValue  
.elementAt(i), output);  
if (strBuff == null || strBuff.length() == 0) \{  
strBuff = SqlTemplateFile.getValue(  
(String) bindValue.elementAt(i), input);  
if (strBuff == null || strBuff.length() == 0) \{  
// SqlTemplateFile.m\_errorNumber =  
// qm.SYS\_SYNTAXERROR;  
// SqlTemplateFile.m\_errorInfo = "不能找到绑定变量\[" +  
// strBuff + "\]对应的值" ;  
// m\_logger.error(SqlTemplateFile.m\_errorInfo);  
// rs.close();  
// return -1;  
strBuff = null;  
\}  
\}  
\}

if (strBuff == null)  
ps.setNull(i + 1, java.sql.Types.VARCHAR);  
else \{  
// 取出CLOB对象,获取游标  
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs  
.getClob((String) bindValue.elementAt(i));

// 向CLOB对象中写入数据  
try \{  
OutputStream os = clob.getAsciiOutputStream();  
 byte\[\] b = strBuff.getBytes("ASCII");  
os.write(b);  
 os.flush();  
os.close();  
\} catch (UnsupportedEncodingException e) \{  
rs.close();  
conn.rollback();  
return -1;  
\} catch (Exception e) \{  
rs.close();  
conn.rollback();  
e.printStackTrace();  
return -2;  
\}  
\}  
\}  
\} else \{  
effectLows = 0;  
\}  
rs.close();  
ps.close();  
if (defaultCommit == true) \{  
conn.commit();  
conn.setAutoCommit(true);  
\}  
\} catch (SQLException e) \{  
conn.rollback();  
e.printStackTrace();  
m\_logger.error(e.toString());  
throw e;  
\}