最近网站需要做数据迁移,需要我将网站的数据从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");// 加载mysql驱动程序
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", "isp@gjj");// 获取连接
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。