clob儲存為本地xml檔案，修改後上傳，clobxml

clob儲存為本地xml檔案，修改後上傳，clobxml

這兩天與小夥伴寫了一個小程式，實現的功能如下：

首先將資料庫的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; // 這邊需要設定一個空的欄位，後面就不會出現null 指標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 blocke.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 < nodeList.getLength(); i++) {nodeList.item(i).appendChild(document.createTextNode(textNode));}createXml(fileName);}public void deleteElementNode(String fileName, String nodeName) {document = parserXml(fileName);NodeList nodeList = document.getElementsByTagName(nodeName);while (nodeList.getLength() > 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);}

net為何不可以更改儲存xml檔案 在本機測試可以正常建立及儲存xmlDocSave 但將項目上傳至伺服器後 就不行

有很多種情況
第一就是你的許可權不夠 不過如果你在公司工作了很長時間這種許可權問題是不存在的 要不你以前早發現了

還一種情況就是你的XML檔案的路徑問題 看看你設定檔裡面的路徑改了沒 還是你程式裡面代碼設定儲存路徑出錯了
 
window7 xml檔案更改後不可以儲存問題

先把檔案複製到非系統硬碟 修改後copy過去就行了