JasperReport : Dynamic SQL Query to inside <queryString> Tag of jrxml file

[Ravi Kiran Va]

Hi ,

The below is part of my sample MyReport.Jrxml File .
Here as you can see the sql query is static .

<jasperReport name="sample_report" >
<queryString>
<![CDATA[select Empname , Empsal from Employees
where Deptno <=100]]>
</queryString>

How can i make it dynamic , so that i want to pass this value dynamically from my Java Program .
Please guide me .
Please tell me what should be appended inside the queryString Tag so to make it dynamic ??

Thanks .

[bikashmca kr]

You can use parameter for this purpose, like for ACTUAL_DATE, following is the syntax.

in sql query you can write, you need to create input control in the jasperserver also while uploading this jrxml.

where date = $P{ACTUAL_DATE}

<parameter name="ACTUAL_DATE" class="java.util.Date">
<defaultValueExpression><![CDATA[(new java.util.Date())]]></defaultValueExpression>
</parameter>

<parameter name="STRING_PARAMETER" class="java.lang.String">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>

[guntur reddy]

se this example it is usefull

The only change we need to make on the JRXML template is to remove the <queryString> element.
To fill the report, we need to execute a database query in our Java code, and pass the query results to the report in a datasource, as can be seen in the following example:

package net.ensode.jasperbook;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
public class DbReportDSFill
{
Connection connection;
Statement statement;
ResultSet resultSet;
public void generateReport()
{
try
{
String query = "select a.tail_num, a.aircraft_serial, "
+ "am.model as aircraft_model, ae.model as engine_model from
aircraft a, "
+ "aircraft_models am, aircraft_engines ae where a.aircraft_
engine_code in ("
+ "select aircraft_engine_code from aircraft_engines "
+ "where horsepower >= 1000) and am.aircraft_model_code =
a.aircraft_model_code "
+ "and ae.aircraft_engine_code = a.aircraft_engine_code";
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection ("jdbc:mysql://
localhost:3306/flightstats?user=user&password=secret");
statement = connection.createStatement();
resultSet = statement.executeQuery(query);
JRResultSetDataSource resultSetDataSource = new
JRResultSetDataSource(resultSet);
System.out.println("Filling report...");
JasperFillManager.fillReportToFile("reports/DbReportDS.jasper",
new HashMap(), resultSetDataSource);
System.out.println("Done!");
resultSet.close();
statement.close();
connection.close();
}
catch (JRException e)
{
e.printStackTrace();
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
public static void main(String[] args)
{
new DbReportDSFill().generateReport();
}
}

I think it is useful

Thanks
venu