Export JasperReports query results

Question

in my Java project, I have many JasperReports reports with complex SQL queries containing many parameters. Reports are used to create pdf documents containing data returned by the request, grouped and formatted in various ways.

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

(NOTE: this is not the same as choosing the csv output format for rendering the report, since this method tries to convert the report project to a csv file ... Instead, I would only like to “reuse” the request inside the report, also using JR parameter management etc.)

This is my Java code to create a pdf 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 a class called JRJdbcQueryExecuter inside JasperReports ... Is it possible to call it directly, instead of calling fillReport , to get the ResultSet from the executed SQL query?

thanks

Answer 1

I would like to start by saying that this seems wrong and hacked, but it is possible, minus, in fact, when JasperReports executes the request.

 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 are no errors here, and you should add this. Also not sure what to do is a great idea. It would be better to transfer requests from reports to your Java code in general. Then just go to the ResultSet as the data source, and you're good to go.