这两天与小伙伴写了一个小程序，实现的功能如下：
首先将数据库的clob保存为本地的xml文件，然后对xml进行修改后上传至数据库
主要的难点如下：
1：clob文件的下载与上传，其中保存为本地的文件要求是utf-8格式
2：xml文件中节点的修改
clob的上传与下载如下
import java.io.bufferedreader;import java.io.fileinputstream;import java.io.filenotfoundexception;import java.io.fileoutputstream;import java.io.ioexception;import java.io.inputstreamreader;import java.io.outputstreamwriter;import java.io.reader;import java.io.unsupportedencodingexception;import java.sql.clob;import java.sql.connection;import java.sql.preparedstatement;import java.sql.resultset;import java.sql.sqlexception;import java.sql.statement;/** * @author guodi-ct dc * */public class clobmodify { /** *@param id 数据库中的id * 返回 保存文件的绝对路径 */ public static string clobtoxml(int id) { connection conn = db.getconn(); statement stmt = db.createstmt(conn); string sql = select * from bd_process_def_ver where id = + id; resultset rs = db.getrs(stmt, sql); string xmlfile = null; try { if (rs.next()) { int fjbh = rs.getint(1); xmlfile = d:\\xml\\ + fjbh + .xml; clob clob = rs.getclob(16); fileoutputstream fo = new fileoutputstream(xmlfile); outputstreamwriter so = new outputstreamwriter(fo, utf-8); if (clob != null) { reader is = clob.getcharacterstream(); bufferedreader br = new bufferedreader(is); string s = br.readline(); while (s != null) { so.write(s + system.getproperty(line.separator));// system.out.println(str); s = br.readline(); } } so.flush(); so.close(); } } catch (sqlexception | ioexception e) { e.printstacktrace(); } db.close(rs); db.close(stmt); db.close(conn); return xmlfile; } public static void updateclob(string filename, int id) { fileinputstream fis = null; inputstreamreader rd = null; try { fis = new fileinputstream(filename); rd = new inputstreamreader(fis, utf-8); } catch (filenotfoundexception e2) { e2.printstacktrace(); } catch (unsupportedencodingexception e) { e.printstacktrace(); } preparedstatement pst = null; connection conn = db.getconn(); statement stmt = db.createstmt(conn); try { conn.setautocommit(false); } catch (sqlexception e1) { e1.printstacktrace(); } string sql1 = update bd_process_def_ver s set s.node_info=' ' where id= + id; // 这边需要设置一个空的字段，后面就不会出现空指针 try { stmt.execute(sql1); } catch (sqlexception e) { e.printstacktrace(); } string sql2 = select * from bd_process_def_ver s where s.id= + id; // 锁定数据行进行更新，注意“for update”语句 resultset rs = null; try { rs = stmt.executequery(sql2); } catch (sqlexception e) { e.printstacktrace(); } try { while (rs.next()) { string sql3 = update bd_process_def_ver set node_info= ? where id=+ id; pst = conn.preparestatement(sql3); pst.setcharacterstream(1, rd, 100000000); pst.executeupdate(); } // 最后一步自己提交 conn.commit(); conn.setautocommit(true); } catch (sqlexception e) { e.printstacktrace(); } finally { db.close(rs); db.close(stmt); try { pst.close(); } catch (sqlexception e) { e.printstacktrace(); } db.close(conn); } }}
其中db是连接数据库的javabean，如下
import java.sql.connection;import java.sql.drivermanager;import java.sql.resultset;import java.sql.sqlexception;import java.sql.statement;/** * @author guodi-ct dc * jdcbc javabean * */public class db { // 驱动程序就是之前在classpath中配置的jdbc的驱动程序的jar 包中 public static final string dbdriver = oracle.jdbc.driver.oracledriver; // 连接地址是由各个数据库生产商单独提供的，所以需要单独记住 public static final string dburl = jdbc:oracle:thin:@172.17.20.215:1521:bpmide; // 连接数据库的用户名 public static final string dbuser = bpmduser; // 连接数据库的密码 public static final string dbpass = bpmd; public static connection getconn() { connection conn = null; try { class.forname(dbdriver); conn = drivermanager.getconnection(dburl, dbuser, dbpass); // 2、连接数据库 } catch (classnotfoundexception e) { e.printstacktrace(); } // 1、使用class 类加载驱动程序 catch (sqlexception e) { e.printstacktrace(); } return conn; } public static statement createstmt(connection conn) { statement stmt = null; try { stmt = conn.createstatement(); } catch (sqlexception e) { e.printstacktrace(); } return stmt; } public static resultset getrs(statement stmt, string sql) { resultset rs = null; try { rs = stmt.executequery(sql); } catch (sqlexception e) { e.printstacktrace(); } return rs; } public static void close(resultset rs) { if (rs != null) { try { rs.close(); } catch (sqlexception e) { e.printstacktrace(); } finally { rs = null; } } } public static void close(statement stmt) { if (stmt != null) { try { stmt.close(); } catch (sqlexception e) { e.printstacktrace(); } finally { stmt = null; } } } public static void close(connection conn) { if (conn != null) { try { conn.close(); } catch (sqlexception e) { // todo auto-generated catch block e.printstacktrace(); } finally { conn = null; } } }}
xml的修改程序如下
import java.io.filenotfoundexception;import java.io.fileoutputstream;import java.io.ioexception;import javax.xml.parsers.documentbuilder;import javax.xml.parsers.documentbuilderfactory;import javax.xml.parsers.parserconfigurationexception;import javax.xml.transform.outputkeys;import javax.xml.transform.transformer;import javax.xml.transform.transformerfactory;import javax.xml.transform.dom.domsource;import javax.xml.transform.stream.streamresult;import org.w3c.dom.document;import org.w3c.dom.element;import org.w3c.dom.nodelist;import org.xml.sax.saxexception;/** * * @author zhangwen.ctdc dom更新与解析xml文档 */public class xmlanalysis /* implements xmldocumentinterface */{ private document document; public void init() { try { documentbuilderfactory factory = documentbuilderfactory .newinstance(); documentbuilder builder = factory.newdocumentbuilder(); this.document = builder.newdocument(); } catch (parserconfigurationexception e) { system.out.println(e.getmessage()); } } public void insertelementnode(string filename, string nodename, string newelementname) { document = parserxml(filename); nodelist nodelist = document.getelementsbytagname(nodename); for (int i = 0; i < nodelist.getlength(); i++) { element element = document.createelement(newelementname); nodelist.item(i).appendchild(element); } createxml(filename); } public void insertattrnode(string filename, string nodename, string newattrname, string attrvalue) { document = parserxml(filename); nodelist nodelist = document.getelementsbytagname(nodename); for (int i = 0; i < nodelist.getlength(); i++) { element element = (element) (nodelist.item(i)); element.setattribute(newattrname, attrvalue); } createxml(filename); } public void inserttextnode(string filename, string nodename, string textnode) { document = parserxml(filename); nodelist nodelist = document.getelementsbytagname(nodename); for (int i = 0; i 0) { nodelist.item(0).getparentnode().removechild(nodelist.item(0)); nodelist = document.getelementsbytagname(nodename); } createxml(filename); } public void updatenode(string filename, string nodename, string attrname, string newattrvalue) { document = parserxml(filename); nodelist nodelist = document.getelementsbytagname(nodename); for (int i = 0; i < nodelist.getlength(); i++) { element node = (element) nodelist.item(i); node.setattribute(attrname, newattrvalue); } createxml(filename); } private document parserxml(string filename) { try { documentbuilderfactory dbf = documentbuilderfactory.newinstance(); documentbuilder db = dbf.newdocumentbuilder(); document document = db.parse(filename); system.out.println(----------------------------------- + 解析完毕 + ----------------------------------------); return document; } catch (filenotfoundexception e) { system.out.println(e.getmessage()); } catch (parserconfigurationexception e) { system.out.println(e.getmessage()); } catch (saxexception e) { system.out.println(e.getmessage()); } catch (ioexception e) { system.out.println(e.getmessage()); } return document; } private void createxml(string filename) { try { /** 将document中的内容写入文件中 */ transformerfactory tfactory = transformerfactory.newinstance(); transformer transformer = tfactory.newtransformer(); transformer.setoutputproperty(outputkeys.encoding, utf-8); transformer.setoutputproperty(outputkeys.indent, yes); domsource source = new domsource(document); streamresult result = new streamresult(new fileoutputstream( filename)); transformer.transform(source, result); system.out.println(-------------------------------- + 更新 xml文件成功 + -------------------------------------); } catch (exception exception) { system.out.println(更新 + filename + 出错： + exception); exception.printstacktrace(); } }}
最后程序提供的接口与说明如下
/** * @author guodi and zhangwen * */public interface nodeinfointerface { /** * xml文档 插元素入节点 * @param time 时间 * @param nodename 标签名 * @param newelementname 新标签 */ public void insertelementnode(string time, string nodename,string newelementname); /** * @param time 时间 * @param nodename 标签名 * @param newattrname 新属性名 * @param attrvalue 新属性值 * xml文档 插入属性节点 */ public void insertattrnode(string time,string nodename,string newattrname,string attrvalue); /** * @param time 时间 * @param nodename 标签名 * @param textnode 文本 * xml文档 插入文本节点 */ public void inserttextnode(string time,string nodename,string textnode); /** * @param time 时间 * @param nodename 标签名 * xml文档 删除所有对应元素节点 */ public void deleteelementnode(string time,string nodename); /** * @param time 时间 * @param nodename 标签名 * @param newattrname 新属性名 * @param attrvalue 新属性值 * xml文档 修改属性节点内容 */ public void updatenode(string time,string nodename,string newattrname,string attrvalue); }