package com.sxt;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.apache.xerces.parsers.DOMParser;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
public class XML2DB {
public static void save(String sql ,Object[] params){
/** test.xml文件
<?xml version="1.0" encoding="gb2312"?>
<employees year="1999">
<employee>
<empid>1111</empid>
<name>zhangsan</name>
<sex>M</sex>
<age>20</age>
<sales>
<sale>
<!--定单号-->
<id>10011</id>
<customer-name>lisi</customer-name>
<money>2000</money>
</sale>
<sale>
<!--定单号-->
<id>10013</id>
<customer-name>lisi</customer-name>
<money>4000</money>
</sale>
</sales>
</employee>
<employee>
<empid>1112</empid>
<name>lisi</name>
<sex>F</sex>
<age>22</age>
<sales>
<sale>
<!--定单号-->
<id>10012</id>
<customer-name>wangmazi</customer-name>
<money>3000</money>
</sale>
<sale>
<!--定单号-->
<id>10014</id>
<customer-name>aaa</customer-name>
<money>3500</money>
</sale>
</sales>
</employee>
</employees>
*/
String driverClass = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "java";
Connection conn = null;
PreparedStatement prst = null;
//1.
try {
Class.forName(driverClass);
//2.
conn = DriverManager.getConnection(url,username,password);
//3.
prst = conn.prepareStatement(sql);
//4.
for(int i = 0 ; i < params.length ; i++){
prst.setObject(i + 1, params[i]);
}
//5.
prst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
//1.解析
DOMParser parser = new DOMParser();
try {
parser.parse("test.xml");
//2.获得根元素
Document doc = parser.getDocument();
Element root = doc.getDocumentElement();
//3.先获得主表的(员工)各列数据标签集合
NodeList ids = root.getElementsByTagName("empid");
NodeList names = root.getElementsByTagName("name");
NodeList sexes = root.getElementsByTagName("sex");
NodeList ages = root.getElementsByTagName("age");
NodeList employees = root.getElementsByTagName("employee");
for(int i = 0 ; i < ids.getLength() ; i++){
//拿到一行数据
String sql = "INSERT INTO employees(id,name,sex,age,year) VALUES(?,?,?,?,?)";
Object[] params = new Object[5];
String str = ((Element)ids.item(i)).getTextContent();
params[0] = new Integer(Integer.parseInt(str));
params[1] = ((Element)names.item(i)).getTextContent();
params[2] = ((Element)sexes.item(i)).getTextContent();
str = ((Element)ages.item(i)).getTextContent();
params[3] = new Integer(Integer.parseInt(str));
params[4] = root.getAttribute("year");
//保存到数据表
save(sql,params);
NodeList sales = ((Element)employees.item(i)).getElementsByTagName("sale");
NodeList idss = ((Element)employees.item(i)).getElementsByTagName("id");
NodeList customer_names = ((Element)employees.item(i)).getElementsByTagName("customer-name");
NodeList money = ((Element)employees.item(i)).getElementsByTagName("money");
for(int j = 0 ; j < sales.getLength() ; j++){
String sql1 = "INSERT INTO sales(id,empId,customer_name,money) VALUES(?,?,?,?)";
Object[] ps = new Object[4];
String str1 = ((Element)idss.item(j)).getTextContent();
ps[0] = new Integer(Integer.parseInt(str1));
ps[1] = params[0];
ps[2] = ((Element)customer_names.item(j)).getTextContent();
str1 = ((Element)money.item(j)).getTextContent();
ps[3] = new Integer(Integer.parseInt(str1));
save(sql1,ps);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
xml文件转换成数据库
最新推荐文章于 2024-03-14 11:33:59 发布