Skip navigation links

Package net.sf.jasperreports.engine.query

Provides support for report queries and query executers.

See: Description

Package net.sf.jasperreports.engine.query Description

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:

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:

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:

A JRQueryExecuter is responsible for running a query, creating a data source out of the result, and closing the result. It includes these methods: 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 QueryExecuterFactoryBundle interface.

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:

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: 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:

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:

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:

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:

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( JRJpaQueryExecuterFactory.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:

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:

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
Skip navigation links

Copyright © 2023 Cloud Software Group, Inc.. All rights reserved.