回复
xml系列之数据库中数据的导入导出
jowvid
发布于 2020-8-30 10:55
浏览
0收藏
这是我一个晚上做出来的，因为要去做其他的项目，所以只实现了对特定数据库的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数据库的
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
分类
赞
收藏
回复
相关推荐
