JasperReports Ultimate Guide - Sample Reference - Schema Reference - Configuration Reference - API (Javadoc)
JasperReports - EJBQL Sample (version 5.6.0) |

Shows how EJBQL could be used in reports.
Download All Sample Source Files
Browse Sample Source Files on SVN

Main Features in This Sample
| EJBQL Query Executer
Secondary Features
| Query Executers

top
| EJBQL Query Executer | Documented by Sanda Zaharia
| Description / Goal |  | How to fill reports using embedded EJBQL queries.
| Since |  | 1.2.3
| Other Samples |  |
/demo/samples/csvdatasource
/demo/samples/hibernate
/demo/samples/mondrian
/demo/samples/xmldatasource
| The 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` set as their language. This mapping can be changed by using the related JasperReports properties (see properties in the category net.sf.jasperreports.query.executer.factory.{language} ). Two built-in parameters are involved in the query execution: PARAMETER_JPA_ENTITY_MANAGER - that specifies the entity manager to be used for executing the query, depending on the particular EJB/JPA environment and implementation JPA_QUERY_HINTS_MAP - that contains a map with hint values mapped on hint names, to be used when running the query. Hints can also be specified statically by using report properties. The query executer treats any report property in the category `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. An example of query hint property is the following:
    <property name="net.sf.jasperreports.ejbql.query.hint.cacheType" value="Shared"/>
A separate report property can be used to paginate the query result in order to control 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"/>
- - meaning that the query result will be fetched in chunks containing 500 rows each. 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. The result of the query execution is sent to a 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 - in this case field mappings are interpreted as bean property names. Queries returning object tuples as rows - fields are mapped using one of the following forms: `COLUMN_<index>` - the field is mapped to a value specified by its position in the resulting tuple. The positions start from 1. `COLUMN_<index>.<property>` - the field is mapped to a property of a value specified by its position in the resulting tuple. The EJB QL/JPA Query Executer Sample The movie database sample in the `demo/samples/ejbql` directory is structured as follows: the `data` directory contains the SQL script that creates and populates the following tables in the built-in HSQL database: `PERSON` - stores people IDs and names `MOVIE` - stores movie ID, director, title, genre and release date informations `MOVIE_CAST` - stores ID, movie ID, actor ID, character and role importance `MOVIE_VARIA` - stores ID, movie ID, type, description, importance the `docs` directory contains the xml file used to generate this documentation the `lib` directory contains 3-rd party jars with necessary persistence APIs the `reports` directory contains, as usual, report templates (1 master and 2 subreports) the `src` directory contains a `META-INF` subdirectory with the `persistence.xml` persistence configuration file:
        <persistence-unit name="pu1">
          <!-- Provider class name is required in Java SE -->
          <provider>oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider</provider>
          <!-- All persistence classes must be listed -->
          <class>Person</class>
          <class>Movie</class>
          <class>Cast</class>
          <class>Varia</class>
          <properties>
            <!-- Provider-specific connection properties -->
            <property name="toplink.jdbc.driver" value="org.hsqldb.jdbcDriver"/>
            <property name="toplink.jdbc.url" value="jdbc:hsqldb:file:build/db"/>
            <property name="toplink.jdbc.user" value="sa"/>
            <property name="toplink.jdbc.password" value=""/>
            <!-- Provider-specific settings -->
            <property name="toplink.logging.level" value="DEBUG"/>
            <property name="toplink.platform.class.name" value="oracle.toplink.essentials.platform.database.HSQLPlatform"/>
          </properties>
        </persistence-unit>
  - the annotated entity classes mapped on the tables in the database: `Person`, `Movie`, `Cast` and `Varia`. Corresponding tables are specified in the @Table annotation:
        @Entity
        @Table(name="movie_cast")
        public class Cast {
        ...
        }
