xml与数据库中数据的导入导出

December 17, 2023
测试
测试
测试
测试
41 分钟阅读

这是我一个晚上做出来的，因为要去做其他的项目，所以只实现了对特定数据库的xml操作，不过我觉得这是学习xml挺不错的参考代码和文档

使用说明：

要先导入xml.sql数据库，可以用navicat导入，然后运行java项目就可以，这是java+mysql数据库实现的程序，仅供参考互相学习

实验前准备:

新建一个Java工程，工程名称为xmlDemo,文件目录如图所示：

src

frame包：存放java的界面类。IndexFrame是索引界面类，ImportFrame是导入界面类，ExportFrame是导出界面类；

service包：存放java的Service类。DBService是实现数据库操作的Service类，DBToXmlService是实现从数据库导出xml文件的Service类，XmlToDBService是实现从xml文件导入数据库的Service类；

utils包：存放java的工具类。DBConnectionUtil是数据库连接的工具类；

libs

dom4j-1.6.1.jar：实现XML读取相关操作的价包；

mysql-connector-5.1.8.jar：实现连接MySql数据库的价包；

IndexFrame.java：

package com.xmlDemo.frame;

import java.awt.Color;
import java.awt.Dimension;
import java.awt.Image;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JFrame;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;


/**
 *项目名称:xml读取转换工具
 *类名:IndexJFrame
 *类描述:主界面类
 *创建人:马增群
 *修改备注:
 *@version 1.0.0
 */
public class IndexFrame extends JFrame{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private JMenuBar menuBar=null;
		
	private JMenu fileMenu=null;
	private JMenu helpMenu=null;
		
	private JMenuItem existMenuItem=null;
	private JMenuItem importMenuItem=null;
	private JMenuItem exportMenuItem=null;
	
	private JMenuItem about=null;
	private JMenuItem contact=null;
	private JMenuItem introduce=null;
	
	private final static String BASEURL="../xmlDemo/images/";
	
	//构造函数，用于初始
	
	
	private String arrs2[];
	
	
	public static void main(String[] args) {
		new IndexFrame();
	}
	
	public IndexFrame(){

		setTitle("xml转换工具");
		Image image=Toolkit.getDefaultToolkit().getImage(BASEURL+"logo.png");
		setIconImage(image);
		
		setLocationRelativeTo(null);

		createMenuBar();
	
		/**/
		
		setJMenuBar(menuBar);
		//getContentPane().add("Center",splitPane);
			
		//设置JFrame的属性
		setResizable(false);//设置不可以改变大小
		pack();//自动调整
		setSize(400,600);
		  //setSize(bg.getIconWidth(), bg.getIconHeight());
		
		//设置运行时窗口的位置
				Dimension screenSize = Toolkit.getDefaultToolkit().getScreenSize();
				Dimension frameSize = getSize();
				if (frameSize.height > screenSize.height) {
					frameSize.height = screenSize.height;
				}
				if (frameSize.width > screenSize.width) {
					frameSize.width = screenSize.width;
				}
		setLocation((screenSize.width - frameSize.width) / 2, (screenSize.height - frameSize.height) / 2);
		setVisible(true);
	}
	
	
	/**
	 * 方法说明:创建菜单栏
	 */
	 public void createMenuBar(){
			
			menuBar=new JMenuBar();
	        menuBar.setBackground(new Color(197,228,251));
			  
	        fileMenu = new JMenu("文件");
			helpMenu=new JMenu("帮助");
			
			//ImageIcon conImage=new ImageIcon(BASEURL+"contact.png");
			contact=new JMenuItem("联系");
			about=new JMenuItem("关于");
			introduce=new JMenuItem("说明");
			
			exportMenuItem = new JMenuItem("xml导出");
			
			exportMenuItem.addActionListener(new ActionListener() {
				
				@Override
				public void actionPerformed(ActionEvent e) {
					// TODO Auto-generated method stub
					try {
						new ExportFrame();
					} catch (Exception e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				}
			});
			
			importMenuItem = new JMenuItem("xml导入");
			
			importMenuItem.addActionListener(new ActionListener() {
				
				@Override
				public void actionPerformed(ActionEvent e) {
					// TODO Auto-generated method stub
					new ImportFrame();
				}
			});
			existMenuItem = new JMenuItem("退出软件");
			
			helpMenu.add(contact);
			helpMenu.add(about);
			helpMenu.add(introduce);
			
			fileMenu.add(exportMenuItem);
			fileMenu.add(importMenuItem);
			fileMenu.add(existMenuItem);
			
			menuBar.add(fileMenu);
			menuBar.add(helpMenu);
	}
	 
	
}

ImportFrame.java： 

package com.xmlDemo.frame;

import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.Image;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;

import com.xmlDemo.service.DBService;
import com.xmlDemo.service.XmlToDBService;

public class ImportFrame extends JFrame implements ItemListener{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private String filePath;
	
