导出JasperReports查询结果 [英] Export JasperReports query results

问题描述

在我的Java项目中，我有很多JasperReports报告与复杂的SQL查询，包含很多参数。这些报告用于生成包含查询返回的数据的pdf文档，以多种方式进行分组和格式化。

现在我还需要直接导出查询结果（例如ResultSet，或Map或csv文件，或类似的...）。
有可能要求JasperReports只执行查询并返回结果，而不是渲染pdf页面？

（注意：它与选择一个csv输出格式到报表渲染，因为这个方法试图把报表设计转换成一个csv文件...而是只想在报表中重用查询，还利用JR参数管理等等...）

这是我的Java代码，从报告中生成pdf文档：

  JasperReport report =（JasperReport）JRLoader.loadObject（inStream）; 
 JasperPrint jasperprint = JasperFillManager.fillReport（report，params，conn）; 
 JRAbstractExporter exporter = new JRPdfExporter（）; 
 exporter.exportReport（）; 
 ByteArrayOutputStream os =（ByteArrayOutputStream）exporter.getParameter（JRExporterParameter.OUTPUT_STREAM）; 
 byte [] formattedReportBytes = os.toByteArray（）; 
 return formattedReportBytes; 
  

我看到有一个名为 JRJdbcQueryExecuter 的类JasperReports ...
可以直接调用它而不是调用 fillReport ，以获取执行的SQL查询的ResultSet？

谢谢

解决方案

我想从这开始，感觉到错误和黑客，这是可能的，减去实际上有JasperReports执行查询。

  JasperReport report =（JasperReport）JRLoader.loadObject（inStream）; 
 
 //这是报表中的实际查询
 JRQuery query = report.getMainDataSet（）。getQuery; 
 
 //一旦在这里你得到整个sql字符串，这将有任何参数替换为
 //'？'字符
 String queryString = query.getText（）; 
 
 //现在开始构建准备好的语句，我假设你已经在conn变量
中连接
 PrepararedStatment statement = con.prepareStatement（queryString）; 
 
 //几乎在那里，需要设置参数
 // sql查询分解成JRQuery里的块。这些块有
 //，它们是text，parameter或parameter子句。我们关心参数，
 //不知道什么参数子句是诚实的
 int index = 0; //这是在语句
中为（JRQueryChunk chunk：query.getChunks（））设置参数的索引{
 if（chunk.getType（）== JRQueryChunk .TYPE_PARAMETER）{
 statement.setObject（index，params.get（chunk.getText（）））; 
 index = index + 1; 
} 
} 
 //然后执行查询
 ResultSet results = statement.executeQuery（）; 
  

注意：这里没有检查错误，您应该添加。也不知道如果这样做是一个好主意。将查询从报表中移除到您的java代码中可能会更好。然后只需将ResultSet作为数据源传递，您就可以走了。

in my Java project I have a lot of JasperReports reports with complex SQL queries, containing a lot of parameters. The reports are used to produce pdf documents containing the data returned by the query, grouped and formatted in various ways.

Now I also have the need to export directly the query result (e.g. a ResultSet, or a Map or a csv file, or similar...). Is it possible to ask JasperReports to execute only the query and return results instead of rendering the pdf page?

(NOTE: it's not the same as choosing a csv output format to the report rendering, because this method tries to convert the report design to a csv file... Instead, I'd like only to "reuse" the query inside a report, also taking advantage of JR parameters management, etc...)

This is my Java code to produce a pdf document from a report:

JasperReport report = (JasperReport) JRLoader.loadObject(inStream);
JasperPrint jasperprint = JasperFillManager.fillReport(report, params, conn);
JRAbstractExporter exporter = new JRPdfExporter();
exporter.exportReport();
ByteArrayOutputStream os = (ByteArrayOutputStream) exporter.getParameter(JRExporterParameter.OUTPUT_STREAM);
byte[] formattedReportBytes = os.toByteArray();
return formattedReportBytes;

I saw there's a class called JRJdbcQueryExecuter inside JasperReports... Is it possible to call it directly instead of calling fillReport, in order to get the ResultSet of the executed SQL query?

Thanks

解决方案

I would like to start with that this feels wrong and hacky, but it is possible, minus actually having JasperReports executing the query.

JasperReport report = (JasperReport) JRLoader.loadObject(inStream);

//this is the actual query in the report
JRQuery query = report.getMainDataSet().getQuery;

//once here you get the entire sql string, this will have any parameters replaced with 
//the '?' character
String queryString = query.getText();

//now start building your prepared statement, I am assuming you already have your
//connection in the conn variable
PrepararedStatment statement = con.prepareStatement(queryString);

//almost there, need to set the parameters
//the sql query is broke up into chunks inside the JRQuery. The chunks have types 
//that are  either text, parameter, or parameter clause. We care about parameter, 
//not sure what parameter clause would be to be honest
int index = 0; //this is the index to set the parameter at in the statement
for (JRQueryChunk chunk : query.getChunks()){
     if (chunk.getType() == JRQueryChunk .TYPE_PARAMETER){
         statement.setObject(index, params.get(chunk.getText()));
         index = index + 1;
     }
}
//then execute the query
ResultSet results = statement.executeQuery();

Note: There is no error checking here, and you should add that. Also not sure if doing this is a great idea. It could be better to move the queries out of the reports and into your java code altogether. Then just pass in the ResultSet as a datasource and you are good to go.

这篇关于导出JasperReports查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！