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

举报
yd_273762914 发表于 2020/12/03 00:05:45 2020/12/03
【摘要】  这是我一个晚上做出来的，因为要去做其他的项目，所以只实现了对特定数据库的xml操作，不过我觉得这是学习xml挺不错的参考代码和文档 使用说明： 要先导入xml.sql数据库，可以用navicat导入，然后运行java项目就可以，这是java+mysql数据库实现的程序，仅供参考互相学习     实验前准备:   新建一个Java工程，工程名称为xmlDe...



这是我一个晚上做出来的，因为要去做其他的项目，所以只实现了对特定数据库的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

 

 

 



文章来源: smilenicky.blog.csdn.net，作者：smileNicky，版权归原作者所有，如需转载，请联系作者。

原文链接：smilenicky.blog.csdn.net/article/details/50315215

【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
XML 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入