XML与数据库的数据转换

实验三：XML 与数据库的数据转换1实验学时 2 学时2实验目的理解 XML 与数据库之间的转换方式在项目实践中综合各种知识的运用3实验内容采用 Eclipse IDE(或 MyEclipse) 建立一个 Java 项目利用 MySQL 及其图形界面工具建立一个数据库利用 JDBC 建立其和数据库的连接编写 XML 文件和处理类以完成 XML 文件和数据库之间的数据转换4实验代码import javax.xml.transform.*;import javax.xml.transform.stream.*;import javax.xml.transform.dom.*;import org.w3c.dom.*;import javax.xml.parsers.*;import java.io.*;import java.sql.*;public class DatabaseToXML {// MySQL的JDBC连接jar包位置是D:\mysql-connector-java-5.1.19-bin.jar// 数据库名称为：xmllab// 表名是：person，表中的字段及属性请查阅ppt文档// 用户名是：root// 密码是：rootpublic static void main(String args[]) {Connection con;Statement sql;ResultSet rs;// 为ppt文档中的图示中的表增加一个字段id，用于MySQL自增量计算Integer[] id = {};String[] number = { "" };String[] name = { "" };String[] date = { "" };String[] salary = { "" };try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {System.out.println("" + e);}try {con = DriverManager.getConnection("jdbc:mysql://localhost/xmllab?"+ "user=root&password=");sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);rs = sql.executeQuery("SELECT * FROM person");st();int recordAmount = rs.getRow();id = new Integer[recordAmount];number = new String[recordAmount];name = new String[recordAmount];date = new String[recordAmount];salary = new String[recordAmount];int k = 0;rs.beforeFirst();while (rs.next()) {id[k] = rs.getInt(1);number[k] = rs.getString(2);name[k] = rs.getString(3);date[k] = rs.getDate(4).toString();salary[k] = String.valueOf(rs.getDouble(5));k++;}con.close();} catch (SQLException e) {System.out.println(e);}try {DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();DocumentBuilder domPaser = factory.newDocumentBuilder();Document document = domPaser.newDocument();document.setXmlVersion("1.0");Element root = document.createElement("雇员列表");document.appendChild(root);for (int k = 0; k < name.length; k++) {Node employee = document.createElement("雇员");root.appendChild(employee);Node idhao = document.createElement("序号");idhao.appendChild(document.createTextNode(number[k]));Node guyuanhao = document.createElement("雇员号");guyuanhao.appendChild(document.createTextNode(number[k]));Node xingming = document.createElement("姓名");xingming.appendChild(document.createTextNode(name[k]));Node chusheng = document.createElement("出生日期");chusheng.appendChild(document.createTextNode(date[k]));Node pay = document.createElement("薪水");pay.appendChild(document.createTextNode(salary[k]));employee.appendChild(idhao);employee.appendChild(guyuanhao);employee.appendChild(xingming);employee.appendChild(chusheng);employee.appendChild(pay);}TransformerFactory transFactory = TransformerFactory.newInstance();Transformer transformer = transFactory.newTransformer();DOMSource domSource = new DOMSource(document);File f = new File("newXML.xml");FileOutputStream out = new FileOutputStream(f);StreamResult xmlResult = new StreamResult(out);transformer.transform(domSource, xmlResult);System.out.println("run success!");out.close();} catch (Exception e) {System.out.println(e);}}}import javax.xml.xpath.*;import org.xml.sax.*;import org.w3c.dom.*;import java.sql.*;public class XMLToDatabase {public static void main(String args[]) {Connection con = null;Statement sql = null;ResultSet rs = null;try {Class.forName("com.mysql.jdbc.Driver");} catch (Exception e) {System.out.println("" + e);}try {con = DriverManager.getConnection("jdbc:mysql://localhost/xmllab?"+ "user=root&password");sql = con.createStatement();XPathFactory xPathFactory = XPathFactory.newInstance();XPath xPath = xPathFactory.newXPath();InputSource source = new InputSource("example9_2.xml");String path = "/雇员列表/雇员";NodeList nodeList = (NodeList) xPath.evaluate(path, source, XPathConstants.NODESET);int size = nodeList.getLength();for (int i = 0; i < size; i++) {int m = i + 1;path = "/雇员列表/雇员[" + m + "]/*/text()";nodeList = (NodeList) xPath.evaluate(path, source,XPathConstants.NODESET);int length = nodeList.getLength();String[] a = new String[length];for (int k = 0; k < length; k++) {Node node = nodeList.item(k);a[k] = node.getTextContent().trim();}String insertData = "INSERT INTO personxml2db VALUES('" + a[0]+ "','" + a[1] + "','" + a[2] + "'," + a[3] + ")";sql.executeUpdate(insertData);}System.out.println("run success!");con.close();} catch (Exception exp) {System.out.println(exp);}}}<?xml version="1.0" encoding="UTF-8"?><雇员列表><雇员><雇员号>A1006</雇员号><姓名>王经路</姓名><出生日期>1995-11-11</出生日期><薪水>6789</薪水></雇员><雇员><雇员号>A1007</雇员号><姓名>赵懂知</姓名><出生日期>1995-06-28</出生日期> <薪水>5673</薪水></雇员></雇员列表>。