/*
*
****数据库导出.xml文件,通过给定的表名导出所有数据生成xml文件
****
*/
package com.lwp.dbxml;
import java.io.File;
import java.io.FileWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.zip.DataFormatException;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import com.sun.org.apache.bcel.internal.generic.NEW;
public class CreateXML {
/**
* 通过给定的表名导出所有数据生成xml文件
*
* @param tableName
* 数据库表名
*/
public static void createXml(String tableName) {
System.out.println("==========转换开始==============");
try {
// -------------jdbc代码 com.microsoft.sqlserver.jdbc.SQLServerDriver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://localhost:1433;DatabaseName=SunLeadsGps";
String user = "sa";//
String password = "123456";//
Connection con = DriverManager.getConnection(url, user, password);
String sql = "select e.* from " + tableName + " e";
PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
// 任务:从resultset结果集中提取元数据信息
ResultSetMetaData rsmd = rs.getMetaData();
// 表的字段数目
int count = rsmd.getColumnCount();
// 存入列名数组
String[] columnName = new String[count];
for (int i = 1; i <= count; i++) {
columnName[i - 1] = rsmd.getColumnName(i);
}
// -----------------使用DOM4J生成xml文件
Document doc = DocumentHelper.createDocument();
// 生成根节点
Element root = doc.addElement("ROOT");
int i;
while (rs.next()) {
Element e = root.addElement(tableName);
for (i = 1; i <= count; i++) {
Element column = e.addElement(columnName[i - 1]);
if (rs.getObject(i) != null) {
column.setText(rs.getObject(i) + "");
} else {
column.setText("");
}
}
}
// 写入文件
DateFormat df = new SimpleDateFormat("yyyyMMdd");
OutputFormat opf = OutputFormat.createPrettyPrint();
opf.setEncoding("GBK");
System.out.println("E:/" + df.format(new Date()));
File f = new File("E:/" + df.format(new Date()));
if (f.exists() == false) {
f.mkdir();
}
Writer w = new FileWriter(f + "/" + tableName + ".xml");
XMLWriter xw = new XMLWriter(w, opf);
xw.write(doc);
// 关闭文件流
xw.close();
w.close();
rs.close();
ps.close();
con.close();
System.out.println("==========生成完毕==============");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
createXml("user");
}