6
I’m redoing some reports on Jasper Reports here at the company. I am migrating to have a reporting service agnostic the SQL query structure of the report. Before, the system knew deeply how such a query was made, but this ran into some limitations that I have.
I need to make an appointment with a clause IN; for example, knowing the orders going to the US, Italy and Germany.
A case similar to the one TIBCO itself put in documentation of parameters in queries
Then my consultation would be this:
SELECT * FROM ORDERS WHERE SHIPCOUNTRY IN ('USA','Italy','Germany')
In JRXML, I had the query this way:
<parameter name="P_WHERE_CLAUSE" class="java.lang.String"/>
<queryString>
<![CDATA[
SELECT * FROM ORDERS $P!{P_WHERE_CLAUSE}
]]>
</queryString>
And in the call to report, the old Java code was like this:
// public class RelatorioDownloadServlet extends HttpServlet {...
// ...
// protected void doPost(HttpServletRequest request,
// HttpServletResponse response) throws ServletException, IOException { ...
String whereClause = " ";
String shippingCountries = request.getParameter("shipping");
if (shippingCountries != null && !shippingCountries.isEmpty()) {
whereClause = " WHERE SHIPCOUNTRY IN (" + shippingCountries + ")";
}
HashMap<String, Object> parametros = new HashMap<String, Object>();
parametros.put("P_WHERE_CLAUSE", whereClause );
String templatePath = servletContext.getRealPath("shipping.jasper");
byte[] bytes;
try (Connection connection = getConnection()) {
bytes = JasperRunManager.runReportToPdf(templatePath, parametros, connection);
}
ServletOutputStream servletOutputStream = response.getOutputStream();
response.setContentType("application/pdf");
response.setContentLength(bytes.length);
servletOutputStream.write(bytes, 0, bytes.length);
servletOutputStream.flush();
Only that model doesn’t satisfy me. I build the (partially) external query to JRXML itself and the execution of this query successfully depends on the caller knowing the report in depth a priori.
As previously said, I am switching to a generic service of receiving the parameters of an abstract caller and then assembling the report. I’m doing this job in Springboot, for other reports he does the following:
@RestController
public class RelatorioController {
@Autowired HikariDataSource dataSource;
/**
* CompileJasperRecursive é uma classe minha, ela possui dois métodos principais:
* - getJasperReport: retorna um objeto JasperReport baseado no .jasper; não existindo o .jasper, compila-o chamando !compileIfShould"
* - compileIfShould: verifica se deve gerar um novo .jasper baseado no .jrxml; também tenta procurar recursivamente por subrelatórios e compilá-los com "compileIfShould"
*
* não vejo esse cara como o problema, ele só faz isso
*/
@Autowired CompileJasperRecursive compiler;
@RequestMapping(path = "/{relat}.pdf", method = RequestMethod.POST, produces = "application/pdf")
public void requisicaoPdf(@PathVariable("relat") String relat,
@RequestParam Map<String, String> params,
HttpServletRequest req, HttpServletResponse resp) throws JRException, SQLException, IOException {
compilaRelatPDF(relat, params, resp.getOutputStream());
resp.setHeader("Content-Disposition", "attachment; filename=" + relat + ".pdf");
}
private void compilaRelatPDF(String relat, Map<String, String> params, OutputStream outputStream) throws JRException, SQLException, IOException {
JasperReport jasperReport = compiler.getJasperReport("/path/relatorios/", relat + ".jrxml");
HashMap<String, Object> map = new HashMap<>();
map.putAll(params);
map.put("P_CAMINHO_SUB_RELAT", "/path/relatorios/"); // ocasional caminho dos subrelatórios
JasperPrint print = fillReport(jasperReport, map);
JRPdfExporter exporter = new JRPdfExporter();
exporter.setExporterInput(new SimpleExporterInput(print));
exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(outputStream));
SimplePdfReportConfiguration reportConfig = new SimplePdfReportConfiguration();
reportConfig.setSizePageToContent(true);
reportConfig.setForceLineBreakPolicy(false);
SimplePdfExporterConfiguration exportConfig = new SimplePdfExporterConfiguration();
exportConfig.setMetadataAuthor("Jeff Coelho Quesado");
exportConfig.setEncrypted(false);
exportConfig.setAllowedPermissionsHint("PRINTING");
exporter.setConfiguration(reportConfig);
exporter.setConfiguration(exportConfig);
exporter.exportReport();
}
}
In this scheme, without worrying where the request comes from nor the parameters a priori, I can assemble all reports that don’t need lists (passing the parameters as String). But I’m not at peace yet on the part of sending those lists.
Looking at the documentation and that issue here at Sopt, apparently the expansion $X would do what I need. My consultation would look like this:
SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}
But I don’t know how to declare the parameters in JRXML. In this case, it would look something like this?
<parameter name="myCountries" class="java.util.Collection"/>
<queryString>
<![CDATA[
SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}
]]>
</queryString>
And how would I handle the data of my request parameter? I would like to treat the receipt of application/x-www-form-urlencoded to avoid any problems with SOP, but I have not yet tested pass vectors through this format.