mysql数据，使用jdbc导出xml文件，亲测成功

最近因公司需要，需要将本地数据库数据导出成xml，提供给第三方使用，这个过程不是一次性的，而是随时的。

前提：导入mysql-connector-java-5.1.30.jar；myeclipse10；jdk1.6；数据库表自己建一个就行啦。

直接上代码：

2个java文件：DBConnection.java，TestXml.java

DBConnection.java

package com.applet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection
{
String Driver="com.mysql.jdbc.Driver";
String Url="jdbc:mysql://localhost:3306/xml";//xml为数据源
String Name="登录名";
String Pwd="密码";

public Connection getConnection() throws SQLException, ClassNotFoundException
{
Connection conn=null;
try
{
Class.forName(Driver);
conn=DriverManager.getConnection(Url,Name,Pwd);
}catch (Exception e)
{
e.printStackTrace();
}
return conn;
}
/**
* 获取数据
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public ResultSet sqlUser() throws SQLException, ClassNotFoundException{
getConnection();
String sql="select * from 表名";
ResultSet rs=null;
PreparedStatement ps=null;
Connection conn=null;
conn=DriverManager.getConnection(Url,Name,Pwd);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
return rs;
}
/**
* 获取表字段
* @return
* @throws SQLException
* @throws ClassNotFoundException
*/
public ResultSet sqlTable() throws SQLException,ClassNotFoundException{
getConnection();
String sql="select COLUMN_NAME from information_schema.COLUMNS where table_name = '表名' and table_schema = '数据库名'";
ResultSet rs=null;
PreparedStatement ps=null;
Connection conn=null;
conn=DriverManager.getConnection(Url,Name,Pwd);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery(sql);
return rs;
}
}

 

TestXml.java

package com.applet;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

public class TestXml
{
public static void main(String[] args)
{
List result = new ArrayList();
String line[]= null;
List<String> tableColumn=new ArrayList<String>();
try{
DBConnection con=new DBConnection();
ResultSet rs=con.sqlUser();// 数据行
ResultSet rsTable=con.sqlTable();// 数据表字段
while(rs.next())
{

// 下面代码将16个字段值取出，放置line中，存入result
line = new String[16];
line[0]=rs.getString(1);
line[1]=rs.getString(2);
line[2]=rs.getString(3);
line[3]=rs.getString(4);
line[4]=rs.getString(5);
line[5]=rs.getString(6);
line[6]=rs.getString(7);
line[7]=rs.getString(8);
line[8]=rs.getString(9);
line[9]=rs.getString(10);
line[10]=rs.getString(11);
line[11]=rs.getString(12);
line[12]=rs.getString(13);
line[13]=rs.getString(14);
line[14]=rs.getString(15);
line[15]=rs.getString(16);
result.add(line);
}
while(rsTable.next()){
tableColumn.add(rsTable.getString(1));
}
DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
DocumentBuilder builder=factory.newDocumentBuilder();
Document document=builder.newDocument();//Creat xml document
document.setXmlVersion("1.0");

Element root=document.createElement("table");//rootname of xml document
document.appendChild(root);//creat root of xml document
for(int k=1;k<=result.size();k++)
{
Element e=document.createElement("object");
e.setAttribute("rows", "row"+k);
root.appendChild(e);//elementname of xml document，如：<object rows='row1'></object>
}
NodeList nodeList=document.getElementsByTagName("object");
int size=nodeList.getLength();
for(int k=0;k<size;k++)
{
Node node=nodeList.item(k);
if(node.getNodeType()==Node.ELEMENT_NODE)
{
String[] temp = (String[])result.get(k);
Element elementNode=(Element)node;

for (int i = 0; null!=tableColumn && tableColumn.size()>0 && i < tableColumn.size(); i++) {

// 对于字段值为null或''时，进行判断
if(null==temp[i] || temp[i].trim().length()==0){
elementNode.appendChild(document.createElement(tableColumn.get(i)));// 类似 <phone />
}else{
elementNode.appendChild(document.createElement(tableColumn.get(i))).appendChild(document.createTextNode(temp[i]));
}
}
}
}
TransformerFactory transFactory=TransformerFactory.newInstance();
Transformer transformer=transFactory.newTransformer();
DOMSource domSource=new DOMSource(document);
File file=new File("H:/testxml.xml");//save xml document as url
FileOutputStream out=new FileOutputStream(file);
StreamResult xmlResult=new StreamResult(out);
transformer.transform(domSource,xmlResult);
}
catch(Exception e)
{
System.out.println(e);
}
System.out.println("success");
}
}

走起来！！成功了，获取数据如下：

 

 如果你有更好的方法，可以交流下，望不吝赐教，^_^