從MySql 數據庫中導出數據並生成指定格式xml文件

最近網站需要做數據遷移，需要我將網站的數據從mysql數據庫中導出，並按照要求生成指定格式的xml文件。

xml格式要求：

代碼：

package com.epsoft.gjjisp;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.stream.StreamResult;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.io.DocumentSource;


public class DataToXml {
	
	public static void main(String[] args) throws Exception{
		long a = System.currentTimeMillis();
		DataToXml cd = new DataToXml();
		String xmlString = cd.getColumnXml();
		cd.stringToXml(xmlString);
		System.out.println(System.currentTimeMillis()-a);
	}
	/**
	 * 描述：生成xml文件
	 * @param xmlString
	 * @throws Exception
	 */
	public void stringToXml(String xmlString) throws Exception {
		Document resDoc = DocumentHelper.parseText(xmlString);
		TransformerFactory tFactory = TransformerFactory.newInstance();  
		Transformer transformer = tFactory.newTransformer();
		DocumentSource source = new DocumentSource(resDoc);
		transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
	 	//設置文檔的換行與縮進
		transformer.setOutputProperty(OutputKeys.INDENT, "YES");
		//設置日期格式
		SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddHHmmss");
	  	String resFile = "E:\\"+fmt.format(new Date())+".xml";
	 	StreamResult result = new StreamResult(new File(resFile));
	 	transformer.transform(source,result);
	}
 
