用amchart的时候遇到一个难题：将数据库里的数据生成xml （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文件里面：
<code>
<?xml version="1.0" encoding="UTF-8"?>
<chart>
<series>
<value xid="0">02-06 15:15</value>
<value xid="1">02-06 15:23</value>
<value xid="2">02-06 15:30</value>
<value xid="3">02-09 09:37</value>
<value xid="4">02-09 09:39</value>
<value xid="5">02-09 09:48</value>
<value xid="6">02-09 09:51</value>
</series>
<graphs>
<graph gid="1" title="C:">
<value xid="0">66.4</value>
<value xid="1">66.4</value>
<value xid="2">66.1</value>
<value xid="3">64.4</value>
<value xid="4">64.4</value>
<value xid="5">64.4</value>
<value xid="6">64.4</value>
</graph>
<graph gid="2" title="D:">
<value xid="0">82.8</value>
<value xid="1">82.8</value>
<value xid="2">82.8</value>
<value xid="3">82.8</value>
<value xid="4">82.8</value>
<value xid="5">82.8</value>
<value xid="6">82.8</value>
</graph>
<graph gid="3" title="E:">
<value xid="0">80.9</value>
<value xid="1">80.9</value>
<value xid="2">80.9</value>
<value xid="3">83.8</value>
<value xid="4">83.8</value>
<value xid="5">83.8</value>
<value xid="6">83.8</value>
</graph>
</graphs>
</chart>
</code>
下面是我之前的实现方法：
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;
}
}