the `EjbqlApp.java` file that hosts the `main` method. The `reports/JRMDbReport.jrxml` master report is structured to present movies in the database along with their additional informations, such as casting, awards, quotes, etc. Casts are managed separately in the `reports/JRMDbCastSubreport.jrxml` template, and the rest of additional information is provided by the `reports/JRMDbVariaSubreport.jrxml` Looking into the `reports/JRMDbReport.jrxml`, one could notice the specific hint properties, the EJB QL query syntax, report fields declarations and how the subreports were set:
    ...
    <property name="net.sf.jasperreports.ejbql.query.hint.fetchSize" value="50"/>
    <property name="net.sf.jasperreports.ejbql.query.page.size" value="100"/>
    <import value="net.sf.jasperreports.engine.data.JRBeanCollectionDataSource"/>
    ...
    <queryString language="ejbql">
      <![CDATA[SELECT   m
        FROM     Movie m
        WHERE    m.releaseDate BETWEEN $P{DateFrom} AND $P{DateTo}
        ORDER BY $P!{OrderClause}]] >
    </queryString>
    <field name="id" class="java.lang.Integer"/>
    <field name="director.name" class="java.lang.String"/>
    <field name="title" class="java.lang.String"/>
    <field name="genre" class="java.lang.String"/>
    <field name="releaseDate" class="java.sql.Date"/>
    <field name="cast" class="java.util.Collection"/>
    ...
    <detail>
      <band height="45">
        ...
        <subreport>
          <reportElement positionType="Float" x="15" y="25" width="245" height="20" isRemoveLineWhenBlank="true" backcolor="#99CCFF"/>
          <dataSourceExpression><![CDATA[new JRBeanCollectionDataSource($F{cast})]] ></dataSourceExpression>
          <subreportExpression class="java.lang.String"><![CDATA["JRMDbCastSubreport.jasper"]] ></subreportExpression>
        </subreport>
        <subreport>
          <reportElement positionType="Float" x="270" y="25" width="245" height="20" isRemoveLineWhenBlank="true" backcolor="#99CCFF"/>
          <subreportParameter name="MovieId">
            <subreportParameterExpression><![CDATA[$F{id}]] ></subreportParameterExpression>
          </subreportParameter>
          <subreportParameter name="JPA_ENTITY_MANAGER">
            <subreportParameterExpression><![CDATA[$P{JPA_ENTITY_MANAGER}]] ></subreportParameterExpression>
          </subreportParameter>
          <subreportExpression class="java.lang.String"><![CDATA["JRMDbVariaSubreport.jasper"]] ></subreportExpression>
        </subreport>
      </band>
    </detail>
    ...
The Movie entities provide an `id`, a `director` (who is a `Person` entity), a `title`, a `genre`, `releaseDate` and a collection of `cast` entities. Notice the report field `director.name` that refers to the `name property in the `director` entity.` The casts collection in the `cast` field is passed as a `JRBeanCollectionDataSource` to the casting subreport. The `Varia` subreport has no expression for data source/connection, but takes the built-in `JPA_ENTITY_MANAGER` parameter into consideration, and the movie ID, in order to identify the `Varia` entities related to the movie. The `reports/JRMDbCastSubreport.jrxml` provides no query, because the data source comes already prepared here. There are two fields declared in the report:
    <field name="actor.name" class="java.lang.String"/>
    <field name="character" class="java.lang.String"/>
Again, the `actor.name` field references the `name` property in the `actor` entity of type `Person`. In the `reports/JRMDbVariaSubreport.jrxml` one could notice the presence of an EJB QL query and field descriptions based on column positions:
    ...
    <parameter name="MovieId" class="java.lang.Integer"/>
    <queryString language="ejbql">
      <![CDATA[SELECT   v.type, v.description
        FROM     Varia v
        WHERE    v.movie.id = $P{MovieId}
        ORDER BY v.importance]] >
    </queryString>
    <field name="type" class="java.lang.String">
      <fieldDescription><![CDATA[COLUMN_1]] ></fieldDescription>
    </field>
    <field name="description" class="java.lang.String">
      <fieldDescription><![CDATA[COLUMN_2]] ></fieldDescription>
    </field>
    ...
Running the Sample Running the sample requires the Apache Ant library. Make sure that `ant` is already installed on your system (version 1.5 or later). In a command prompt/terminal window set the current folder to `demo/hsqldb` within the JasperReports source project and run the `> ant runServer` command. It will start the HSQLDB server shipped with the JasperReports distribution package. Let this terminal running the HSQLDB server. Open a new command prompt/terminal window and set the current folder to `demo/samples/ejbql` within the JasperReports source project and run the `> ant test view` command. It will generate all supported document types containing the sample report in the `demo/samples/ejbql/build/reports` directory. Then the report will open in the JasperReports internal viewer.

© 2001- Jaspersoft Corporation www.jaspersoft.com