xml系列之数据库中数据的导入导出
这是我一个晚上做出来的,因为要去做其他的项目,所以只实现了对特定数据库的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
![](https://res.hc-cdn.com/ecology/9.3.130/v2_resources\images\global\blog-dialog.png)
华为开发者空间发布
让每位开发者拥有一台云主机
- 点赞
- 收藏
- 关注作者
评论(0)