写入数据
1. 向oracle clob字段创建一个空的 empty_clob()对象。
insert into PTR_AssetXml(cChr_AssetPlanId,cChr_AssetData,Create_Date,Submitter,Last_Modified_By,Modified_Date,Status,Short_Description,Request_ID) " + "values(?,empty_clob(),?,?,?,?,?,?,(select nextid from arschema where schemaid=3555));
2.查询此条记录并且锁定该记录其它会话不可操作此记录
select cChr_AssetData from PTR_AssetXml where cChr_AssetPlanId=? for update
3.得到该clob字段 的对象并建立输出流 向该对象写入数据
conn.setAutoCommit(false);
rs = pre4.executeQuery();
rs.next();
clob = (CLOB) rs.getClob(1);
bw = new BufferedWriter(clob.getCharacterOutputStream());
bw.write(str.toString());
if (bw != null) {
bw.close();
}
conn.commit();
读取数据
1 .查询出此字段并获得此clob字段的对象并建立输入流
-
try { pre1 = daodb.pre(ConstantTool.sqlsearch_3555_blob); pre1.setString(1, "PD20140924154"); rs = pre1.executeQuery(); CLOB c; String str; while (rs.next()) { c = (oracle.sql.CLOB) rs.getClob(1); bf = new BufferedReader(c.getCharacterStream()); while ((str = bf.readLine()) != null) { System.out.println(str); } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); }
2. 更新数据 应将此clob字段更新为空，然后建立连接 输入数据，如果直接更新数据 会从clob字段里开头的值一一向下覆盖。
注意 clob 对象是 oracle.sql.CLOB;
实际应用的一个写入clob 字段值的一个方法
public void CreateXmlFile(StringBuilder str, String name) {
file = new File(ConstantTool.filePath + "/" + name + "ws.xml");
logger.info("开始往路径写入数据.." + file.getAbsolutePath());
try {
fos = new FileOutputStream(file);
osw = new OutputStreamWriter(fos, "UTF-8");
osw.write(str.toString());
osw.close();
} catch (FileNotFoundException e) {
logger.info(e);
e.printStackTrace();
} catch (IOException e) {
logger.info(e);
e.printStackTrace();
}
// database----------------------------------------------------------
DAODB db = new DAODB();
Connection conn = db.conn();
PreparedStatement pre1;
PreparedStatement pre2;
PreparedStatement pre3;
PreparedStatement pre4;
PreparedStatement pre5;
CLOB clob = null;
ResultSet rs = null;
BufferedWriter bw;
int i = 0;
pre1 = db.pre(ConstantTool.sqlsearch_3555_update_key);
pre2 = db.pre(ConstantTool.sqlinsertinto_3555);
pre3 = db.pre(ConstantTool.sqlupdateid_3555);
pre4 = db.pre(ConstantTool.sqlselect_3555_blob);
pre5 = db.pre(ConstantTool.sqlsearch_3555_update_clob_empty);
// public static final String sqlinsertinto_3555 = "insert "
// +
// " into PTR_AssetXml(cChr_AssetPlanId,cChr_AssetData,Create_Date,Submitter,Last_Modified_By,Modified_Date,Status,Short_Description,Request_ID) "
// +
// "values(?,empty_clob(),?,?,?,?,?,?,(select nextid from arschema where schemaid=3555))";
String[] planstr = name.split("_");
try {
conn.setAutoCommit(false);
pre1.setLong(1, new Date().getTime() / 1000);
pre1.setString(2, planstr[0]);
i = pre1.executeUpdate();
conn.commit();
if (i == 0) {
pre3.executeUpdate();
conn.commit();
pre2.setString(1, planstr[0]);
pre2.setLong(2, (new Date().getTime() / 1000));
pre2.setString(3, "同步");
pre2.setString(4, "同步");
pre2.setLong(5, (new Date().getTime() / 1000));
pre2.setInt(6, 1);
pre2.setString(7, "同步");
pre2.executeUpdate();
conn.commit();
pre4.setString(1, planstr[0]);
conn.setAutoCommit(false);
rs = pre4.executeQuery();
rs.next();
clob = (CLOB) rs.getClob(1);
bw = new BufferedWriter(clob.getCharacterOutputStream());
bw.write(str.toString());
if (bw != null) {
bw.close();
}
conn.commit();
}
if (i != 0) {
pre5.setString(1, planstr[0]);
pre5.executeUpdate();
conn.commit();
pre4.setString(1, planstr[0]);
conn.setAutoCommit(false);
rs = pre4.executeQuery();
rs.next();
clob = (CLOB) rs.getClob(1);
bw = new BufferedWriter(clob.getCharacterOutputStream());
bw.write(str.toString());
if (bw != null) {
bw.close();
}
conn.commit();
}
if(pre1!=null){
pre1.close();
}
if(pre2!=null){
pre1.close();
}
if(pre3!=null){
pre1.close();
}
if(pre4!=null){
pre1.close();
}
if(pre5!=null){
pre1.close();
}
db.closeConn();
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
实际应用的读取clob值一个方法
public String getPlanInfoByPlanId(String planId) {
PreparedStatement pre1 = daodb.pre(ConstantTool.sqlsearch_3555_blob);
ResultSet rs;
BufferedReader bf;
StringBuilder sb = new StringBuilder();
CLOB c;
String str;
try {
pre1.setString(1, planId);
rs = pre1.executeQuery();
while (rs.next()) {
c = (CLOB) rs.getClob(1);
bf = new BufferedReader(c.getCharacterStream());
while ((str = bf.readLine()) != null) {
sb.append(str);
}
}
} catch (SQLException e) {
logger.info(e);
e.printStackTrace();
} catch (IOException e) {
logger.info(e);
e.printStackTrace();
}
return sb.toString().replaceAll("<", "
\\$\\$").replaceAll
(">", "##");
}