I'm redoing reports in Jasper Reports here from the company. I am migrating to have an agnostic reporting service the structure of the report's SQL query. Before, the system was deeply aware of how such a query was made, but this ran into some of the limitations I have.
I need to query with a IN clause; for example, know the orders going to the US, Italy and Germany.
A case similar to what TIBCO itself put in documentation of parameters in queries
So my query would be this:
SELECT * FROM ORDERS WHERE SHIPCOUNTRY IN ('USA','Italy','Germany')
In JRXML, I had the query like this:
<parameter name="P_WHERE_CLAUSE" class="java.lang.String"/>
<queryString>
<![CDATA[
SELECT * FROM ORDERS $P!{P_WHERE_CLAUSE}
]]>
</queryString>
And in the report call, the old Java code looks 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();
But this model does not satisfy me. I build the (partially) external query to the JRXML itself and the execution of this query successfully depends on the caller to know the report in depth a priori.
As I said before, I am moving to a generic service to receive the parameters of an abstract caller, and then mount the report. I'm doing this service in Springboot, for other reports it 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 about where the request comes from or the parameters a priori , I can mount all the reports that do not need lists (passing the parameters as String). But I'm still not in the mood to send those lists.
Looking at the a>, apparently the $X expansion does what I need. My query would look like this:
SELECT * FROM ORDERS WHERE $X{IN, SHIPCOUNTRY, myCountries}
But I do not know how to declare the parameters in JRXML. In case, would it 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 in my parameter request? I would like to handle receiving application/x-www-form-urlencoded to avoid any problems with SOP, but I have not yet tested passing vectors through this format.