写入数据
1. 向oracle clob字段创建一个空的 empty_clob()对象。insertintoPTR_AssetXml(cChr_AssetPlanId,cChr_AssetData,Create_Date,Submitter,Last_Modified_By,Modified_Date,Status,Short_Description,Request_ID)"
+"values(?,empty_clob(),?,?,?,?,?,?,(selectnextidfromarschemawhereschemaid=3555));
2.查询此条记录并且锁定该记录其它会话不可操作此记录selectcChr_AssetDatafromPTR_AssetXmlwherecChr_AssetPlanId=?forupdate
3.得到该clob字段 的对象并建立输出流 向该对象写入数据conn.setAutoCommit(false);
rs=pre4.executeQuery();
rs.next();
clob=(CLOB)rs.getClob(1);
bw=newBufferedWriter(clob.getCharacterOutputStream());
bw.write(str.toString());
if(bw!=null){
bw.close();
}
mit();
读取数据
1 .查询出此字段并获得此clob字段的对象并建立输入流
try{
pre1=daodb.pre(ConstantTool.sqlsearch_3555_blob);
pre1.setString(1,"PD0924154");
rs=pre1.executeQuery();
CLOBc;
Stringstr;
while(rs.next()){
c=(oracle.sql.CLOB)rs.getClob(1);
bf=newBufferedReader(c.getCharacterStream());
while((str=bf.readLine())!=null){
System.out.println(str);
}
}
}catch(SQLExceptione){
//TODOAuto-generatedcatchblock
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}
2. 更新数据 应将此clob字段更新为空,然后建立连接 输入数据,如果直接更新数据 会从clob字段里开头的值一一向下覆盖。
注意 clob 对象是 oracle.sql.CLOB;
实际应用的一个写入clob 字段值的一个方法publicvoidCreateXmlFile(StringBuilderstr,Stringname){
file=newFile(ConstantTool.filePath+"/"+name+"ws.xml");
logger.info("开始往路径写入数据.."+file.getAbsolutePath());
try{
fos=newFileOutputStream(file);
osw=newOutputStreamWriter(fos,"UTF-8");
osw.write(str.toString());
osw.close();
}catch(FileNotFoundExceptione){
logger.info(e);
e.printStackTrace();
}catch(IOExceptione){
logger.info(e);
e.printStackTrace();
}
//database----------------------------------------------------------
DAODBdb=newDAODB();
Connectionconn=db.conn();
PreparedStatementpre1;
PreparedStatementpre2;
PreparedStatementpre3;
PreparedStatementpre4;
PreparedStatementpre5;
CLOBclob=null;
ResultSetrs=null;
BufferedWriterbw;
inti=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);
//publicstaticfinalStringsqlinsertinto_3555="insert"
//+
//"intoPTR_AssetXml(cChr_AssetPlanId,cChr_AssetData,Create_Date,Submitter,Last_Modified_By,Modified_Date,Status,Short_Description,Request_ID)"
//+
//"values(?,empty_clob(),?,?,?,?,?,?,(selectnextidfromarschemawhereschemaid=3555))";
String[]planstr=name.split("_");
try{
conn.setAutoCommit(false);
pre1.setLong(1,newDate().getTime()/1000);
pre1.setString(2,planstr[0]);
i=pre1.executeUpdate();
mit();
if(i==0){
pre3.executeUpdate();
mit();
pre2.setString(1,planstr[0]);
pre2.setLong(2,(newDate().getTime()/1000));
pre2.setString(3,"同步");
pre2.setString(4,"同步");
pre2.setLong(5,(newDate().getTime()/1000));
pre2.setInt(6,1);
pre2.setString(7,"同步");
pre2.executeUpdate();
mit();
pre4.setString(1,planstr[0]);
conn.setAutoCommit(false);
rs=pre4.executeQuery();
rs.next();
clob=(CLOB)rs.getClob(1);
bw=newBufferedWriter(clob.getCharacterOutputStream());
bw.write(str.toString());
if(bw!=null){
bw.close();
}
mit();
}
if(i!=0){
pre5.setString(1,planstr[0]);
pre5.executeUpdate();
mit();
pre4.setString(1,planstr[0]);
conn.setAutoCommit(false);
rs=pre4.executeQuery();
rs.next();
clob=(CLOB)rs.getClob(1);
bw=newBufferedWriter(clob.getCharacterOutputStream());
bw.write(str.toString());
if(bw!=null){
bw.close();
}
mit();
}
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(SQLExceptione){
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}
}
}
实际应用的读取clob值一个方法
publicStringgetPlanInfoByPlanId(StringplanId){
PreparedStatementpre1=daodb.pre(ConstantTool.sqlsearch_3555_blob);
ResultSetrs;
BufferedReaderbf;
StringBuildersb=newStringBuilder();
CLOBc;
Stringstr;
try{
pre1.setString(1,planId);
rs=pre1.executeQuery();
while(rs.next()){
c=(CLOB)rs.getClob(1);
bf=newBufferedReader(c.getCharacterStream());
while((str=bf.readLine())!=null){
sb.append(str);
}
}
}catch(SQLExceptione){
logger.info(e);
e.printStackTrace();
}catch(IOExceptione){
logger.info(e);
e.printStackTrace();
}
returnsb.toString().replaceAll("
\\$\\$").replaceAll
(">","##");
}