Passing SQL query parameters from Java

Support
#1

Hi,

Excuse me if this is a existing topic - I searched through the forum but couldn’t find anything - i don’t know the nomenclature enough to search for some keyword. As a matter of fact I am going through the examples in core\modules\core\test\sample

I just wanted to find out how I could pass a user parameter for the query in the report.

I get that I can either use the xml document with the query as shown below

<band name="Data" orientation="H">
<queries>
<query name="Data" type="sql">
	<script>
		select person_type as "person_type"
			from PERSON_TYPE 
			where email_id=${email_id}
	</script>
</query>
</queries>
</band>

and run it from java using the below code

Report report = new DefaultXmlReader().parseXml(readFileToString("working.xml"));
  
  Map<String, Object> params = new HashMap<String, Object>();
  params.put("email_id", new String("xxxxx@yyy.com"));
  Reporting reporting = new Reporting();
  reporting.setFormatterFactory(new DefaultFormatterFactory());
  reporting.setLoaderFactory(
  		new DefaultLoaderFactory().setSqlDataLoader(new SqlDataLoader(getOracleDataSource()).loadData(ReportQuery, BandData, params)));
  ReportOutputDocument output = reporting.runReport(new RunParams(report), new FileOutputStream("working.xlsx"));

Here I believe the SqlDataLoader.loadData(ReportQuery, BandData, params) is used to pass the parameter. In that case how would I get the BandData (parentBand) object and the ReportQuery object for the given Report?

I believe I can do the same thing exclusively in Java as shown below -

ReportBuilder reportBuilder = new ReportBuilder();
  ReportTemplateBuilder reportTemplateBuilder = new ReportTemplateBuilder()
          .documentPath("working.xlsx")
          .documentName("working.xlsx")
          .outputType(ReportOutputType.xlsx)
          .readFileFromPath();
  reportBuilder.template(reportTemplateBuilder.build());
  BandBuilder bandBuilder = new BandBuilder();
  ReportBand date= bandBuilder.name("Date")
          .query("Date", "select TO_CHAR(sysdate, 'MM/DD/YYYY') as \"my_date\" from dual", "sql")
          .build();
  reportBuilder.band(date);
  
  ReportBand header= bandBuilder.name("Header").build();
  reportBuilder.band(header);
  
  ReportBand data= bandBuilder.name("Data")
          .query("Data", "select person_type as \"person_type\" from PERSON_TYPE where email_id = ${email_id}", "sql")
          .build();
  reportBuilder.band(data);
  
  Report report = reportBuilder.build();
   
  Reporting reporting = new Reporting();
  reporting.setFormatterFactory(new DefaultFormatterFactory());
  reporting.setLoaderFactory(
          new DefaultLoaderFactory().setSqlDataLoader(new SqlDataLoader(getOracleDataSource())));
  ReportOutputDocument reportOutputDocument = reporting.runReport(new RunParams(report), new FileOutputStream("working.xlsx"));

Again how will I pass the email_id query parameter to the query?

#2

Simply edited the parsed xml string in the Java service layer based on user input.

Surely not the most elegant solution, works anyway.