Passing SQL query as a parameter

0

Hi All,

I have a developed a Java console app which is connected to a datasource and generating a PDF from a .jrxml file(which was created using JasperStudio).

Here is the program:

public class JavaCallJasperReport {
    public static void main(String[] args) throws JRException,
    ClassNotFoundException, SQLException {

String reportSrcFile = "C:/jasperreport/CustomerDataReport/CustomerData.jrxml";
 
JasperReport jasperReport =    JasperCompileManager.compileReport(reportSrcFile);

Connection conn = SQLServerConnUtils.getSQLServerConnection_SQLJDBC();

// Parameters for report
Map<String, Object> parameters = new HashMap<>();
Connection conn = SQLServerConnUtils.getSQLServerConnection_SQLJDBC();
JasperPrint print = JasperFillManager.fillReport(jasperReport, parameters, conn);


File outDir = new File("C:/jasperoutput");
outDir.mkdirs();

// PDF Exporter.
JRPdfExporter exporter = new JRPdfExporter();

ExporterInput exporterInput = new SimpleExporterInput(print);
// ExporterInput
exporter.setExporterInput(exporterInput);

// ExporterOutput
OutputStreamExporterOutput exporterOutput = new SimpleOutputStreamExporterOutput(
        "C:/jasperoutput/CustomerDataReport.pdf");
.................

Now, I want to pass an sql query say a select statement so that the PDF report generated should have only the values returned by the SQL query.

I am using:

parameters.put("QUERY", "Select c.FirstName, c.CompanyName from SalesLT.Customer c where c.CustomerID = 1");

but this is not working. 

Please help me.

Thanks in advance.

sumukha.prasanna's picture
Joined: Sep 20 2017 - 7:01am
Last seen: 1 year 8 months ago

1 Answer:

1

Jasper supports $P!{PARAM} to hold the SQL inside a parameter and pass it to the report datasource.

Check this: http://community.jaspersoft.com/questions/529203/pparam-vs-pparam

If I'm understanding your question correctly, jasper does not support dynamic SQL queries. (it cannot detect the field changes at runtime and modify the jrxml by itself)

For example : you can pass "select column1, column2 from table where condition = 1" as a parameter, but the same fields should be evaluated inside the jrxml beforehand.

lets say if you pass "select column3, column4 from table where condition = 1" as a parameter into the above mentioned report, it would fail because the evaluated fields column1 and column2 could not be found at runtime.

reportdev's picture
1224
Joined: Oct 12 2015 - 12:05pm
Last seen: 2 months 1 week ago
Feedback
randomness