HyperSQL (HSQLDB) data source/data adapter in JasperReports Server/JRIO

Issue Description:

Jaspersoft Studio has built-in HyperSQL DB data source called "Sample DB". I'd like to use HSQLDB for data input in JasperServer as well as JRIO.

Explanation: 

1) HyperSQL in JasperReports Server

First of all, start by adding HyperSQL JDBC driver to application classpath. If you are hosting JasperServer on Tomcat, simply add hsqldb-2.4.0.jar (or similar JAR file of different version) to apache-tomcat/webapps/jasperserver-pro/WEB-INF/lib/ folder and reboot.

Then, you need to make sure HyperSQL DB server is up and running and there is data to be retrieved. Quoting HSQLDB documentation:

"A uniform method is used to distinguish between different types of connection. The common driver identifier is jdbc:hsqldb: followed by a protocol identifier (mem file res hsql http hsqls https) then followed by host and port identifiers in the case of servers, then followed by database identifier. Additional property / value pairs can be appended to the end of the URL, separated with semicolons"

For the purpose of this demonstration, I'm using Northwind DB accessed as Resource Database URL:

jdbc:hsqldb:res:hsqldb/northwind;shutdown=true

In order to do that, I'm also adding hsqldb-northwind.jar to application server classpath (apache-tomcat/webapps/jasperserver-pro/WEB-INF/lib/)

1.1. Data Adapter approach on JasperReports Server

Data adapter approach is described in details in following articles:

https://community.jaspersoft.com/wiki/how-create-report-uses-remote-json-data-source

https://community.jaspersoft.com/wiki/using-data-adapters-fill-reports-json-data-remote-url

Main idea is to reference data adapter using "repo:" syntax, e.g.:

<property name="net.sf.jasperreports.data.adapter" value="repo:/public/customer_cases/HsqldbDataAdapter"/>

You can use data adapter definition below for HSQLDB Northwind use case:

<?xml version="1.0" encoding="UTF-8" ?>
<jdbcDataAdapter class="net.sf.jasperreports.data.jdbc.JdbcDataAdapterImpl">
    <name>HyperSQL Northwind DB</name>
    <driver>org.hsqldb.jdbcDriver</driver>
    <username>sa</username>
    <password></password>
    <savePassword>true</savePassword>
    <url>jdbc:hsqldb:res:hsqldb/northwind;shutdown=true</url>
    <database></database>
    <serverAddress></serverAddress>
</jdbcDataAdapter>

Also, please find example JasperReports Server ZIP export in the end of this article.

1.2. JDBC data source on JasperReports Server

You can also create a domain based on HSQLDB. Simply create JDBC data source based on your HSQLDB using driver/url/credential above and create a domain accordingly.

2) HyperSQL in JasperReports IO (Data Adapter approach only)

JRIO only provides you ability to work with data adapters. The key different between JRXML executed on JasperReports Server and the one of JRIO is "data.adapter" path value. Here I'm using syntax below

<property name="net.sf.jasperreports.data.adapter" value="/samples/data/HsqldbDataAdapter.xml"/>

Hence, you need to make sure that

- HsqldbDataAdapter.xml is present in respective folder (e.g. /opt/jrio-1.0.0/repository/samples/data/HsqldbDataAdapter.xml)

- JAR files are in JRIO classpath (e.g. /opt/jrio-1.0.0/repository/samples/JR-INF/context.xml), for example

<context xmlns="">
        <classpath>
                <entry>
                        <path>/samples/JR-INF/lib/hsqldb-2.4.0.jar</path>
                </entry>
                <entry>
                        <path>/samples/data/hsqldb-northwind.jar</path>
                </entry>
                ...
        </classpath>
        ...
</context>

For more information on JRIO functionality please refer to "JasperReports IO - Getting Started" project page.

3) HyperSQL and dummy query in JasperReports IO/JasperReports Server

For compatibility purposes you might want to avoid using Northwind DB and consider using dummy query. Such query can be executed against any HyperSQL (and traditional RDBMS like PostgreSQL/MySQL) database. Jaspersoft Studio provides functionality to automatically generate such query:

https://community.jaspersoft.com/wiki/create-mock-or-sample-query-data-using-jaspersoft-studios-export-sql

After modifying dataset query in JRXML file, you need to update data adapter respectively. HyperSQL provides ability to (create in-memory DB on-the-fly and we will utilize this approach to create "empty" in-memory DB. To do that, please change connection URL string in data adapter XML file to:

jdbc:hsqldb:mem:empty;

I'm also attaching TEST_charts_HSQLDB.jrxml report that contains HighCharts and utilizes dummy query approach with HsqldbDataAdapter_inMemory_dummy.xml data adapter.

hsqldb_address_jrs.jrxml

hsqldb_address_jrio.jrxml

hsql_on_jrs.zip

hsqldbdataadapter.xml

hsqldb-northwind.jar

hsqldb-2.4.0.jar

hsqldbdataadapter_inmemory_dummy.xml

test_charts_hsqldb.jrxml