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 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 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 getAllTables(String databaseName) throws Exception{

List list = new ArrayList();

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 getAllDatabases() throws Exception{

List list = new ArrayList();

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

??

XML 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。