	private final static String BASEURL="../xmlDemo/images/";
	
	private JComboBox comboBox;
	
	private List<String> list;
	
	private String[] arrs = {};
	
	
	private String dbName ="xml";
	
	public ImportFrame(){
		 JFileChooser fileChooser=new JFileChooser("打开文件");
         int isOpen=fileChooser.showOpenDialog(null);
         fileChooser.setDialogTitle("打开文件");
         if(isOpen==JFileChooser.APPROVE_OPTION){
         	filePath = fileChooser.getSelectedFile().getPath();
         	
         	//final JDialog dialog=new JDialog();
         	Image image=Toolkit.getDefaultToolkit().getImage(BASEURL+"logo.png");
     		setIconImage(image);
     		setTitle("导入信息");
             
         	JPanel p1=new JPanel();
            JPanel p2=new JPanel();
             
         	
             JTextArea textArea=new JTextArea(60,60);
         	textArea.setText(readFromFile(filePath));
         	JScrollPane scrollPanel=new JScrollPane(textArea);
         	scrollPanel.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
         	scrollPanel.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
         	scrollPanel.getViewport().add(textArea);
         	scrollPanel.getViewport().setPreferredSize(new Dimension(700,570));
         	JButton yes=new JButton("导入");
         	JButton no=new JButton("取消");
         	
         	yes.addActionListener(new ActionListener() {
					@Override
					public void actionPerformed(ActionEvent e) {
						
						try {
							if(dbName.equals("xml")){
								new XmlToDBService().importDataIntoDB(filePath,dbName);
							}else{
								JOptionPane.showConfirmDialog(null, "当前版本只支持特定数据库","温馨提示",JOptionPane.YES_NO_OPTION);
							}
						} catch (Exception e1) {
							e1.printStackTrace();
						}
						
					}
				});
         	
         	no.addActionListener(new ActionListener() {
					
					@Override
					public void actionPerformed(ActionEvent e) {
                      setVisible(false);							
					}
				});
         	
         	
         	JLabel label = new JLabel("数据库:");
         	
         	try {
				list = new DBService().getAllDatabases();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
         	
         	arrs = new String[list.size()];
         	
         	for(int i = 0; i < list.size(); i++){
         		arrs[i] = list.get(i);
         	}
         	
         	comboBox = new JComboBox(arrs);
         	comboBox.setSelectedItem(dbName);
         	comboBox.addItemListener(this);
         	
         	p1.add(scrollPanel);
         	
         	p2.setLayout(new FlowLayout(FlowLayout.LEFT));
         	p2.add(label);
         	p2.add(comboBox);
         	p2.add(yes);
         	p2.add(no);
         	
         	add("Center",p1);
         	add("South",p2);
         	
         	setVisible(true);
         	setSize(800,700);
         	setLocation(100,100);
         }
	}

	
	public String readFromFile(String path){
		File file=new File(path);
		String s=null;
		try {
		FileInputStream fin=new FileInputStream(file);
		
		int length=fin.available();
		
		byte arr[]=new byte[length];
		
		int len=fin.read(arr);
		
		s=new String(arr,0,len);
		
			
		} catch (FileNotFoundException e) {
		
			e.printStackTrace();
		} catch (IOException e) {
			
			e.printStackTrace();
		}
		return s;
		
	}


	@Override
	public void itemStateChanged(ItemEvent e) {
		// TODO Auto-generated method stub
		if(e.getStateChange() == ItemEvent.SELECTED){
			dbName =  "" + e.getItem();
		}
	}
}

ExportFrame.java：

package com.xmlDemo.frame;

import java.awt.Dimension;
import java.awt.FlowLayout;
import java.awt.Image;
import java.awt.Toolkit;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.ItemEvent;
import java.awt.event.ItemListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;

import com.xmlDemo.service.DBService;
import com.xmlDemo.service.XmlToDBService;

public class ImportFrame extends JFrame implements ItemListener{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private String filePath;
	
