这两天与小伙伴写了一个小程序，实现的功能如下： 首先将数据库的clob保存为本地的xml文件，然后对xml进行修改后上传至数据库 主要的难点如下： 1：clob文件的下载与上传，其中保存为本地的文件要求是UTF-8格式 2：xml文件中节点的修改 clob的上传与下载如下
这两天与小伙伴写了一个小程序，实现的功能如下：
首先将数据库的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);
}
}
}
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 < 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);
}