历史版本10 :解析数据库内xml文件 返回文档
编辑时间:
内容长度:图片数:目录数:
修改原因:
1. 问题描述编辑
数据库表,其中字段XML所存的为xml格式数据在表xmltest中,如下图,在使用该表进行报表制作时,需要将存于xml字段中的值读取出来作为报表数据源。
![222 222](/uploads/201808/20180815111939_5994.png)
XML每条记录数据格式如下:
<Field><Name>MemoryFreeSize</Name><Type>int</Type><Value>1962</Value></Field> <Field><Name>MemoryTotalSize</Name><Type>int</Type><Value>2047</Value></Field> <Field><Name>MemoryUsage</Name><Type>int</Type><Value>4</Value></Field>
<Field><Name>MemoryFreeSize</Name><Type>Int</Type><Value>1999</Value></Field> <Field><Name>MemoryTotalSize</Name><Type>Int</Type><Value>2048</Value></Field> <Field><Name>MemoryUsage</Name><Type>Int</Type><Value>10</Value></Field>
<Field><Name>MemoryFreeSize</Name><Type>Int</Type><Value>2000</Value></Field> <Field><Name>MemoryTotalSize</Name><Type>Int</Type><Value>2050</Value></Field> <Field><Name>MemoryUsage</Name><Type>Int</Type><Value>15</Value></Field>
最终用于制作报表的数据源形式如下:
![222 222](/uploads/201808/20180815111939_1984.png)
对于这样的情况我们如何来实现呢?
FineReport中可以通过自定义程序数据集来对xml字段数据进行解析,最终返回所希望的数据表。实现步骤如下:
FineReport中可以通过自定义程序数据集来对xml字段数据进行解析,最终返回所希望的数据表。实现步骤如下:
2. 实现原理编辑
FineReport报表的数据来源可以是任何类型的数据,因为FineReport是通过AbstractTableData抽象类来读取数据源的,因此用户只要实现了AbstractTableData抽象类,也就可以用自定义类型的程序数据集,AbstractTableData抽象类主要有5个方法,具体使用可参考简单程序数据集实现原理文档,数据来源是通过把xml格式数据转入ArrayList中。
3. 实现步骤编辑
3.1 数据集初始化方法init()
连接目标数据库后,执行sql查询语句,将xmltest表数据全部查询出来,对于ID、NAME字段的值我们将其直接存于新的结果集ArrayList中,对于xml字段我们通过GetXmlDate类对其进行解析后再转入ArrayList中。
GetXmlDate类代码如下:
package com.fr.data;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import com.fr.stable.xml.XMLReadable;
import com.fr.stable.xml.XMLableReader;
public class GetXmlDate {
// 定义返回值数组
private String[] Value = new String[3];
// 定义查询的name值
private String[] Name = null;
protected String[] readerXMLSource(InputStream in, String[] name)
throws Exception {
Name = name;
InputStreamReader reader = new InputStreamReader(in, "utf-8");
readXMLSource(reader);
return Value;
}
protected void readXMLSource(Reader reader) throws Exception {
XMLableReader xmlReader = XMLableReader.createXMLableReader(reader);
if (xmlReader != null) {
xmlReader.readXMLObject(new Content());
}
}
private class Content implements XMLReadable {
public void readXML(XMLableReader reader) {
if (reader.isChildNode()) {
if (reader.getTagName().equals("Field")) {
Field field = new Field();
reader.readXMLObject(field);
// 获得name对应的value值
if (Name[0].equals(field.name)) {
Value[0] = field.value;
} else if (Name[1].equals(field.name)) {
Value[1] = field.value;
} else if (Name[2].equals(field.name)) {
Value[2] = field.value;
}
}
}
}
}
// 定义每个field的结构
private class Field implements XMLReadable {
private String name;
private String type;
private String value;
public void readXML(XMLableReader reader) {
if (reader.isChildNode()) {
String tagName = reader.getTagName();
if (tagName.equals("Name")) {
this.name = reader.getElementValue();
} else if (tagName.equals("Type")) {
this.type = reader.getElementValue();
} else if (tagName.equals("Value")) {
this.value = reader.getElementValue();
}
}
}
}
}
3.2 定义程序数据集
定义类XMLRead.java,继承AbstractTableData接口,实现getColumnCount、getColumnName、getRowCount、getValueAt四个方法;
XMLRead.java类代码如下:
XMLRead.java类代码如下:
package com.fr.data;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.io.StringBufferInputStream;
public class XMLRead extends AbstractTableData {
// 列名数组,保存程序数据集所有列名
private String[] columnNames = { "id", "name", "MemoryFreeSize",
"MemoryTotalSize", "MemoryUsage" };
// 保存表数据
private ArrayList valueList = null;
public int getColumnCount() {
return 5;
}
public String getColumnName(int columnIndex) {
return columnNames[columnIndex];
}
public int getRowCount() {
init();
return valueList.size();
}
public Object getValueAt(int rowIndex, int columnIndex) {
init();
return ((Object[]) valueList.get(rowIndex))[columnIndex];
}
private void init() {
// 确保只被执行一次
if (valueList != null) {
return;
}
valueList = new ArrayList();
String sql = "select * from xmltest";
String[] name = { "MemoryFreeSize", "MemoryTotalSize", "MemoryUsage" };
Connection conn = this.getConncetion();
try {
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
// 用对象保存数据
Object[] objArray = null;
while (rs.next()) {
objArray = new Object[5];
String[] xmldata = null;
objArray[0] = rs.getObject(1);
objArray[1] = rs.getObject(2);
InputStream in = new StringBufferInputStream("<demo>"
+ rs.getObject(3).toString() + "</demo>");
GetXmlDate getxmldata = new GetXmlDate();
// 对xml流进行解析,返回的为name对应的value值数组
xmldata = getxmldata.readerXMLSource(in, name);
// 将解析后的值存于最终结果ArrayList中
objArray[2] = xmldata[0];
objArray[3] = xmldata[1];
objArray[4] = xmldata[2];
valueList.add(objArray);
}
// 释放数据源
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConncetion() {
String driverName = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://review.finedevelop.com:3306/susie";
String username = "root";
String password = "ilove";
Connection con = null;
try {
Class.forName(driverName);
con = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
return null;
}
return con;
}
// 释放一些资源,因为可能会有重复调用,所以需释放valueList,将上次查询的结果释放掉
public void release() throws Exception {
super.release();
this.valueList = null;
}
}
注:上述代码中的数据库连接需要改成你自己存放xmltest表的数据库
3.3 编译程序数据源
首先编译GetXmlDate.java然后再编译XMLRead.java,将生成的class文件放于WEB-INF/classes/com/fr/data下。
![222 222](/uploads/201903/1553736060wlVnLn0m.png)
![222 222](/uploads/201903/1553736060wlVnLn0m.png)
3.4 配置程序数据源
新建报表,
,选择我们定义好的程序数据集XMLRead.class文件,名字可以自定义,如ds1。3.5 使用程序数据源
制作报表保存为xmlread.cpt,如下:
![222 222](/uploads/201808/1534303168FFGKuDO3.png)
![222 222](/uploads/201808/1534303168FFGKuDO3.png)
BS访问报表,效果如下:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAeQAAABvCAIAAACyzCQBAAAMtUlEQVR4nO2du7WjMBRFVYhjUlLXQOicBhzQgmMCCpgWHFGIQ/phAj4SPxvxLpKAvRdrlkcPg+5BOkjCi6tqAAAIHuW7AgAA8BvMGgDgAGDWAAAHALMGADgAmDUAwAHArAEADgBmDQBwADBrAIADgFkDABwAzBoA4ABg1gAABwCzBgA4AJg1AMAB2GjWCgAANuHarD/153zbieP696nZRhuyIJcXGTFrGVPzXoed4vLeRgPckAW5vMiIWcuYmvc67BSX9zYa4IYsyOVFRsxaxtS812GnuLy30QA3ZEEuLzJi1jKm5r0OO8XlvY0GuCELcnmREbOWMTXvddgpLu9tNMANWZDLi4znM+siUVFWTQtVlL/3MzXvxrpTXN7b6H7bK4tVUm7rNt4rL7RVj0gppe7FXlqdSy6f21nNOimm5WWCWW+Ia9hiyrtSfad9JkpF+Wu31vlMzN+Ypk+RQxkVfmXxLau2dZtJoVNl/hWpKc1Pq/15NPMIslotyGW7uZTXuIG985txXr/bKc16YcOsN8U17didb5b3v9vEzy7a9clXlj7eW4+jzUgf8I/dZvYszpRp1HhlcWMoj+VzvYryd7ymWUtrtSiX/UV01/De+a0z6FeW/2WUILhh1mKm5t1Yd4prrs/Ej3c76HBk1u/8UZTPzecq0n5UKNL3ls3akTLPovz36cz6Uz2LpTFv9UhWGO7QrGW1WpTL/iK6a3iYtQuzrrJIKRVnb11YNLO6KI4OYtZFolSSZ5FSql98b+JSSjUrPGWi4ihSSqVZFneF9TuLjX32Muv8nsT3on5l8S2K2z7TzcpvWdXMIm9RrFT8yNKusH51lTP/e8uqZtJ9y6q+pDnaLavaya8x059+a3L2aX3qf93U3pzINyVGn9fn0lPs6ZG/uI9TZVo1TM8aHaedwg8j6pc4BhHNLYNIabUol+3mUt45s+6P80VMvY/qFk+WZbm8WTeelWqzLlKVlJ9P/XnnRzHrzzuP2vtNNxso0ta133mUlI0vJ0X9zuIoqz5FmhT1550n2tnTYj+zfhT5LSmfSfpozaK868XE+PHuRmdFqpLy3zu/D1t/u0/Xjod203SM6pGVrSnokbV2lrFxjI88LWm26hEN1zoHJtWO3J+J0usA88dZNGuHykzMevY4o5G13me4xDE0a1mtFuWyN2t38s6Y9WSOMiNmX5/phxlZbLfTm3Vjap9PfaRlkM6RjTqXiXHD7mNsoyvSxrj1TX04t5A263d5j+JbUrZmMX3Y1XTspue883tWDWyln2gbM+7eBR5Z9a/Iu2Y9t2w6+tbc2Ucl5kx2VJORSbXuU5TP2SN/dx+nyoxjmT/O2GLaR2eDEfFQB3GtFuXaYNbO5J1dBulmKlrbiZj9yHo08J+VBbP+TEbW7Zi0SA/z072JWRdJZ77dRGFq1jrST/15VzKRLph121H7B1x9y/73rl6fmT7TDnbMTj7fZ0YP+leY9fTsc/VRuu8ZDyon0/97O2BMn7PH+e4+TpWZMdOZ4/RmXeT6gWQT2tKoWVqrRblsN5fyan2qR9Q+1XzotY50QUw9jl5snH/YTmfWem23Xf3o16yTNFEzv78Oz6z1snszWG4WOrqgmijyRHXr2s2/Uf421qz72MXNunpESiXlK8ufzUCjaaZ9/aL81c2gH0n3r0qfk6XD2d+HNe37/muheTowHBeOSozV29Eq9uw+i8f55j5OlTHqpifX4zVrM8ykm923R07vUVM4GR5KayVk1q7l7XfTt70lobSYcwdfluXyZu1pO3Fcf+5mdls4D9+/dxv3Jz2EMuHI5UHe+V9ASsqIWcuYmvc67BSXuw7zzm8SD80dbK7d5zjKBCGXN3kXHgnIyYhZy5ia9zrsFJf/vhTehizI5UVGzFrG1LzXYae4vLfRADdkQS4vMmLWMqbmvQ47xeW9jQa4IQtyeZERs5YxNe912Cku7200wA1ZkMuLjK7NGgAANuDarLd9MXCI61IgixXIJQJmLQNxXQpksQK5RMCsZSCuS4EsViCXCJi1DMR1KZDFCuQSAbOWgbguBbJYgVwinNisy+4FKnFe7X6y4JvjRjV8xFXmRhX7eqelRcneeLzcVR4vxD++slUeTyVy0RkmhN07Bu1tScwQOK1Zl2knd5k66MRhN8ftajiOq+kpuqNM672mZH+8XW5twFUeN3GXqeo/GOFXedz7ufEXrZZDgu0d4/a2JGYYnNisjfaJWW9Vw0Nchp2YY8MqT/NqVYkDfJp1p04b7LwRV3ms4lgbe6r/4Egik6B7h3n78n1X+85pzbrHTevcFFc/N+1bSD/HbQqaHdKyLU7LvqT9W7Pbz29pbNXwa9b6cz9OXFOyPwG4T3vTNdxb37cazxnexmKlVJr6caAA5FpmeIubHSgEwsnNup/V7M2WuCZjXO2kg9nucAGt36lbalv1rfaE1mp4Nuv+PhTneTvoWVOyO57dp8pjpa/02F+qPC+N/zYf82bFyOG6vgFmLcKZzdrlstO2uLqh9WiAbD7hmLaZpkX1Tr/yWxvV8G3WPbM6/CzZC5/uYzpKPTNzHzYIFeeVOZ3y4kFHMWuWQWTOZ8vgUXgZ4pp1NwCq+zG22T6qajxa1pTp/EO45W9tVsO3WeuniOOHZd9Kdsef+5jjwLIcPAobx99fdNPeecA4Zra98YDxL+ezZDC8cNA8ty2D9DWcrFmrZj4bj3fovjkI6ee3tqvhftlqMkUYVXpNye54c5/SzJatB4HT+Ee/1eOne7NM2hs/3ZM4X+AQ16VAFiuQSwTMWgbiuhTIYgVyiYBZy0BclwJZrEAuETBrGYjrUiCLFcglAmYtA3FdCmSxArlE8GDWAACwAddmve2LgUNclwJZrEAuETBrGYjrUiCLFcglAmYtA3FdCmSxArlEwKxlIK5LgSxWIJcImLUMxHUpkMUK5BLhvGY9esnzzoTeHLeq4TiuVTmr1pTsjMfLvUIi81Uw394f4oywe4eZ1mtOumA4rVn37zpy8/qssJvjdjWcxrUqZ9Wakt3xdrlXSdS+js/Ac7aqYHvHJK3XVLqAOK1Za1xk9Qq3OY6xVMO1Wf/MWbWmZH98mvXvtF4Tx/H9muage8cw2QVmLXC+zTjx6sOk9bJVw1M3W8xZtabEAQG4z5e0XmWepuZbY70nQAlArmWG77MeSRcUpzbrNvFcqGvWjtN6bVLDQzf7mrMKs67rX2m9zKYRt7ljMOtFhnONkXRBcWqzbnCyTLctLpdpvfQpQ85u/itn1dWXQeoVEg32/ZIB3R0HMuseL2ngv3NWszZadKhm7TCt13Y13HazNTmr1nzYHX/us0IiLYP5ENJntqrDmPWMdAFxVrOeWxDek23LIJNfCe2U1mu7Gk672cqcVVf+6d4qiXQLGNiQv5XYYM16MY0ca9Z/OV/gENelQBYrkEsEzFoG4roUyGIFcomAWctAXJcCWaxALhEwaxmI61IgixXIJQJmLQNxXQpksQK5RPBg1gAAsAHXZr3ti4FDXJcCWaxALhEwaxmI61IgixXIJQJmLQNxXQpksQK5RMCsZSCuS4EsViCXCJi1DMR1KZDFCuQS4eRmbbwsaV8O0Rw3qOEjLjPNkn57g/FOk+V3Ybh6SZHHy70q81m35+jtIbO7OeAQvaP29P7Y9ZzXrOdevb8foTfHrWo4jmucZkm/sUrnrBq/SW4m09XueLvcq9J6dX9X+hVyef+mMEejlwGh944GU7EgOa9Z17XLO+URmuMWNTzEZb4E1Khx+37hpfd7m/vsj0+z/p3Wq9lPxfHkeg8mLe44SO+YUywkMGsZNsXlOK3X8cxaf+5HPWUa53k6VE1/8wLLIB1f0nq1Bj693mXpx4oCkOsHS4oFBWYtw5a4HKf1OqJZ9/ehOM+77tSb9GDab2S6coBn9/me1qtb6hhfbz9LIHXtXa6fLCkWGJi1DNvicpvW64hm3dMngx2Mu32l9fDpPr/Seg0bhPkY1lueqsDNekmx0MCsZdgQl8O0XvVy4Q98m/UkVZUxqOx2nGa62h1/7rMm81nL8Hr7zFMVuFn3MLKWOZ81izmxdmHbMoga13C3tF5b1XDczRbTLI3GkmbZbKarnfHmPisznxk76ueRmPVXJooFx3nN2i3EdSmQxQrkEgGzloG4LgWyWIFcImDWMhDXpUAWK5BLBMxaBuK6FMhiBXKJgFnLQFyXAlmsQC4RPJg1AABswKlZAwCASzBrAIADgFkDABwAzBoA4ABg1gAABwCzBgA4AJg1AMABwKwBAA4AZg0AcAAwawCAA4BZAwAcAMwaAOAAYNYAAAcAswYAOACYNQDAAcCsAQAOAGYNAHAA/gMgwGQN/KD+5gAAAABJRU5ErkJggg==)
数据库表字段为XML类型的数据就可以转换报表数据源了。