JAVA中 XML与数据库互转学习笔记三

浏览数：53 / 时间：2015年06月12日

要求

必备知识

JAVA基础知识,XML基础知识,数据库的基本操作。
开发环境

MyEclipse10/MySql5.5
资料下载

源码下载

数据库在数据查询，修改，保存，安全等方面与其他数据处理手段有绝对的优势，而XML文件是基于标记的文本文件，兼容性好，便于组织，解析和交换数据。在某些情况下我们需要讲XML数据导入到数据库中，发挥数据库在管理数据方面的优势；另一方面，我们需要讲数据库中的数据导入到XML文件中，以便与其它系统交互数据，发挥XML文件在数据交换上的优势。下我介绍普通DOM和Dom4J，两种方式实现 XML和数据库互转。

Dom4j实现XML与数据库互转:

英语六级词汇 xml文件下载

数据库Sql代码:

dom4j(如对dom4j不了解可以点击这里)将xml数据导入到数据库中:XMLToDatabase.java

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.Element;


public class XMLToDatabase {

    
    
    
        
        
    
    public static void toDatabase(){
        
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet rs=null;
        connection=JDBCUtilSingle.getInitJDBCUtil().getConnection();
        String sql="INSERT INTO `xmlanddb`.`dict` (`id`, `word`, `meaning`, `lx`) VALUES (NULL, ?, ?, ?);";
        try {
            statement=connection.prepareStatement(sql);
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        
        
        Document document=Dom4j.load("dict.xml");
        Element root=Dom4j.getRootElement(document);
        List words = root.elements("word"); //word节点列表      
        String wordStr="",meaningStr="",lxStr="";
        for (Iterator i = words.iterator(); i.hasNext();) {  
            
                    Element word= (Element) i.next(); //单个word节点
                    
                    for(Iterator k=word.elementIterator();k.hasNext();){  //遍历 name mean lx节点     
                        Element element = (Element) k.next();     
                        if(element.getName().equals("name")){
                            wordStr=element.getText();
                            
                        }
                        if(element.getName().equals("mean")){
                            meaningStr=element.getText();
                        }
                        if(element.getName().equals("lx")){
                            lxStr=element.getText();
                        }      
                    } 
                 
                    try {
                    
                        statement.setString(1,wordStr);
                        statement.setString(2,meaningStr);
                        statement.setString(3,lxStr);
                        statement.executeUpdate();
                        
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
            
           
        }      
        JDBCUtilSingle.getInitJDBCUtil().closeConnection(rs, statement, connection);
    }
    
    
    public static void main(String[] args){
        toDatabase();
    }
    
    
    
    
}

dom4j(如对dom4j不了解可以点击这里)将数据库中记录导入到xml文件中:DatabaseToXML.java

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.XMLWriter;


public class DatabaseToXML {

    
    public static void toXML(){
        
        
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet rs=null;
        connection=JDBCUtilSingle.getInitJDBCUtil().getConnection();
        String sql="SELECT * FROM  `dict`";
        
        
         Document document = DocumentHelper.createDocument();      
         Element root = document.addElement("dict");// 创建根节点     
         
        
        try {
            statement=connection.prepareStatement(sql);
            rs=statement.executeQuery();
            while(rs.next()){
                
                Element word = root.addElement("word");  
                    Element name = word.addElement("name");
                    name.setText(rs.getString("word"));
                    Element mean=word.addElement("mean");  
                        mean.addCDATA(rs.getString("meaning"));
                    Element lx=word.addElement("lx");
                        lx.addCDATA(rs.getString("lx"));
                        

            }
            XMLWriter writer=new XMLWriter(new FileWriter(new File("dict2.xml"))); 
            writer.write(document);
            writer.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        toXML();
    }

}

普通Dom(java中dom解析器不了解可以点击这里)实现XML与数据库互转:

student2.xml

Sql代码:

Dom(java中dom解析器不了解可以点击这里)将xml数据导入到数据库中:XMLToDatabaseWithDom.java

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;




/**
 * 讲XML文件导入到数据库
 * @author licheng
 *
 */
public class XMLToDatabaseWithDom {
    
    
    
    
    /**
     * 讲 学生XML文件导入到数据库中
     */
    public void toDatabase(){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet rs=null;
        connection=JDBCUtilSingle.getInitJDBCUtil().getConnection();
        String sql="INSERT INTO `xmlanddb`.`student` (`number`, `name`, `date`, `height`) VALUES (?,?,?,?);";
        try {    
            statement=connection.prepareStatement(sql);
            DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
            factory.setIgnoringElementContentWhitespace(true);  //忽略空白缩进
            DocumentBuilder domParser=factory.newDocumentBuilder();
            Document document=domParser.parse(new File("student2.xml")); //通过已经存在的文件创建Document对象
            Element root=document.getDocumentElement();
            NodeList list1=root.getElementsByTagName("学号");
            NodeList list2=root.getElementsByTagName("姓名");
            NodeList list3=root.getElementsByTagName("出生日期");
            NodeList list4=root.getElementsByTagName("身高");
            int size=list1.getLength(); //获取长度
            String[] number=new String[4];
            String[] name=new String[4];
            String[] date=new String[4];
            double[] height=new double[4];
            for(int k=0;k<size;k++){
                Node numberNode=list1.item(k);
                Node nameNode=list2.item(k);
                Node dateNode=list3.item(k);
                Node heightNode=list4.item(k);
                number[k]=numberNode.getTextContent().trim();
                name[k]=nameNode.getTextContent().trim();
                date[k]=dateNode.getTextContent().trim();
                height[k]=Double.parseDouble(heightNode.getTextContent().trim());
                statement.setString(1, number[k]);
                statement.setString(2, name[k]);
                statement.setString(3, date[k]);
                statement.setDouble(4, height[k]);
                statement.executeUpdate();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            JDBCUtilSingle.getInitJDBCUtil().closeConnection(rs, statement, connection);
        }
    }
    
    
    
    /**
     * 读取数据库中的数据
     */
    public void getItemFromDatabase(){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet rs=null;
        connection=JDBCUtilSingle.getInitJDBCUtil().getConnection();
        String sql="SELECT * FROM `student` ";
        try {
            statement=connection.prepareStatement(sql);
            rs=statement.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString(1)+rs.getString(2)+rs.getString(3)+rs.getDouble(4));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    
    }
    
    
    public static void main(String[] args){
            //new XMLToDatabase().toDatabase();
            new XMLToDatabaseWithDom().getItemFromDatabase();
    }    
}

Dom(java中dom解析器不了解可以点击这里)将数据库中记录导入到xml文件中:DatabaseToXMLWithDom.java

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
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.Node;



public class DatabaseToXMLWithDom {
    
    
    
    public void toXML(){
        
        
        
        /**
         * 讲数据记录存入到数组中
         */
        String[] number={""};
        String[] name={""};
        String[] date={""};
        String[] height={""};
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet rs=null;
        connection=JDBCUtilSingle.getInitJDBCUtil().getConnection();
        String sql="SELECT * FROM `student` ";
        try {
            statement=connection.prepareStatement(sql);
            rs=statement.executeQuery();
            rs.last(); //讲游标移到结果集的最后一行
            int recordAmount=rs.getRow(); //获取记录数据
            number=new String[recordAmount];
            name=new String[recordAmount];
            date=new String[recordAmount];
            height=new String[recordAmount];
            int k=0;
            rs.beforeFirst(); //讲游标移到第一条记录前
            while(rs.next()){
                number[k]=rs.getString(1);
                name[k]=rs.getString(2);
                date[k]=rs.getString(3);
                height[k]=String.valueOf(rs.getDouble(4));
                k++;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            JDBCUtilSingle.getInitJDBCUtil().closeConnection(rs, statement, connection);
        }
        
        
        
        /**
         * 将数据导入到XML文件
         */
        DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
        factory.setIgnoringElementContentWhitespace(true);  //忽略空白缩进
        DocumentBuilder domParser;
        try {
            domParser = factory.newDocumentBuilder();
            Document document=domParser.newDocument(); //通过调用newDocument() 方法获取实例
            document.setXmlVersion("1.0"); //设置 xml版本号
            Element root=document.createElement("学生信息");
            document.appendChild(root);
            for(int k=0;k<name.length;k++){
                Node student=document.createElement("学生");
                root.appendChild(student);
                
                    Node xuehao=document.createElement("学号");
                    xuehao.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 shenggao=document.createElement("身高");
                    shenggao.appendChild(document.createTextNode(height[k]));
                    
                    student.appendChild(xuehao);
                    student.appendChild(xingming);
                    student.appendChild(chusheng);
                    student.appendChild(shenggao);
                    
                    
                    
                    TransformerFactory transFactory=TransformerFactory.newInstance(); //工厂对象获取transFactory实例
                    Transformer transformer=transFactory.newTransformer(); //获取Transformer实例
                    DOMSource domSource=new DOMSource(document);
                    File file=new File("newXML2.xml");
                    FileOutputStream out=new FileOutputStream(file);
                    StreamResult xmlResult=new StreamResult(out);
                    transformer.transform(domSource, xmlResult);
                    out.close();
                    
                
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        
        
        
        
    
    }
    

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        new DatabaseToXMLWithDom().toXML();
    }

}