	/**
	 * 描述：獲取欄目數據並轉化爲xml字符串
	 * @return
	 */
	public String getColumnXml() {
		Connection con = null;// 創建一個數據庫連接
		PreparedStatement pre = null;// 創建預編譯語句對象
		ResultSet rs = null;// 創建一個結果集對象
		StringBuffer xmlString = new StringBuffer();
		xmlString.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "\t" + "\r\n"  +"<!DOCTYPE book [<!ENTITY nbsp \" \"><!ENTITY copy \"©\"><!ENTITY reg \"®\"><!ENTITY trade \"™\"><!ENTITY mdash \"—\"><!ENTITY ldquo \"「\"><!ENTITY rdquo \"」\"><!ENTITY pound \"£\"><!ENTITY yen \"¥\"><!ENTITY euro \"€\">]>"  + "\r\n" + "<book>"  + "\r\n"+"\t");
		xmlString.append("<edition>1.0</edition>"  + "\r\n" + "\t");
		xmlString.append("<copyright>大漢網絡</copyright>"  + "\r\n" + "\t");
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加載Oracle驅動程序
			String url = "jdbc:mysql://192.168.70.175:3306/isp_gjj_zs?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8";
			//String url = "jdbc:mysql://10.25.1.2:3306/isp_gjj_zs?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8";
			con = DriverManager.getConnection(url, "isp_gjj",  "[email protected]");// 獲取連接
			String sql = "SELECT TITLE, ASSIST_TITLE, OUT_LINK, CREATOR, SOURCE, KEYWORDS, REMARK, CONTENT, CREATE_TIME, IMAGE_URL FROM c_news n, c_news_channel_category nc WHERE n.id = nc.news_id AND nc.category_code = 5007 AND channel_code = 1  LIMIT 5";// 預編譯語句，「？」代表參數
			pre = con.prepareStatement(sql);// 實例化預編譯語句
			rs = pre.executeQuery();// 執行查詢，注意括號中不需要再加參數
			StringBuffer article = new StringBuffer();
			while(rs.next()) {
				article.append( "<article>"  + "\r\n" + "\t\t");
				article.append("<title><![CDATA[" + rs.getString("TITLE") + "]]></title>"  + "\r\n" + "\t\t");
				article.append("<caption><![CDATA[" + rs.getString("ASSIST_TITLE") + "]]></caption>"  + "\r\n" + "\t\t");
				article.append("<leadtitle><![CDATA[]]></leadtitle>"  + "\r\n" + "\t\t");
				article.append("<linktitle><![CDATA[]]></linktitle>"  + "\r\n" + "\t\t");
				article.append("<href><![CDATA[" + rs.getString("OUT_LINK") + "]]></href>"  + "\r\n" + "\t\t");
				article.append("<author><![CDATA[" + rs.getString("CREATOR") + "]]></author>"  + "\r\n" + "\t\t");
				article.append("<source><![CDATA[" + rs.getString("SOURCE") + "]]></source>"  + "\r\n" + "\t\t");
				article.append("<userid><![CDATA[]]></userid>"  + "\r\n" + "\t\t");
				article.append("<editor><![CDATA[]]></editor>"  + "\r\n" + "\t\t");
				article.append("<keyword><![CDATA[" + rs.getString("KEYWORDS") + "]]></keyword>"  + "\r\n" + "\t\t");
				article.append("<classname><![CDATA[]]></classname>"  + "\r\n" + "\t\t");
				article.append("<describe><![CDATA[" + rs.getString("REMARK") + "]]></describe>"  + "\r\n" + "\t\t");
				article.append("<text><![CDATA[" + rs.getString("CONTENT") + "]]></text>"  + "\r\n" + "\t\t");
				article.append("<datetime><![CDATA[" + rs.getString("CREATE_TIME") + "]]></datetime>"  + "\r\n" + "\t\t");
				article.append("<deploytime><![CDATA[]]></deploytime>"  + "\r\n" + "\t\t");
				article.append("<validend><![CDATA[0000-00-00]]></validend>"  + "\r\n" + "\t\t");
				article.append("<image><![CDATA[" + rs.getString("IMAGE_URL") + "]]></image>"  + "\r\n" + "\t\t");
				article.append("<flash><![CDATA[]]></flash>"  + "\r\n" + "\t\t");
				article.append("<media><![CDATA[]]></media>"  + "\r\n" + "\t\t");
				article.append("<attach><![CDATA[]]></attach>"  + "\r\n" + "\t");
				if(!rs.isLast()) {
					article.append( "</article>"  + "\r\n" + "\t");
				}else {
					article.append( "</article>"  + "\r\n" );
				}
			}
			xmlString.append( article.toString() + "</book>"  + "\t" );
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 注意關閉的順序，最後使用的最先關閉
				if (rs != null)
					rs.close();
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return xmlString.toString();
	}
	
	/**
	 * 描述：獲取期房樓盤數據並轉化爲xml
	 * @return
	 */
	public String getqflp() {
		Connection con = null;// 創建一個數據庫連接
		PreparedStatement pre = null;// 創建預編譯語句對象
		ResultSet rs = null;// 創建一個結果集對象
		StringBuffer xmlString = new StringBuffer();
		xmlString.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + "\t" + "\r\n"  +"<!DOCTYPE book [<!ENTITY nbsp \" \"><!ENTITY copy \"©\"><!ENTITY reg \"®\"><!ENTITY trade \"™\"><!ENTITY mdash \"—\"><!ENTITY ldquo \"「\"><!ENTITY rdquo \"」\"><!ENTITY pound \"£\"><!ENTITY yen \"¥\"><!ENTITY euro \"€\">]>"  + "\r\n" + "<book>"  + "\r\n"+"\t");
		xmlString.append("<edition>1.0</edition>"  + "\r\n" + "\t");
		xmlString.append("<copyright>大漢網絡</copyright>"  + "\r\n" + "\t");
		try {
			Class.forName("com.mysql.jdbc.Driver");// 加載Oracle驅動程序
			String url = "jdbc:mysql://192.168.70.175:3306/isp_gjj_zs?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8";
			//String url = "jdbc:mysql://10.25.1.2:3306/isp_gjj_zs?allowMultiQueries=true&useUnicode=true&characterEncoding=utf8";
			con = DriverManager.getConnection(url, "isp_gjj",  "[email protected]");// 獲取連接
			String sql = "SELECT TITLE, ASSIST_TITLE, OUT_LINK, CREATOR, SOURCE, KEYWORDS, REMARK, CONTENT, CREATE_TIME, IMAGE_URL FROM c_news n, c_news_channel_category nc WHERE n.id = nc.news_id AND nc.category_code = 5007 AND channel_code = 1  LIMIT 5";// 預編譯語句，「？」代表參數
			pre = con.prepareStatement(sql);// 實例化預編譯語句
			rs = pre.executeQuery();// 執行查詢，注意括號中不需要再加參數
			StringBuffer article = new StringBuffer();
			while(rs.next()) {
				article.append( "<article>"  + "\r\n" + "\t\t");
				article.append("<title><![CDATA[" + rs.getString("TITLE") + "]]></title>"  + "\r\n" + "\t\t");
				article.append("<caption><![CDATA[" + rs.getString("ASSIST_TITLE") + "]]></caption>"  + "\r\n" + "\t\t");
				article.append("<leadtitle><![CDATA[]]></leadtitle>"  + "\r\n" + "\t\t");
				article.append("<linktitle><![CDATA[]]></linktitle>"  + "\r\n" + "\t\t");
				article.append("<href><![CDATA[" + rs.getString("OUT_LINK") + "]]></href>"  + "\r\n" + "\t\t");
				article.append("<author><![CDATA[" + rs.getString("CREATOR") + "]]></author>"  + "\r\n" + "\t\t");
				article.append("<source><![CDATA[" + rs.getString("SOURCE") + "]]></source>"  + "\r\n" + "\t\t");
				article.append("<userid><![CDATA[]]></userid>"  + "\r\n" + "\t\t");
				article.append("<editor><![CDATA[]]></editor>"  + "\r\n" + "\t\t");
				article.append("<keyword><![CDATA[" + rs.getString("KEYWORDS") + "]]></keyword>"  + "\r\n" + "\t\t");
				article.append("<classname><![CDATA[]]></classname>"  + "\r\n" + "\t\t");
				article.append("<describe><![CDATA[" + rs.getString("REMARK") + "]]></describe>"  + "\r\n" + "\t\t");
				article.append("<text><![CDATA[" + rs.getString("CONTENT") + "]]></text>"  + "\r\n" + "\t\t");
				article.append("<datetime><![CDATA[" + rs.getString("CREATE_TIME") + "]]></datetime>"  + "\r\n" + "\t\t");
				article.append("<deploytime><![CDATA[]]></deploytime>"  + "\r\n" + "\t\t");
				article.append("<validend><![CDATA[0000-00-00]]></validend>"  + "\r\n" + "\t\t");
				article.append("<image><![CDATA[" + rs.getString("IMAGE_URL") + "]]></image>"  + "\r\n" + "\t\t");
				article.append("<flash><![CDATA[]]></flash>"  + "\r\n" + "\t\t");
				article.append("<media><![CDATA[]]></media>"  + "\r\n" + "\t\t");
				article.append("<attach><![CDATA[]]></attach>"  + "\r\n" + "\t");
				if(!rs.isLast()) {
					article.append( "</article>"  + "\r\n" + "\t");
				}else {
					article.append( "</article>"  + "\r\n" );
				}
			}
			xmlString.append( article.toString() + "</book>"  + "\t" );
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 注意關閉的順序，最後使用的最先關閉
				if (rs != null)
					rs.close();
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return xmlString.toString();
	}

}

這裏我用到的jar包有dom4j-1.6.1.jar，xml-apis-1.0.b2.jar，mysql-connector-java-5.1.38.jar。