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
相關文章
- MongoDB--Mongodb 中資料匯出與匯入MongoDB
- 資料庫的匯入匯出資料庫
- 將XML匯入資料庫XML資料庫
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- mysql 資料庫匯入匯出MySql資料庫
- MySQL資料庫匯入匯出MySql資料庫
- Mongodb資料的匯出與匯入MongoDB
- AWR資料的匯出與匯入
- 將資料庫中資料匯入至solr索引庫資料庫Solr索引
- 將資料庫中資料匯出為excel表格資料庫Excel
- MongoDB資料匯入與匯出MongoDB
- DB2資料庫匯出表結構與匯入、匯出表資料DB2資料庫
- SQL資料庫的匯入和匯出SQL資料庫
- 使用xml檔案,做資料的匯入,匯出 (轉)XML
- 【mysql】資料庫匯出和匯入MySql資料庫
- mysqldump匯入匯出mysql資料庫MySql資料庫
- oracle資料庫匯入匯出命令!Oracle資料庫
- 使用EXPDP/IMPDP匯入匯出表中資料/後設資料測試
- MySQL表資料匯入與匯出MySql
- 將informix匯出的文字資料匯入oracle資料庫ORMOracle資料庫
- 不同版本間的資料庫匯入匯出資料庫
- Oracle資料庫的匯入和匯出命令Oracle資料庫
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- OracleDatabase——資料庫表空間dmp匯出與匯入OracleDatabase資料庫
- plsql developer匯入匯出資料庫方法SQLDeveloper資料庫
- 在SQL Server資料庫中匯入匯出資料SQLServer資料庫
- 資料泵的匯入匯出
- SQLServer 異構資料庫之間資料的匯入匯出SQLServer資料庫
- [Docker核心之容器、資料庫檔案的匯入匯出、容器映象的匯入匯出]Docker資料庫
- 資料泵匯出匯入
- Oracle 資料匯入匯出Oracle
- mysql資料匯入匯出MySql
- mysql資料匯出匯入MySql
- oracle資料匯入匯出Oracle
- SQL Server資料庫匯入匯出資料方式比較SQLServer資料庫
- 資料庫泵(expdp/impdp)匯入匯出流程資料庫
- plsql developer匯入匯出資料庫方法 <轉>SQLDeveloper資料庫
- MySQL資料庫結構和資料的匯出和匯入 (轉)MySql資料庫