Package net.sf.jasperreports.engine.query

Provides support for report queries and query executers.

Report SQL Queries

To fill a report, provide the reporting engine with the report data, or at least instruct it how to get this data.

JasperReports normally expects to receive a JRDataSource object as the report data source, but it has also been enhanced to work with JDBC so that it can retrieve data from relational databases if required.

The library allows the report template to specify the SQL query for report data if this data is located in relational databases. The SQL query specified in the report template is taken into account and executed only if a java.sql.Connection object is supplied instead of the normal JRDataSource object when filling the report.

This query can be introduced in the JRXML report template using the <queryString> element. If present, this element comes after the report parameter declarations and before the report fields.

Report parameters in the query string are important to further refine the data retrieved from the database. These parameters can act as dynamic filters in the query that supplies data for the report. Parameters are introduced using a special syntax, similar to the one used in report expressions.

There are three possible ways to use parameters in the query, described below:

  • $P{paramName} Syntax - the parameters are used like normal java.sql.PreparedStatement parameters
  • $P!{paramName} Syntax - the parameters are used to dynamically modify portions of the SQL query or to pass the entire SQL query as a parameter to the report-filling routines. The value supplied for those parameters replace the parameter references in the query, before it is sent to the database server using a java.sql.PreparedStatement object.
  • $X{functionName, param1, param2,...} Syntax - There are also cases when a part of the query needs to be dynamically built starting from a report parameter value, with the query part containing both query text and bind parameters. This is the case, for instance, with IN and NOT IN query clauses that need to use a collection report parameter as a list of values. Such complex query clauses are introduced into the query using the $X{} syntax.
In the majority of cases, the SQL query text placed inside a report template is a SELECT statement. JasperReports uses a java.sql.PreparedStatement behind the scenes to execute that SQL query through JDBC and retrieve a java.sql.ResultSet object to use for report filling. However, the SQL query string might also contain stored procedure calls.

Certain conditions must be met to put stored procedure calls in the SQL query string of a report template:

  • The stored procedure must return a java.sql.ResultSet when called through JDBC.
  • The stored procedure cannot have OUT parameters.

Query Executers

Report data can be produced by specifying queries in languages other than SQL. Each query language is associated a query executer factory implementation. JasperReports has built-in query executer implementations for SQL, Hibernate 3, XPath, EJB-QL, CSV and Excel queries.

The query language is specified in JRXML using the language attribute of the <queryString> tag. Using the API, the query language is set by setLanguage(String). The default language is SQL, thus ensuring backward compatibility for report queries that do not specify a query language.

To register a query executer factory for a query language, one has to define a JasperReports property named net.sf.jasperreports.query.executer.factory.<language>. The same mechanism can be used to override the built-in query executers for a query language, for instance to use a custom query executer for SQL queries.

The API for query executers involves an executer factory interface, a query executer interface, implementations of these interfaces, and JRDataSource implementations.

QueryExecuterFactory is a factory interface used to query executers for a specific language and to provide information regarding the connection parameters required by the query executer to run the query. It has the following methods:

  • public JRQueryExecuter createQueryExecuter(JasperReportsContext jasperReportsContext, JRDataset dataset, Map<String,? extends JRValueParameter> parameters) - This method creates a query executer. The dataset includes the query string and the fields that will be requested from the data source created by the query executer. The parameters map contains parameter types and runtime values to be used for query parameters. This method usually sends the dataset and parameters map to the created query executer.
  • public Object[] getBuiltinParameters() - This method returns parameters that will be automatically registered with a report/dataset based on the query language. These parameters will be used by query executers as the context/connection on which to execute the query. For instance, the Hibernate query executer factory specifies a HIBERNATE_SESSION parameter of type org.hibernate.Session whose value will be used by the query executer to run the query.
  • public boolean supportsQueryParameterType(String className) - This method is used on report validation to determine whether a query parameter type (for a parameter specified in the query using $P{..}) is supported by the query executer implementation.
