解析数据库生成XML文件及解析XML文件插入数据库-优快云博客

本文介绍了一种使用Java程序实现XML文件与Oracle数据库之间的数据交互的方法。具体包括从数据库导出数据到XML文件，以及读取XML文件并将数据插入到数据库的过程。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
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("有错啊！");
		   }
	}

}