xml系列之数据库中数据的导入导出

刺骨的言语ヽ痛彻心扉 2021-09-10 16:56 525阅读 0赞

****

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

使用说明：

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

实验前准备:

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

![Center][]

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：

上面的都是界面类，然后现在贴出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();
    		}
    	}
    
    }

实现效果：

![Center 1][]

![Center 2][]

![Center 3][]

![Center 4][]

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

[Center]: /images/20210813/1b347235fa7d448b8b0de553b711e1cf.png
[Center 1]: /images/20210813/6962fe428be7454486ea64c5aeee8e86.png
[Center 2]: /images/20210813/40228fa05b2d4f7fb3ec52061e4fc439.png
[Center 3]: /images/20210813/01da093a845e48b5851feebbdcd72bd9.png
[Center 4]: /images/20210813/8bf219421ac14410826c676d464341bc.png
[http_download.csdn.net_detail_u014427391_9357575]: http://download.csdn.net/detail/u014427391/9357575