Logo Questions Linux Laravel Mysql Ubuntu Git Menu

Export JasperReports query results

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();
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?


like image 318
sigi_tm Avatar asked Mar 20 '12 15:03


1 Answers

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.

like image 69
Jacob Schoen Avatar answered Sep 20 '22 06:09

Jacob Schoen