A JRQueryExecuter is responsible for running a query, creating a data source out of the result, and closing the result. It includes these methods:
  • public JRDataSource createDatasource() - This method processes and runs the query and creates a data source out of the query result. Usually, the required data (query string and parameter values) is made available to the query executer by the factory on creation.
  • public void close() - This method closes the query execution result and any other resource associated with it. It is called after all data produced by the query executer has been fetched.
  • public boolean cancelQuery() - This method is called when the user decides to cancel a report fill process. The implementation should check whether the query is currently being executed and ask the underlying mechanism to abort the execution. The method should return true if the query was being executed and the execution was canceled. If execution abortion is not supported, the method will always return false.
Query executer implementation can benefit from using JRAbstractQueryExecuter as a base. The abstract base provides query parameter processing functionality and other utility methods.

In most cases, a query executer needs a new JRDataSource implementation to wrap its specific query results. Still, in some of the cases, query executers can use existing JRDataSource implementations.

Note that registering new query executer implementations by adding properties in the jasperreports.properties file, as mentioned above, is only one way of registering the executers. They can be registered in a more transparent way by using the JasperReports extension support. One ore more query executer implementations can be packaged in a query executer bundle that can be deployed as a single JAR file. This approach obviates the need to modify existing application files. The query executer extension point in JasperReports is represented by the DefaultQueryExecuterFactoryBundle final class.

SQL Query Executer

The SQL query executer is a JDBC-based executer for SQL queries.

The SQL query executer factory does not register any parameter as the REPORT_CONNECTION parameter is kept in all reports for backward compatibility. The SQL query executer uses this parameter to retrieve a java.sql.Connection object. The query executer creates a JRResultSetDataSource data source to wrap the JDBC result set.

Aborting the currently running query is supported using java.sql.PreparedStatement.cancel() when running a report asynchronously by using FillHandle.cancellFill(). Alternatively, the query timeout of the JDBC statement used by the query executer can be set to cancel the query after a certain amount of time by using the net.sf.jasperreports.jdbc.query.timeout configuration property at the report level or globally.

The fetch size of the JDBC statement used by the query executer behind the scenes can be set using the net.sf.jasperreports.jdbc.fetch.size configuration property at report level or globally.

XPath Query Executer

The XPath query executer permits reports using XML data sources to specify the XPath that produces the list of nodes/records as the report query.

The query executer factory registers a parameter named XML_DATA_DOCUMENT of type org.w3c.dom.Document. The query executer will run the XPath query against this document and produce a JRXmlDataSource data source.

Parameters are supported in the XPath query. All parameters will be replaced in the query string by their java.lang.String values. This query executer recognizes four additional parameters that serve for localization purposes when creating the JRXmlDataSource instance:

Hibernate Query Executer

JasperReports includes support for Hibernate 3 in the form of a query executer. This allows users to specify in a report an HQL query that should be used to retrieve report data.

For reports having an HQL query, the executor factory will automatically define a parameter named HIBERNATE_SESSION of type org.hibernate.Session. Its value will be used by the query executor to create the query.

Like SQL queries, HQL queries can embed two types of parameters:

  • using $P{..} syntax - These parameters are used as named parameters of the Hibernate query.
  • using $P!{..} syntax - The java.lang.String value of the parameter is substituted as-is in the query string before creating the Hibernate query. This type of parameter can be used to dynamically specify query clauses/parts.
The result of a Hibernate query can be obtained in several ways. The Hibernate query executer chooses the way the query result will be produced based on a property named net.sf.jasperreports.hql.query.run.type. The run type can be one of the following:
  • list - The result is fetched using org.hibernate.Query.list(). The result rows can be fetched all at once or in fixed-sized chunks. To enable paginated result row retrieval, the net.sf.jasperreports.hql.query.list.page.size configuration property should have a positive value.
  • scroll - The result is fetched using org.hibernate.Query.scroll().
  • iterate - The result is fetched using org.hibernate.Query.iterate().
The fetch size of the query can be set using the net.sf.jasperreports.jdbc.fetch.size configuration property at report level or globally.

