java数据库导出xml,用java将数据库里的数据生成xml(amchart用)

(mysql5.0)sql脚本

CREATE TABLE `hostdiskinfo` (

`hdid` bigint(20) NOT NULL auto_increment,

`begintime` datetime default '0000-00-00 00:00:00',

`hdiskinfo` longtext,

`hostid` bigint(20) default '0',

PRIMARY KEY (`hdid`)

) ；

这是查询结果：

mysql> select hdiskinfo,begintime from hostdiskinfo where hostid=11

+-------------------------+---------------------+

| hdiskinfo               | begintime           |

+-------------------------+---------------------+

| C:,66.4;D:,82.8;E:,80.9 | 2009-02-06 15:15:03 |

| C:,66.4;D:,82.8;E:,80.9 | 2009-02-06 15:23:45 |

| C:,66.1;D:,82.8;E:,80.9 | 2009-02-06 15:30:02 |

| C:,64.4;D:,82.8;E:,83.8 | 2009-02-09 09:37:20 |

| C:,64.4;D:,82.8;E:,83.8 | 2009-02-09 09:39:41 |

| C:,64.4;D:,82.8;E:,83.8 | 2009-02-09 09:48:23 |

| C:,64.4;D:,82.8;E:,83.8 | 2009-02-09 09:51:14 |

+-------------------------+---------------------+

7 rows in set (0.00 sec)

现在的要求是要将以上的查询结果以下面形式写入到xml文件里面：

02-06 15:15

02-06 15:23

02-06 15:30

02-09 09:37

02-09 09:39

02-09 09:48

02-09 09:51

66.4

66.4

66.1

64.4

64.4

64.4

64.4

82.8

82.8

82.8

82.8

82.8

82.8

82.8

80.9

80.9

80.9

83.8

83.8

83.8

83.8

下面是我之前的实现方法：

package org.enilu.util.amchart;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

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 org.enilu.util.db.DBConnection;

import org.jdom.Document;

import org.jdom.Element;

import org.jdom.output.Format;

import org.jdom.output.XMLOutputter;

