Question

从oracle数据库查询数据生成xml,java解析xml文件的数据批量插入到oracle数据库(用SAX解析)

Answer 1

我自己前段时间写的有事就问
数据库建了一个表study
id student class teacher age 这5个字段
package com.sql.xml;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
//导出数据存入XML
public class ReadDb {
public static void main(String args[]){
//连接数据库
try{
//数据库连接
String url="jdbc:oracle:thin:@10.0.3.18:1521:hxtest18";
String classforname="oracle.jdbc.driver.OracleDriver";
String usename="zhiyong";
String password="password";
Class.forName(classforname);
Connection con=DriverManager.getConnection(url,usename,password);
Statement sta=con.createStatement();
//查询数据
ResultSet result=sta.executeQuery(
"SELECT * FROM study");
//创建根节点
Document document=DocumentHelper.createDocument();
Element studyInfo=DocumentHelper.createElement("studyInfo");
document.setRootElement(studyInfo);
//Element studyInfo=document.addElement("studyInfo");
//当有值时查询
while(result.next()){
//增加每个节点
Element study=studyInfo.addElement("study");
Element id=study.addElement("id");
Element student=study.addElement("student");
Element classe=study.addElement("class");
Element teacher=study.addElement("teacher");
Element age=study.addElement("age");
//将数据库值通过SETTEXT为XML值
id.setText(result.getString("id"));
student.setText(result.getString("student"));
//System.out.println(id.getText());
classe.setText(result.getString("class"));
teacher.setText(result.getString("teacher"));
age.setText(result.getString("age"));
}
//将树导出到文件中:两种输出方法
XMLWriter write=new XMLWriter(new FileOutputStream(new File("studyInfo.xml")));
write.write(document);
write.close();

}catch(Exception e){
e.printStackTrace();
}
}

}

插入数据库,改了几个条件
bookstore.xml文件如下
<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
<book has="100">
<bname>数据库</bname>
<price>33.80元</price>
<date>2009-10-1</date>
</book>
<book has="95">
<bname>三国演义</bname>
<price>40.00元</price>
<date>2005-1-7</date>
</book>
<book has="86">
<bname>西游记</bname>
<price>30.00元</price>
<date>2004-3-8</date>
</book>
<cd have="15">
<id>1</id>
<cname>beyound</cname>
<owner>小黎</owner>
</cd>
<cd have="17">
<id>2</id>
<cname>kugou</cname>
<owner>小强</owner>
</cd>
</bookstore>

解析如下
package com.sql.xml;
import java.io.File;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Iterator;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.DocumentException;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;
import org.dom4j.io.OutputFormat;

//XML的解析与转换后存入数据库
public class Fenkai {
public static void main(String args[]){
String sql,BNAME=null,PRICE=null,DAT=null;//定义数据库表段
String sqlatt,CNAME=null,ID=null,OWNER=null;
//读取xml文件
SAXReader reader=new SAXReader();
File file=new File("bookstore.xml");
try{
//数据库连接
String url="jdbc:oracle:thin:@10.0.3.18:1521:hxtest18";
String classforname="oracle.jdbc.driver.OracleDriver";
String usename="zhiyong";
String password="password";
Class.forName(classforname);
Connection con=DriverManager.getConnection(url,usename,password);
//解析xml文件
Document document=reader.read(file);
Element root=document.getRootElement();//根节点
System.out.println("输出根节点:"+root.getName());
//下面用迭代得到子节点和孙节点
for(Iterator child=root.elementIterator();child.hasNext();){
Element childEle=(Element)child.next();
System.out.println("子："+childEle.getName());
System.out.println("孙：");
for(Iterator grand=childEle.elementIterator();grand.hasNext();){
Element grandEle=(Element)grand.next();
System.out.println(grandEle.getName()+"值:"+grandEle.getText());
//转换数据
while(grandEle.getText().equals("数据库")){
grandEle.setText("001");
}
while(grandEle.getText().equals("三国演义")){
grandEle.setText("002");
}
while(grandEle.getText().equals("西游记")){
grandEle.setText("003");
}
while(grandEle.getText().equals("beyound")){
grandEle.setText("004");
}
while(grandEle.getText().equals("kugou")){
grandEle.setText("005");
}

}}
//得到各节点值插入数据库

for(Iterator it= root.elementIterator("book");it.hasNext();){
Element ch=(Element)it.next();
BNAME=ch.elementText("bname");
PRICE=ch.elementText("price");
DAT=ch.elementText("date");

// while(childEle.getName().equals("cd")){}

sql="insert into book values('"+BNAME+"','" +PRICE
+"','"+DAT+"')";
//sql="select * from book";//加入本语句运行不改变数据表
//执行
con.createStatement().executeUpdate(sql);}
for(Iterator itt= root.elementIterator("cd");itt.hasNext();){
Element cht=(Element)itt.next();
CNAME=cht.elementText("cname");
ID=cht.elementText("id");
OWNER=cht.elementText("owner");
sqlatt="insert into cd values('"+CNAME+"','" +ID
+"','"+OWNER+"')";
//sqlatt="select * from cd";
con.createStatement().executeUpdate(sqlatt);}

//将转换后文件存入txt
OutputFormat format=OutputFormat.createPrettyPrint();
format.setEncoding("gbk");
XMLWriter writer = new XMLWriter( new FileWriter(new File("bookchange.txt")),format);
writer.write(document);
writer.close();
System.out.println(document.asXML());//打印改变后的树
}catch(Exception e){
e.printStackTrace();
System.out.println("有错啊！");
}
}

}