However, when dealing with large amounts of data, using pagination is the most common way to present the document content. In this case, it is necessary to clear Hibernate's first-level cache after each page fetching, otherwise Hibernate will eventually cause an OutOfMemory error. If the Hibernate's session cache is regularly cleared, the memory trap can be avoided. Because flushing data and clearing the cache is a time-consuming process, you should use it only if really huge datasets are involved. This is why the net.sf.jasperreports.hql.clear.cache property was introduced. Normally, it defaults to false. If set to true, the periodic Hibernate session cache cleanup is performed after each page fetching.

A report/dataset field is mapped to a value from the Hibernate query result either by its description or its name. By default, the program uses the report field name, but the report field description property can be used instead if the net.sf.jasperreports.hql.field.mapping.descriptions configuration property is set to true either in the report template or globally.

The mappings are similar to the ones used by JavaBeans data sources, except that select aliases are used when queries return tuples instead of single objects.

MDX Query Executer

Reporting on OLAP data is supported in JasperReports via the MDX query executer and a data source that use the Mondrian API's (this is why often we refer to this query executer also as the Mondrian query executer). Users can create reports with MDX queries and map report fields onto the OLAP result; the engine will execute the query via Mondrian and pass the result to a data source implementation, which will be used to fill the report.

The Mondrian query executer is registered by default for queries having MDX or mdx as the language specified in the report template. One can use JasperReports configuration properties to register additional or alternative query language to query executer mappings

The Mondrian query executer requires a single connection parameter named MONDRIAN_CONNECTION of type mondrian.olap.Connection.

MDX queries can contain placeholders for parameters of any type. When the query gets executed, each parameter placeholder will be replaced in the query string by its toString() value. Therefore, for MDX queries, $P{...} parameters are equivalent to $P!{...} query fragments.

The Mondrian query executer passes the query result to a JRMondrianDataSource, which will be used to iterate the result and map values from the result to the report fields. The field mapping deals with mapping values from the OLAP result to the report fields. As an OLAP result has a multidimensional and hierarchical structure while a JasperReports data source has a tabular structure, mapping values to fields is not a trivial task.

A special syntax is used to specify what value should be mapped to a field. The field description is used to hold the mapping specification.

Using the mapping syntax, one can map two types of values from the OLAP result:

  • Member values are names or properties of members of the result axes.
  • Data/measure values are cell values from the result.
The Mondrian data source performs a traversal of the OLAP result by iterating the members of the result axes. On every step, each field is checked for whether its mapping matches the current position in the OLAP result. If so, the value is extracted from the result and set to the field.

XML/A Query Executer

MDX queries can also be executed on remote OLAP data sources via the XML for Analysis interface. This functionality is implemented in JasperReports as a query executer.

Just like the Mondrian query executer presented in the previous section, the XML/A query executer is also mapped by default to the MDX and mdx query languages, but the Mondrian query executer takes precedence.

The dispatch between the two query executers that are mapped on the same query language is done by a special query executer implementation. It is actually the JRMdxQueryExecuterFactory class that is registered by default with the MDX and mdx query languages, and it delegates the creation of the query instances at runtime to either the JRMondrianQueryExecuterFactory or the JRXmlaQueryExecuterFactory, depending on the specific parameter values that are passed in at report-filling time. It first checks for the MONDRIAN_CONNECTION parameter, and if found, the Mondrian query executer takes over. If this parameter is not found, it then checks for the PARAMETER_XMLA_URL to see if the XMLA query executer can be used. In fact, there are 3 possible connection parameters for the XML/A query executer:

  • XMLA_URL - a java.lang.String value representing the XMLA/SOAP service URL
  • XMLA_DATASOURCE - a java.lang.String value representing the information required to connect to the OLAP data source
  • XMLA_CATALOG - a java.lang.String value representing name of the OLAP catalog to use
The XMLA query executer creates a data source equivalent to the one created by the Mondrian query executer, with a few minor exceptions.

This means that the result cube traversal and field mapping logic available for the MDX query executer applies for the XMLA query executer as well.

The XMLA query executer lacks some of the functionality of the Mondrian query executer, due to inherent limitations of the XML for Analysis standard. The missing features are the following:

  • Mapping report fields to custom member properties does not work with XML/A
  • For XMLA, it is not possible to produce a complete mondrian.olap.Member object, hence this feature is not supported.
  • Parent member matching using the mondrian.olap.Member.getParent() method does nor work via XML/A, since the parent member information is not present in the response.