public class AmchartDemo02 {

/**

* @param args

*/

public static void main(String[] args) {

// TODO Auto-generated method stub

List all = getData();

System.out.println(all.size());

createXML(all);

}

public static List getData() {

Connection conn = DBConnection.getConnection("conf.properties");

List all = new ArrayList();

ResultSet rs = null;

Hostdiskinfo hd = null;

PreparedStatement pstmt = null;

String sql = "select hdiskinfo,begintime from hostdiskinfo where hostid=11";

try {

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()) {

hd = new Hostdiskinfo();

hd.setHdiskinfo(rs.getString(1));

hd.setBegintime(rs.getTimestamp(2));

System.out.println(hd.getBegintime());

all.add(hd);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

rs.close();

pstmt.close();

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return all;

}

public static boolean createXML(List all) {

Document doc = new Document();

Element chart, series, value, graphs, ce, de, ee, fe, ge, he = null;

ce = new Element("graph");

de = new Element("graph");

ee = new Element("graph");

fe = new Element("graph");

ge = new Element("graph");

he = new Element("graph");

chart = new Element("chart");

series = new Element("series");

graphs = new Element("graphs");

for (int i = 0; i < all.size(); i++) {

value = new Element("value");

value.setAttribute("xid", i + "");

Hostdiskinfo hd = (Hostdiskinfo) all.get(i);

value.setText(hd.getBegintime().toString().substring(5, 16));

series.addContent(value);

}

chart.addContent(series);

Hostdiskinfo hd2 = (Hostdiskinfo) all.get(0);

String st = hd2.getHdiskinfo();

if (st.contains("C:")) {

ce.setAttribute("gid", "1");

ce.setAttribute("title", "C:");

}

if (st.contains("D:")) {

de.setAttribute("gid", "2");

de.setAttribute("title", "D:");

}

if (st.contains("E:")) {

ee.setAttribute("gid", "3");

ee.setAttribute("title", "E:");

}

if (st.contains("F:")) {

fe.setAttribute("gid", "4");

fe.setAttribute("title", "F:");

}

if (st.contains("G:")) {

ge.setAttribute("gid", "5");

ge.setAttribute("title", "G:");

}

if (st.contains("H:")) {

he.setAttribute("gid", "6");

he.setAttribute("title", "H:");

}

for (int a = 0; a < all.size(); a++) {

Hostdiskinfo hd3 = (Hostdiskinfo) all.get(a);

String[] st1 = hd3.getHdiskinfo().split(";");

for (int b = 0; b < st1.length; b++) {

String[] st2 = st1[b].split(",");

value = new Element("value");

value.setAttribute("xid", a + "");

value.setText(st2[1]);

if (st2[0] == "C:" || st2[0].equals("C:")) {

ce.addContent(value);

}

if (st2[0] == "D:" || st2[0].equals("D:")) {

de.addContent(value);

}

if (st2[0] == "E:" || st2[0].equals("E:")) {

ee.addContent(value);

}

if (st2[0] == "F:" || st2[0].equals("F:")) {

fe.addContent(value);

}

if (st2[0] == "G:" || st2[0].equals("G:")) {

ge.addContent(value);

}

if (st2[0] == "H:" || st2[0].equals("H:")) {

he.addContent(value);

}

}

}

if (ce.getChildren().size() > 1) {

graphs.addContent(ce);

}

if (de.getChildren().size() > 1) {

graphs.addContent(de);

}

graphs.addContent(ee);

if (fe.getChildren().size() > 1) {

graphs.addContent(fe);

}

if (ge.getChildren().size() > 1) {

graphs.addContent(ge);

}

if (he.getChildren().size() > 1) {

graphs.addContent(he);

}

chart.addContent(graphs);

doc.addContent(chart);

Format f = Format.getRawFormat();

f.setEncoding("UTF-8");

f.setIndent(" ");// 每一层元素缩排两格

XMLOutputter XMLOut = new XMLOutputter(f);

try {

XMLOut.output(doc, new FileOutputStream("d:/amline_data.xml"));

return true;

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return false;

}

}

我想不到其他的实现方法，本来打算在for循环里动态new graph实例，但是我做不到，不知哪位高人路过你呢个指点下^_^

效果图如下：

后来朋友给了个更好的循环方法，贴这儿^_^：

package org.enilu.util.amchart;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

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 org.enilu.util.db.DBConnection;

import org.jdom.Document;

import org.jdom.Element;

import org.jdom.output.Format;

import org.jdom.output.XMLOutputter;

public class AmchartDemo03 {

public static void main(String[] args) {

List all = getData();

createXML(all);

}

/**

* 从数据库里取得数据

*

* @return

*/

public static List getData() {

Connection conn = DBConnection.getConnection("conf.properties");

List all = new ArrayList();

ResultSet rs = null;

Hostdiskinfo hd = null;

PreparedStatement pstmt = null;

String sql = "select hdiskinfo,begintime from hostdiskinfo where hostid=11";

try {

pstmt = conn.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()) {

hd = new Hostdiskinfo();

hd.setHdiskinfo(rs.getString(1));

hd.setBegintime(rs.getTimestamp(2));

System.out.println(hd.getBegintime());

all.add(hd);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

rs.close();

pstmt.close();

conn.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return all;

}

/**

* 创建数据源文件xml

*

* @param all

* @return

*/

public static boolean createXML(List all) {

Document doc = new Document();

Element chart, series, value, graphs, graph = null;

chart = new Element("chart");

series = new Element("series");

graphs = new Element("graphs");

for (int i = 0; i < all.size(); i++) {

value = new Element("value");

value.setAttribute("xid", i + "");

Hostdiskinfo hd = (Hostdiskinfo) all.get(i);

value.setText(hd.getBegintime().toString().substring(5, 16));

series.addContent(value);

}

Hostdiskinfo hostdiskinfo = (Hostdiskinfo) all.get(0);

String diskinfo = hostdiskinfo.getHdiskinfo();

chart.addContent(series);

/* 随即取出一条数据根据是否大写判断有几个磁盘，并将磁盘名称放到集合allDriver里 */

List allDriver = new ArrayList();

Hostdiskinfo hd2 = (Hostdiskinfo) all.get(0);

String st = hd2.getHdiskinfo();

for (int i = 0; i < st.length(); i++) {

Character c = st.charAt(i);

if (c.isUpperCase(c)) {

allDriver.add(c);

System.out.println(c);

}

}

int driverNum = allDriver.size();

for (int m = 0; m < driverNum; m++) {

graph = new Element("graph");

graph.setAttribute("gid", m + "");

graph.setAttribute("title", allDriver.get(m) + "盘");

for (int n = 0; n < all.size(); n++) {

Hostdiskinfo hd = (Hostdiskinfo) all.get(n);

String st1[] = hd.getHdiskinfo().split(";");

String st2[] = st1[m].split(",");

value = new Element("value");

value.setAttribute("xid", n + "");

value.setText(st2[1]);

graph.addContent(value).detach();

}

graphs.addContent(graph);

}

chart.addContent(graphs);

doc.addContent(chart);

Format f = Format.getRawFormat();

f.setEncoding("UTF-8");

f.setIndent(" ");// 每一层元素缩排两格

XMLOutputter XMLOut = new XMLOutputter(f);

try {

XMLOut.output(doc, new FileOutputStream("d:/amline_data.xml"));

return true;

} catch (FileNotFoundException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

return false;

}

}