EJBQL query support added in JasperReports

My patch to support EJBQL queries inside JasperReports templates is included in the latest JasperReports 1.2.3 release. You can use any Java Persistence API implementation (part of JSR-220) like GlassFish/Toplink Essentials or Hibernate Entity Manager to provide data to your reports.

An example of using EJBQL in JasperReports templates is provided in the JasperReports project distribution, located in \demo\samples\ejbql. The example uses the Toplink Essentials Java Persistence API implementation from GlassFish. All necessary libraries to run the example are included in the JasperReports project distribution.

Running the example
Open a Command box and and navigate to JR_HOME\demo\samples\ejbql. Then run ant javac to compile the Java sources, ant compile to compile the JasperReports template files, ant fill to fill the compiled report design with data, and ant view to launch the report viewer to preview the report.

C:\Java Libs>cd jasperreports-1.2.3\demo\samples\ejbql

C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql>ant javacBuildfile:

build.xml

javac:

    [mkdir] Created dir: C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\build\classes

    [javac] Compiling 5 source files to C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\build\classes

    [javac] Note: C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\EjbqlApp.java uses unchecked or unsafe operations.

    [javac] Note: Recompile with -Xlint:unchecked for details.

     [copy] Copying 1 file to C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\build\classes

     [copy] Copying 1 file to C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\build

BUILD SUCCESSFUL

Total time: 2 seconds

C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql>ant compileBuildfile:

build.xml

compile:

      [jrc] Compiling 3 report design files.

      [jrc] File : C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\JRMDbVariaSubreport.jrxml ... OK.

      [jrc] File : C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\JRMDbCastSubreport.jrxml ... OK.

      [jrc] File : C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql\JRMDbReport.jrxml ... OK.

BUILD SUCCESSFUL

Total time: 3 seconds

C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql>ant fillBuildfile:

build.xml

fill:

     [java] [TopLink Info]: 2006.06.02 08:55:20.906--ServerSession(30533424)--TopLink, version: Oracle TopLink Essentials - 2006.4 (Build 060412)

     [java] [TopLink Info]: 2006.06.02 08:55:21.281--ServerSession(30533424)--file:/C:/Java%20Libs/jasperreports-1.2.3/demo/samples/ejbql/build/classes-pu1 login successful

     [java] Filling time : 2125

     [java] [TopLink Info]: 2006.06.02 08:55:23.015--ServerSession(30533424)--file:/C:/Java%20Libs/jasperreports-1.2.3/demo/samples/ejbql/build/classes-pu1 logout successful

BUILD SUCCESSFUL

Total time: 4 seconds

C:\Java Libs\jasperreports-1.2.3\demo\samples\ejbql>ant viewBuildfile:

build.xml

view:

BUILD SUCCESSFULTotal time: 4 seconds

Understanding the code
The ant fill target does the filling of the report by quering the HSQDB using the Java Persistence API. In the JRMDbReport.jrxml report template the following queryString was defined:

<queryString language="ejbql">

  <![CDATA[

    SELECT   m

    FROM     Movie m

    WHERE    m.releaseDate BETWEEN $P{DateFrom} AND $P{DateTo}

    ORDER BY $P!{OrderClause}]]>

</queryString>

By setting the language="ejbql" attribute, the JasperReports engine will use the Java Persistence API to retrieve the data. When filling the report an EnitiyManager instance need to be provided to the JasperReports engine. This is done in the main \demo\samples\ejbql\EjbqlApp.java class:

EntityManagerFactory emf = Persistence.createEntityManagerFactory("pu1", new HashMap());

EntityManager em = emf.createEntityManager();

Map parameters = new HashMap();

parameters.put(JRJpaQueryExecuterFactory.PARAMETER_JPA_ENTITY_MANAGER, em);

JasperFillManager.fillReportToFile(fileName, parameters);

Note that this is only the part of the code to provide the EntityManager. Take a look at the \demo\samples\ejbql\EjbqlApp.java class for the complete code of the example.

The actual connection information to connect to the database is stored in the \demo\samples\ejbql\META-INF\persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">

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

To read more about the EJBQL support in JasperReports, for example how to specify Query Hints, take a look at the JasperReports API documentation of the JRJpaQueryExecuter class.