EJB-QL/JPA Query Executer

The EJB-QL report query executer adds support for reporting on EJB 3.0 persistent entities data. For an EJB-QL query in a report, the query executer will use the EJB 3.0 Java Persistence API to execute the query against an entity manager provided at runtime, and use the query result as a data source for the report.

The built-in EJB-QL query executer is registered by default for queries having EJBQL or ejbql as their language. This mapping can be changed by using JasperReports properties.

The EJB-QL query executer contributes built-in parameters to the report:

  • The entity manager to be used for executing the query
  • An optional query hints map
When the report template contains an EJB-QL query, one must provide a JPA entity manager at runtime; the query executer will run the query using the supplied entity manager. The entity manager is of type javax.persistence.EntityManager and should be provided via the JPA_ENTITY_MANAGER built-in parameter: 
   Map parameters = new HashMap();
   javax.persistence.EntityManager entityManager = createEntityManager();
   parameters.put( EjbqlConstants.PARAMETER_JPA_ENTITY_MANAGER, entityManager );
   JasperFillManager.fillReport(jasperReport, parameters);
The means of getting hold of an entity manager depends on the particular EJB/JPA environment and implementation.

An additional parameter named JPA_QUERY_HINTS_MAP allows you to specify query hints for running the query. The parameter value should be a map containing hint values mapped to hint names. The hints are set using the

javax.persistence.Query.setHint(String hintName, Object value) method.

Hints can also be specified statically by using report properties. The query executer treats any report property starting with net.sf.jasperreports.ejbql.query.hint.<hintName> as a hint by interpreting the property suffix as the hint name and the property value as the hint value. Thus, if the following property is present in the report:

<property name="net.sf.jasperreports.ejbql.query.hint.cacheType" value="Shared"/>

then the cacheType hint having Shared as value will be set when running the query.

Note that only hints that accept String values can be set using this mechanism.

A separate report property can be used to paginate the query result. This property can be used for controlling the amount of Java heap space used by the query executer while filling the report. The property can be set in the following manner:

<property name="net.sf.jasperreports.ejbql.query.page.size" value="500"/>

The results of the query will be fetched in chunks containing 500 rows.

The pagination is achieved via the javax.persistence.Query.setMaxResults() and setFirstResult() methods. Obviously, using pagination could result in performance loss. Therefore enabling it is primarily recommended when the query results are very large.

EJB-QL report queries can contain parameters of any type. At runtime, the value of the parameter is directly set by using javax.persistence.Query.setParameter(String name, Object value), with no other processing.

The result of the query execution is sent to a JRJpaDataSource data source implementation, which iterates over it and extracts report field values. Fields are mapped to specific values in the query result by specifying the mapping as field description or field name. The JPA data source can handle two types of query results:

  • Queries returning a single entity/bean per row
  • Queries returning object tuples as rows
When the query returns a single entity/bean per row, as in

SELECT m FROM Movie m

or

SELECT NEW MovieDescription(m.title, m.gender) FROM Movie m

then the field mappings are interpreted as bean property names. The same conventions as for JavaBeans data sources are used.

When the query returns multiple objects per row, as in

SELECT m.title, m.gender FROM Movie m

then the fields are mapped using one of the following forms:

  • COLUMN_<index> - maps the field to a value specified by its position in the resulting tuple. The positions start from 1.
  • COLUMN_<index>.<property> - maps the field to a property of a value specified by its position in the resulting tuple.
For instance, the following mappings could be used for a query returning multiple objects per row: COLUMN_1, COLUMN_2, COLUMN_1.title, and COLUMN_2.movie.title.

Related Documentation

JasperReports Tutorial
See Also:
JRDataSource, JRResultSetDataSource, JRJpaDataSource, JRXmlDataSource, JRMdxQueryExecuterFactory, JRMondrianDataSource, JRMondrianQueryExecuterFactory, JRXmlaQueryExecuterFactory