	private final static String BASEURL="../xmlDemo/images/";
	
	private JComboBox comboBox;
	
	private List<String> list;
	
	private String[] arrs = {};
	
	
	private String dbName ="xml";
	
	public ImportFrame(){
		 JFileChooser fileChooser=new JFileChooser("打开文件");
         int isOpen=fileChooser.showOpenDialog(null);
         fileChooser.setDialogTitle("打开文件");
         if(isOpen==JFileChooser.APPROVE_OPTION){
         	filePath = fileChooser.getSelectedFile().getPath();
         	
         	//final JDialog dialog=new JDialog();
         	Image image=Toolkit.getDefaultToolkit().getImage(BASEURL+"logo.png");
     		setIconImage(image);
     		setTitle("导入信息");
             
         	JPanel p1=new JPanel();
            JPanel p2=new JPanel();
             
         	
             JTextArea textArea=new JTextArea(60,60);
         	textArea.setText(readFromFile(filePath));
         	JScrollPane scrollPanel=new JScrollPane(textArea);
         	scrollPanel.setVerticalScrollBarPolicy(JScrollPane.VERTICAL_SCROLLBAR_ALWAYS);
         	scrollPanel.setHorizontalScrollBarPolicy(JScrollPane.HORIZONTAL_SCROLLBAR_ALWAYS);
         	scrollPanel.getViewport().add(textArea);
         	scrollPanel.getViewport().setPreferredSize(new Dimension(700,570));
         	JButton yes=new JButton("导入");
         	JButton no=new JButton("取消");
         	
         	yes.addActionListener(new ActionListener() {
					@Override
					public void actionPerformed(ActionEvent e) {
						
						try {
							if(dbName.equals("xml")){
								new XmlToDBService().importDataIntoDB(filePath,dbName);
							}else{
								JOptionPane.showConfirmDialog(null, "当前版本只支持特定数据库","温馨提示",JOptionPane.YES_NO_OPTION);
							}
						} catch (Exception e1) {
							e1.printStackTrace();
						}
						
					}
				});
         	
         	no.addActionListener(new ActionListener() {
					
					@Override
					public void actionPerformed(ActionEvent e) {
                      setVisible(false);							
					}
				});
         	
         	
         	JLabel label = new JLabel("数据库:");
         	
         	try {
				list = new DBService().getAllDatabases();
			} catch (Exception e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
         	
         	arrs = new String[list.size()];
         	
         	for(int i = 0; i < list.size(); i++){
         		arrs[i] = list.get(i);
         	}
         	
         	comboBox = new JComboBox(arrs);
         	comboBox.setSelectedItem(dbName);
         	comboBox.addItemListener(this);
         	
         	p1.add(scrollPanel);
         	
         	p2.setLayout(new FlowLayout(FlowLayout.LEFT));
         	p2.add(label);
         	p2.add(comboBox);
         	p2.add(yes);
         	p2.add(no);
         	
         	add("Center",p1);
         	add("South",p2);
         	
         	setVisible(true);
         	setSize(800,700);
         	setLocation(100,100);
         }
	}

	
	public String readFromFile(String path){
		File file=new File(path);
		String s=null;
		try {
		FileInputStream fin=new FileInputStream(file);
		
		int length=fin.available();
		
		byte arr[]=new byte[length];
		
		int len=fin.read(arr);
		
		s=new String(arr,0,len);
		
			
		} catch (FileNotFoundException e) {
		
			e.printStackTrace();
		} catch (IOException e) {
			
			e.printStackTrace();
		}
		return s;
		
	}


	@Override
	public void itemStateChanged(ItemEvent e) {
		// TODO Auto-generated method stub
		if(e.getStateChange() == ItemEvent.SELECTED){
			dbName =  "" + e.getItem();
		}
	}
}

上面的都是界面类，然后现在贴出Service的代码

package com.xmlDemo.service;

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

import com.xmlDemo.util.DBConnectionUtil;

public class DBService {

//获取某个数据库的所有数据表
	public List<String> getAllTables(String databaseName) throws Exception{
		List<String> list = new ArrayList<String>();
		int i = 0;
		
		String url = "jdbc:mysql://localhost:3306/"+databaseName;
		
		Connection connection = new DBConnectionUtil().getConnection(url);
	
		try {
			ResultSet rs=connection.getMetaData().getTables("","","",null);

			while (rs.next()) {
				list.add(rs.getString("TABLE_NAME"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	public List<String> getAllDatabases() throws Exception{
		
		List<String> list = new ArrayList<String>();
		int i = 0;
		
		String sql = "show databases";
		String url="jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8";
		Connection connection = new DBConnectionUtil().getConnection(url);
	
		try {
			PreparedStatement prepare = connection.prepareStatement(sql);
			ResultSet rs=prepare.executeQuery();

			while (rs.next()) {
				list.add(rs.getString(1));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().close();
		}

		return list;
	}

}

DBToXmlService.java:

package com.xmlDemo.service;

import java.io.FileOutputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

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

import com.xmlDemo.util.DBConnectionUtil;

public class DBToXmlService {
	//导出xml文件
	public void exportDataToXMlFile(String dbName,String tableName) throws Exception {
		//创建文档并设置根元素userinfo
				Element root=DocumentHelper.createElement("userinfo");
				Document document=DocumentHelper.createDocument(root);
				//访问数据库并将数据库信息封装进创建的xml文档中
				accessDB(document, root,dbName,tableName);
				//指定文档输出格式
				OutputFormat format=new OutputFormat("   ", true);
				//定义输出流，输出文档，限于内存中,表现为在控制台输出
				XMLWriter xmlWriter=new XMLWriter(format);
				xmlWriter.write(document);
				//获取当前时间
				SimpleDateFormat sf = new SimpleDateFormat("yyyy年MM月dd日HH时mm分ss秒");
				String time = sf.format(new Date());
				//把文档输出到存储设备，硬盘：第一种方式
				String fileName = dbName+"_"+tableName+"_"+time+".xml";
				XMLWriter xmlWriter2=new XMLWriter(new FileOutputStream("xml/"+fileName),format);
				xmlWriter2.write(document);
				//把文档输出到存储设备，硬盘：第二种方式
				XMLWriter xmlWriter3=new XMLWriter(new FileWriter("xml/"+fileName), format);
				xmlWriter3.write(document);
				//必须进行刷新和关闭，否则写入内容为空
				xmlWriter3.flush();
	}
	
	//定义静态函数访问数据库
	public static void accessDB(Document doc,Element root,String dbName,String tableName) {
		try {
			//数据库连接字符串
			String url="jdbc:mysql://localhost:3306/"+dbName;
			//连接数据库执行查询
			Connection connection=new DBConnectionUtil().getConnection(url);
			Statement statement=connection.createStatement();
			//获得数据库结果集
			ResultSet rs=statement.executeQuery("select * from "+tableName);
			//生成xml文档
			createXml(doc, root, rs);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			new DBConnectionUtil().close();
		}
	}
	//定义静态函数创建xml文档
	public static void createXml(Document doc,Element root,ResultSet rs) throws SQLException {
		while (rs.next()) {
			//生成与表名对应的元素节点并添加到根元素节点下
			Element user=root.addElement("users");
			//添加子元素userid
			Element userid=user.addElement("userid");
			userid.setText(rs.getString("userid"));
			//添加子元素username
			Element username=user.addElement("username");
			username.setText(rs.getString("username"));
			//添加子元素password
			Element password=user.addElement("password");
			password.setText(rs.getString("password"));
		}
	}
}

XMLToDBService.java:

package com.xmlDemo.service;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import com.xmlDemo.util.DBConnectionUtil;

public class XmlToDBService {

	public void importDataIntoDB(String path,String dbName) throws Exception{
		//sql
		String sql="insert into users (userid,username,password) values(?,?,?)";
		//调用工具包里的数据库连接方法
		String url = "jdbc:mysql://localhost:3306/"+dbName+"?useUnicode=true&characterEncoding=UTF-8";
		Connection connection = new DBConnectionUtil().getConnection(url);
		//执行sql
		PreparedStatement presta=connection.prepareStatement(sql);
		//定义解析器
		SAXReader reader=new SAXReader();
		//获取文档对象
		Document document=reader.read(new File(path));
		//获取根元素
		Element root=document.getRootElement();
		//获取根元素下的用户集合
		List userList=root.elements();
		//双重循环遍历每一个用户下的子元素信息
		for (int i = 0; i < userList.size(); i++) {
			Element userElement=(Element)userList.get(i);
			List itemList=userElement.elements();
			 System.out.println("第"+(i+1)+"个用户包含子元素个数："+itemList.size());
			//遍历每个用户的子元素信息
			for (int j = 0; j< itemList.size(); j++) {
				Element element=(Element)itemList.get(j);
				//获取子元素信息进行参数设置
				presta.setString(j+1, element.getText());
			}
			//批量更新
			presta.addBatch();
			presta.executeBatch();
		}
		System.out.println("xml消息插入数据库成功！");
		new DBConnectionUtil().close();
	}
}

然后是数据库连接的工具类：

package com.xmlDemo.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * 数据库连接的工具类
 * @version 1.0.0
 */
public class DBConnectionUtil {
	
	/**
	 * 驱动
	 */
	private String DRIVER="com.mysql.jdbc.Driver";
	
	/**
	 * 链接
	 */
	private String URL="jdbc:mysql://localhost:3306/xml?useUnicode=true&characterEncoding=UTF-8";
	
	/**
	 * 用户名
	 */
	private String USER="root";
	
	/**
	 * 密码
	 */
	private String PWD="111";
	
	Connection conn=null;
	
	PreparedStatement sta=null;
	
	ResultSet res=null;
	
	public DBConnectionUtil(){
		
	}
	
	/**
	 * 连接数据库
	 */
	public Connection getConnection(String url){
		try {
			Class.forName(DRIVER);
			conn=DriverManager.getConnection(url, USER, PWD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 关闭数据库,释放内存
	 */
	public void close(){
		try {
			if(res!=null){
				res.close();
			}
			if(sta!=null){
				sta.close();
			}
			if(conn!=null){
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}

实现效果：

这是下载的链接：http://download.csdn.net/detail/u014427391/9357575

继续阅读

更多来自我们博客的帖子

查看全部
如何安装 BuddyPress
由 测试 December 17, 2023
经过差不多一年的开发，BuddyPress 这个基于 WordPress Mu 的 SNS 插件正式版终于发布了。BuddyPress...
阅读更多
Filter如何工作
由 测试 December 17, 2023
在 web.xml...
阅读更多
如何理解CGAffineTransform
由 测试 December 17, 2023
CGAffineTransform A structure for holding an affine transformation matrix. ...
阅读更多