Cocoon-reports之失败体验

Cocoon-reports之失败体验

参考文档：http://www.xml.com/pub/a/2003/01/22/cocoon-excel.html

最近，在构建公司新业务技术平台的时候总是在思考一个问题，WEB报表如何创建、渲染、展示？这个问题可能大家会说无非是3中解决方案：

1） 购买商业WEB报表套件；

2） 使用开源的WEB报表开发工具，如目前比较流行的Jasper report + ireport；

3） 自行开发；

事实上，从大的角度来讲搭建WEB报表中心确实上述3种解决方案足以，顶多说可能还有上述3种解决方案的不同组合而已。对于JSP、纯JAVA而言，这3种方案的实现原理都有点类似，无非是样式定义、js解析、填充数据，而这种实现原理本人总觉得报表展示速度慢、耗内存。为此，我一直在琢磨有没有基于java+excel的报表解决方案，为什么要使用excel作为报表展示工具，有人很好地阐述了这一点：Excel is the business world's spreadsheet of choice. While most readers of this article are software technologists, many of the projects and solutions you're developing are meant for use by non-technologists in a corporate environment, in which Microsoft Office is the dominant software suite. Your end user is probably already familiar and skilled with Office. Providing reports as Excel spreadsheets allows the data to be manipulated to meet the end user's needs. As long as a sufficiently similar report and associated data are available, most end users can manipulate the report to obtain the desired results.

在Google基于java excel reports solution的过程中，搜索结果“XML.com: Excel Reports with Apache Cocoon and POI”（Generating professional reports within a web application can be a difficult problem to solve. However, by combining two Apache projects (Cocoon and POI) you can produce Excel reports from a pure Java server application. The key to this solution is to embrace Excel on the client and deploy a Java solution on the server.）让我为之心跳，就迫不及待想在本机做个小应用体验下。

1、 cocoon-reports 的工作机制

Figure 1: Basic Solution Design

A report is defined in two parts: an Excel "Report Template" document and a solution specific "Report Scheme" XML document.

2、 参考文档上提供的部署操作步骤：

1） Download war file from http://candlelightsoftware.com/downloads/cocoon-reports.war

2） Place cocoon-reports in ~jakarta-tomcat-4.1.12/webapps/cocoon-reports.war

3） Start up Tomcat

4） Place browser URL to http://localhost:8080/cocoon-reports You should now see selection for the two demo report. This will also un-archive the war file so the additional configuration and setup steps below can be performed.

5） Create MySql database table: mysql < ~/cocoon-reports/demo.sql on command line.

6） Adjust Cocoon Sitemap file:

·Adjust URL for database if needed: see line 230 of sitemap file

·Adjust line 237 of sitemap file (i.e. parameter absolute-path) to reflect your installation

按照上述步骤，我下载了cocoon-reports.war，并且将之部署到tomcat下(我的tomcat是6.0)，建立mysql数据库并将demo.sql导入到test数据库中，修改sitemap文件中的<map:transform type="sql">配置信息和<map:parameter name="absolute-path" value="D:/Software/Java/tomcat6.0/webapps/cocoon-reports"/>配置信息，启动tomcat，在IE地址栏输入http://localhost:8080/cocoon-reports，成功出现下述页面：

Available reports are show below:

Rich Summary Example Report

Simple Summary Example Report

点击，上述第一个连接，进入http://localhost:8080/cocoon-reports/report/rich.rpt页面，默认时间查询条件2003-1-1到2003-1-10，点击Render Report按钮，报错“Access denied for user ''@'localhost' (using password: NO)”，查阅sitemap文件的<map:transform type="sql">，怀疑是此处没有设置mysql数据的用户和口令，更改如下：

<map:transform type="sql">

<!--<map:parameter name="use-connection" value="personnel"/>-->

<map:parameter name="dburl" value="jdbc:mysql://localhost:3306/test" />

<map:parameter name="username" value="root"/>

<map:parameter name="password" value="test"/>

</map:transform>

保存sitemap，并重启tomcat，继续上述操作，点击Render Report按钮，任然报错：Cannot connect to MySQL server on localhost:3306. Is there a MySQL server running on the machine/port you are trying to connect to? (java.lang.NumberFormatException)，不过跟上次已经不一样，总算是有点小成就，此前有过mysql驱动太旧会出现类似错误的经验，于是看了一下lib下的jar文件，果然有个mysql-connector-java-2.0.14-bin.jar文件，当时十分惊愕，mysql都6了，怎么apache还用怎么旧的driver，无语中。。。，用现有项目的mysql-connector-java-5.0.8-bin.jar替换了mysql-connector-java-2.0.14-bin.jar。重启tomcat，继续上述步骤，点击Render Report按钮任然报错：

java.sql.SQLException: Operation not allowed after ResultSet closed

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)

at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)

at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)

at org.apache.cocoon.transformation.SQLTransformer$Query.next(SQLTransformer.java:1166)

at org.apache.cocoon.transformation.SQLTransformer.executeQuery(SQLTransformer.java:333)

。。。

这下子，我彻底郁闷中，没办法只能关联cocoon-2.0.3.jar源码，并调试SQLTransformer，更让我崩溃的事情发生了，原来操作resultset是在connection关闭之后进行的，难怪会报java.sql.SQLException: Operation not allowed after ResultSet closed错误。到此，Cocoon-reports之体验由于时间关系就没再进行下去。哪位在tomcat6下，数据库是mysql5，能成功运行Cocoon-reports的请联系[e-mail]，不胜感激。