JasperReports 4.6.0 - Query Sample

JasperReports Ultimate Guide - Sample Reference - Schema Reference - Configuration Reference - API (Javadoc)

JasperReports - Query Sample (version 4.6.0)

Shows how the report query can be build dynamically using report parameters.

Download All Sample Source Files
Browse Sample Source Files on SVN


Main Features in This Sample
	Parameterized Queries (Dynamic Queries)


top

	Parameterized Queries (Dynamic Queries)		Documented by Luke Shannon , Sanda Zaharia

	Description / Goal	How to pass parameter references to report queries and how to change the report query at runtime.
	Since	0.1.0

	One of the most powerful features in the JasperReports API, when running JDBC based reports, is the ability to perform complex manipulations of the report query during the Filling stage of the report life cycle (this is what the JasperReports API executes the query against the datasource getting back the data for the report). The API offers three powerful tools for query manipulation that are reviewed in this sample. We will be discussing each of these in this document. Also demonstrated in this sample is how to use the Background band for adding a watermark style background image. Before we go further we should discuss the concept of a DataSet. A DataSet is an element of the report template and it the combination of a report data source (JDBC in this case), parameters (object references that are passed into the report-filling operations by the parent application), fields (maps data from the data source into the report template), variables (objects built on top of a report expression that perform various calculations) and groups (covered in the groups sample). All report templates (JRXML) implicitly declare and use a main dataset. The main dataset is responsible for iterating through the data source records, calculating variables, filtering out records, and estimating group breaks during the report-filling process. In the case of a JDBC based report, the Fields map to the columns coming back from the query. Modifications to the query allows for fundamentally changes to the data the report works with. Using Parameters to do this allows us to use information gathered from the parent application, which in turn could come from a report user (example: A user may provide a start and end date for which they want the data the report show to occur within). Running the Sample Prerequisites Ant is required. By running 'ant --version' you will be able to check if ant is set up on your system (at least version 1.5 is required): C:\>ant -version Apache Ant version 1.8.0 compiled on February 1 2010 You can obtain ant from http://ant.apache.org/, instructions for installation can be found there as well. Starting the Data Source In a command prompt/terminal window browse to the demo/hsqldb folder of the JasperReports source and run the command 'ant runServer'. Leave window/terminal running (see below for sample output). C:\js-workspace\jasperreports\demo\hsqldb>ant runServer Buildfile: C:\js-workspace\jasperreports\demo\hsqldb\build.xml runServer: [java] [Server@83cc67]: [Thread[main,5,main]]: checkRunning(false) entered [java] [Server@83cc67]: [Thread[main,5,main]]: checkRunning(false) exited [java] [Server@83cc67]: Startup sequence initiated from main() method [java] [Server@83cc67]: Loaded properties from [C:\js-workspace\jasperreports\demo\hsqldb\server.properties] [java] [Server@83cc67]: Initiating startup sequence... [java] [Server@83cc67]: Server socket opened successfully in 19 ms. [java] [Server@83cc67]: Database [index=0, id=0, db=file:test, alias=] opened sucessfully in 1104 ms. [java] [Server@83cc67]: Startup sequence completed in 1125 ms. [java] [Server@83cc67]: 2010-03-10 11:32:30.423 HSQLDB server 1.8.0 is online [java] [Server@83cc67]: To close normally, connect and execute SHUTDOWN SQL [java] [Server@83cc67]: From command line, use [Ctrl]+[C] to abort abruptly Generating the Report Open up a separate command prompt/terminal window and browse to the root directory of the sample. By running 'ant -p' you will be presented with a list of options available. Of interest in this list is all the exporters available for testing. Each export type will generate a output type in the build/report folder. By running the command 'ant' the following actions will be performed: All java code will be compiled to produce class files. JRXML fills will be compiled by JasperReports to produce a .jasperfile (this is a serialized version of a JasperReports object). The report will be filled with data and the resulting object, JasperPrint, will be serialized to the file system as a .jrprint. All the exporters the sample is configured to test will run. You can now run 'ant view' to see a version of the report in the JasperViewer (an applet contained in the JasperReports package which can be used to view a .jrprint object). Each of the these task can be ran separately as well: ant clean - removes all generated files. ant javac - compiles all java code, this should be done before running further tasks. ant compile - compiles the JRXML generating a .jasper file. ant fill - fills the report with data, some reports use the empty data source, others use the HSQL DB and other an inline data source. A .jrprint object is generated in this step. ant view - opens the report in the JasperViewer ant pdf - generates a PDF (other exporters are available run 'ant -p' for a full list) Note: All generated files can be found in build/reports. You now have a working version of the report you can review. Tools for Manipulating the Query $P{} Using $P{} in the report query is for situations where the query is fixed at design time and you only wish to inject values into the query before it is executed. The example does not illustrate this concept, however the $X{} explained shortly works with a similar concept. When using $P{} you do something like the following: SQL query string: SELECT * FROM Address WHERE city = $P{customerId} If we changed the query in this way and turned on Debugging for the JRJdbcQueryExecuter in an application running this report, we would get an output like this (the hosting application also collected the value for the parameter and supplied it to JasperReports when it was time to Fill the report): 2010-03-11 12:47:53,648 DEBUG JRJdbcQueryExecuter,http-8080-5:155 - SQL query string: SELECT * FROM Address WHERE city = ? 2010-03-11 12:47:53,660 DEBUG JRJdbcQueryExecuter,http-8080-5:252 - Parameter #1 (city of type java.lang.String): New York In this case the query as seen above and the parameter are passed to the database via the JDBC Driver (MySQL in this example) to be executed. As report developers we don't have to worry about adding quotes around the String value for city in the query as that will be done for us. This illustrates one way of injecting values into the query. $P!{} Using $P!{} allows you to modify the query syntax itself. The query in the sample uses this: SELECT * FROM Address WHERE $X{NOTIN, City, ExcludedCities} ORDER BY $P!{OrderClause} If we run the report in an application and collect values for the parameters (OrderBy was given the value 'LastName') we will see an output like this: 2010-03-11 13:01:05,818 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT * FROM Address WHERE City NOT IN (?) ORDER BY LastName 2010-03-11 13:01:05,821 DEBUG JRJdbcQueryExecuter,http-8080-4:303 - Parameter #1 (ExcludedCities[0] of type java.lang.String): New York Here we can see the value of $P!{OrderClause} was added into the query directly by JasperReports. For this reason, when working with $P!{} you must ensure any values collected will not result in a syntax error in the query as they will be inserted directly into the query. However this does give us the power to modify the query entirely. For example we could have set the whole 'ORDER BY' clause using $P!{}, or chosen to omit it entirely. $X{} There are also cases when just using `$P{}` in the report query is not enough, because parts of the query need to be dynamically built, depending on one or more report parameter values, in order to construct a valid query. The most common case is the `<column_name> = $P{<param_name>}` equality clause. When the value of the parameter is null, the expression `<column_name> = NULL` becomes invalid and it has to be replaced with `<column_name> IS NULL`. Another case comes with `IN` and `NOT IN` query clauses that need to use a collection report parameter as a list of values, unavailable for the simple `$P{}` syntax. Such complex query clauses are introduced into the query using the $X{} syntax. The general form of a `$X{}` clause is `$X{functionName, param1, param2,...}`. Similar to the `$P{}` explanation above, `$X{}` results in `?` being added to the query before submitting it to the DB. Also submitted are the values collected leaving it to the JDBC driver to add the values in and ensure the syntax of the query is correct. Built-in SQL Clause Functions As shown above, complex queries generation might depend on parameter values. JasperReports provides built-in support for several SQL clause functions which require some additional processing: The `$X{IN, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `IN`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value list. The value of this parameter has to be an array, a `java.util.Collection` or `null`. If the parameter's value is a collection of not null values, the function constructs a `<column_name> IN (?, ?, .., ?)` clause If the parameter's value is a collection containing both null and not null values, the function constructs a `(<column_name> IS NULL OR <column_name> IN (?, ?, .., ?))` clause If the parameter's value is a collection containing only null values, the function constructs a `<column_name> IS NULL` clause If the parameter's value is null, the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). The `$X{NOTIN, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `NOTIN`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value list. The value of this parameter has to be an array, a `java.util.Collection` or `null`. If the parameter's value is a collection of not null values, the function constructs a `<column_name> NOT IN (?, ?, .., ?)` clause If the parameter's value is a collection containing both null and not null values, the function constructs a `(<column_name> IS NOT NULL AND <column_name> NOT IN (?, ?, .., ?))` clause If the parameter's value is a collection containing only null values, the function constructs a `<column_name> IS NOT NULL` clause If the parameter's value is null, the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). Since JasperReports v4.0.1, the following built-in clause functions are also available: The `$X{EQUAL, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `EQUAL`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value to compare to. If the parameter's value is not null, the function constructs a `<column_name> = ?` clause. If the parameter's value is null, the function generates a `<column_name> IS NULL` clause. The `$X{NOTEQUAL, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `NOTEQUAL`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value to compare to. If the parameter's value is not null, the function constructs a `<column_name> <> ?` clause. If the parameter's value is null, the function generates a `<column_name> IS NOT NULL` clause. The `$X{LESS, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `LESS`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value to compare to. If the parameter's value is not null, the function constructs a `<column_name> < ?` clause. If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). Note: If the comparison with null (which always return `false`) should not be ignored, then one can use the `<column_name> < $P{<parameter_name>}` instead. The `$X{LESS], <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `LESS]`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value to compare to. If the parameter's value is not null, the function constructs a `<column_name> <= ?` clause. If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). Note: If the comparison with null (which always return `false`) should not be ignored, then one can use the `<column_name> <= $P{<parameter_name>}` instead. The `$X{GREATER, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `GREATER`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value to compare to. If the parameter's value is not null, the function constructs a `<column_name> > ?` clause. If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). Note: If the comparison with null (which always return `false`) should not be ignored, then one can use the `<column_name> > $P{<parameter_name>}` instead. The `$X{[GREATER, <column_name>, <parameter_name>}` clause function The function expects three mandatory clause tokens: The first token represents the function ID and always takes the fixed value `[GREATER`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the report parameter that contains the value to compare to. If the parameter's value is not null, the function constructs a `<column_name> >= ?` clause. If the parameter's value is null, the comparison with null will be neglected and the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). Note: If the comparison with null (which always return `false`) should not be ignored, then one can use the `<column_name> >= $P{<parameter_name>}` instead. The `$X{BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>}` clause function The function expects four mandatory clause tokens: The first token represents the function ID and always takes the fixed value `BETWEEN`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the parameter that contains the left member value. The fourth token is the name of the parameter that contains the right member value. If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where both interval endpoints are excluded: `(<column_name> > ? AND <column_name> < ?)` If the left parameter's value is null, the function constructs a `<column_name> < ?` clause, using the right parameter's value at fill time. If the right parameter's value is null, the function constructs a `<column_name> > ?` clause, using the left parameter's value at fill time. If both parameter values are null, the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). The `$X{[BETWEEN, <column_name>, <left_parameter_name>, <right_parameter_name>}` clause function The function expects four mandatory clause tokens: The first token represents the function ID and always takes the fixed value `[BETWEEN`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the parameter that contains the left member value. The fourth token is the name of the parameter that contains the right member value. If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where the left interval endpoint is included and the right endpoint is excluded: `(<column_name> >= ? AND <column_name> < ?)` If the left parameter's value is null, the function constructs a `<column_name> < ?` clause, using the right parameter's value at fill time. If the right parameter's value is null, the function constructs a `<column_name> >= ?` clause, using the left parameter's value at fill time. If both parameter values are null, the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). The `$X{BETWEEN], <column_name>, <left_parameter_name>, <right_parameter_name>}` clause function The function expects four mandatory clause tokens: The first token represents the function ID and always takes the fixed value `BETWEEN]`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the parameter that contains the left member value. The fourth token is the name of the parameter that contains the right member value. If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where the left interval endpoint is excluded and the right endpoint is included: `(<column_name> > ? AND <column_name> <= ?)` If the left parameter's value is null, the function constructs a `<column_name> <= ?` clause, using the right parameter's value at fill time. If the right parameter's value is null, the function constructs a `<column_name> > ?` clause, using the left parameter's value at fill time. If both parameter values are null, the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). The `$X{[BETWEEN], <column_name>, <left_parameter_name>, <right_parameter_name>}` clause function The function expects four mandatory clause tokens: The first token represents the function ID and always takes the fixed value `[BETWEEN]`. The second token is the SQL column (or column combination) to be used in the clause. The third token is the name of the parameter that contains the left member value. The fourth token is the name of the parameter that contains the right member value. If both parameter values are not null, the function constructs a double comparison, similar to the BETWEEN SQL clause where both interval endpoints are included: `(<column_name> >= ? AND <column_name> <= ?)` If the left parameter's value is null, the function constructs a `<column_name> <= ?` clause, using the right parameter's value at fill time. If the right parameter's value is null, the function constructs a `<column_name> >= ?` clause, using the left parameter's value at fill time. If both parameter values are null, the function generates a SQL clause that will always evaluate to `true` (e.g. `0 = 0`). In this sample the query string contains a `$X{NOTIN, City, ExcludedCities}` piece of code: <parameter name="ExcludedCities" class="java.util.Collection"/> <parameter name="OrderClause" class="java.lang.String"/> <queryString><![CDATA[SELECT * FROM Address WHERE $X{NOTIN, City, ExcludedCities} ORDER BY $P!{OrderClause}]] ></queryString> If we run the report again and pass two values into $P{ExcludedCities}: 2010-03-11 13:25:23,300 DEBUG JRJdbcQueryExecuter,http-8080-4:155 - SQL query string: SELECT * FROM Address WHERE City NOT IN (?, ?) ORDER BY LastName 2010-03-11 13:25:23,302 DEBUG JRJdbcQueryExecuter,http-8080-4:303 - Parameter #1 (ExcludedCities[0] of type java.lang.String): New York 2010-03-11 13:25:23,302 DEBUG JRJdbcQueryExecuter,http-8080-4:303 - Parameter #2 (ExcludedCities[1] of type java.lang.String): Boston Creating Watermarks with the Background Band When working with JasperReport templates it is best to avoid overlap of elements otherwise you get unexpected results in many of our exporters. An exception to this rule in the Background band. Content of the pages in the filled report will be overlaid on top of elements placed in the Background band (this will be the case of each page in the generated report). This is why the band is a full page size in the design and contains an image the same height. The result is to have this image displayed on each page of the generated report. TIP: As is the example it is better to have more 'faded' images or text as to not distract the viewer from the main content of the report. Here we can see how the band can be configured to have an image occur in a specific position on each of the rendered pages of the report: <background> <band height="742"> <image scaleImage="Clip" hAlign="Left" vAlign="Bottom"> <reportElement x="0" y="0" width="150" height="742"/> <imageExpression class="java.lang.String"><![CDATA["jr.watermark.gif" ] ]></imageExpression> </image> </band> </background> Further Resources: JasperReports Ultimate Guide (available from the JasperSoft eShop) iReport Ultimate Guide (available from the JasperSoft eShop)