How to Call an SPL Script in JasperReport
esProc provides its own JDBC driver to become integration-friendly with a reporting tool. Here we take the development environment of JasperReport5.6.0 as an example to explain how to call SPL in JasperReport.
Put simply, to integrate esProc JDBC with JasperReport’s iReport designer is to put the necessary jars and configuration files for loading esProc JDBC in JasperReport’s class path. esProc JDBC requires JDK 1.8 or a higher version.
Create the necessary configuration file raqsoftConfig.xml, which is located in [installation directory]\esProc\config. Its name must not be changed.
The raqsoftConfig.xml file contains esProc main path, dfx file’s search path, and etc.
esProc JDBC is like a database JDBC driver without physical tables. It can be regarded simply as a database that only supports the stored procedure. It is a stand-alone computing engine that can perform all calculations independently. A database’s JDBC, however, functions only as an interface and an extra database server is needed to perform calculations.
esProc JDBC has two basic jars, which are all situated in [installation directory]\esProc\lib:
esproc-bin-xxxx.jar esProc computing engine and JDBC driver
icu4j-60.3.jar Handle internationalization
Besides, there are jars for achieving specific functionalities:
To use databases as the data sources in esProc JDBC, their driver jars are required.
To read and write an Office file, poi.jar* and xmlbeans-4.0.0.jar are needed.
To use the graphic function in esProc JDBC, jars for SVG-typed image processing are required, including batik.jar*, js-1.0.jar, pdf-transcoder-1.0.jar, xalan-2.7.0.jar, xercesImpl-2.12.1.jar, xml-apis-xerces-2.7.1.jar and xml-apis-ext-2.5.0.jar.
Place the necessary jars and the directory where raqsoftConfig.xml is located (like D:\software2\Jaspersoft\esProc\config) in the class path. You can also put the compressed configuration file into the root directory of any of the necessary jars.
Click Tools -> Options to configure Classpath:
Here is the process of calling SPL in JasperReport. esProc JDBC is the data source over which SQL-like SPL queries are executed and return a result set. Then the reporting tool calls the result set to build a report.
On the Datasource window, add the data source esproc, select **com.esproc.jdbc.InternalDriver **under JDBC Driver, type **jdbc:esproc:local:// **under JDBC URL, and leave both Username and Password blank.
esProc is a stand-alone embedded computing engine that can perform all calculations independently. Its URL is local. As it isn’t a real database, it doesn’t need user name and password.
Click Test button and, if the prompt shows Connection test successful!, the data source is connected. Then Click Save to finish the data source creation.
Let’s look at how JasperReport executes different types of SPL query to build a report.
Create a data table with two fields – baseNum and square2, insert 100 records made up of natural numbers less than and equal to 100 and their squares into it, and return the table as result set. The calculation can be expressed in SPL this way: =100.new(:baseNum,*~:square2).
Create a new report, use the esproc data source, and set a query:
Click Next to select all fields:
Click Next until you get to the Finish button.
Report editing:
Drag all selected fields to the report template’s detailed data area to edit:
Report preview:
You can access a local file from SPL. esProc supports multiple file types, such as TXT, Excel, JSON, CSV and CTX. Both absolute path and relative path can be used to find a file. A relative path is relative to the main directory set in the configuration file raqsoftConfig.xml. First, let’s look at how to configure the main directory.
Add the following node under </ Esproc > in raqsoftConfig.xml:
<!-- esProc main directory, which is an absolute path -->
<mainPath> D:\software2\Jaspersoft\esProc </mainPath>
Put a to-be-called file, like employee.txt, into the main directory. Code for creating a new report and connecting data source is the same as those explained in the above. Here the query is: =file("employee.txt").import@t().
Report preview:
As the calculation is simple, you can also use esProc simple SQL syntax to do it: $()select * from employee.txt.
The symbol $() means accessing a local file system. The two queries get same result.
Parameters are an important part of a SQL statement. So are they in SPL statements. To query records in employee.txt according to the condition that salary is within a range of [12000, 20000] and sort them by salary in ascending order.
On the Report query window, we can add two parameters param1 and param2 and set their data types as integer. Drag the parameters to their appropriate positions in the query. The final query is: $()select * from employee.txt where SALARY > $P{param1} and SALARY<$P{param2} order by SALARY.
Report preview:
Enter the parameters on Parameter prompt window to preview the report.
Here’s the finished report:
As a computing engine, one of the most important data sources of esProc JDBC is the database. Let’s look at how to call a SPL statement that connects to the database.
First, put in place corresponding database driver jars; then set data source information in raqsoftConfig.xml. Suppose the data source name used in an SPL statement is demo and the database type is HSQL, then you can configure it like this:
First, put HSQL database driver jar hsqldb-2.2.8.jar in Classpath;
Second, set data source information in the node in raqsoftConfig.xml.
<DBList>
<DB name="demo"> <!—data source name-->
<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo" /> <!—url connection-->
<property name="driver" value="org.hsqldb.jdbcDriver" /> <!—database driver-->
<property name="type" value="13" /> <!—database type-->
<property name="user" value="sa" /> <!--username-->
<property name="password" /> <!--password-->
<property name="batchSize" value="1000" />
<property name="autoConnect" value="true" /><!-- Automatically connect or not. If the value is true, use a SQL statement starting with $ to access the database; if it is false, there will be no automatic connection; establish the connection with connect(db) statement before you can access the database -->
<property name="useSchema" value="false" />
<property name="addTilde" value="false" />
<property name="dbCharset" value="UTF-8" />
<property name="clientCharset" value="UTF-8" />
<property name="needTransContent" value="false" />
<property name="needTransSentence" value="false" />
<property name="caseSentence" value="false" />
</DB>
</DBList>
Now we’ll query SALES table from demo in SPL to find records whose SELLERID is 3 and ORDERDATE is from 2014-11-01 to 2014-12-12:
Create a new report and data source as the first instance shows. Then set three parameters – param1, param2 and param3, and drag them to their positions in the query. The final query is: $(demo)select * from SALES where SELLERID = $P{param1} and ORDERDATE>$P{param2} and ORDERDATE<$P{param3}.
Report preview:
Enter the parameters on Parameter prompt window to preview the report.
Here’s the finished report:
By integrating esProc JDBC, JasperReport can call and execute a single SPL statement as well as the complicated SPL script (whose extension is dfx).
A dfx file:
| A | B | C | |
|---|---|---|---|
| 1 | =demo.query("select NAME as CITY, STATEID as STATE from CITIES") | [] | |
| 2 | for A1 | =demo.query("select * from STATES where STATEID=?",A2.STATE) | |
| 3 | if left(B2.ABBR,1)==arg1 | >A2.STATE=B2.NAME | |
| 4 | >B1=B1 | ||
| 5 | return B1 |
Explanation:
There are two tables – CITIES and STATES. Traverse records in CITIES and find corresponding STATES record by CITIES. STATES, during which if the first letter of STATES.ABBR is equal to parameter arg1, then assign STATES.NAME to CITIES.STATE, and add this record in CITIES to B1. The final result set returned is B1’s value.
The above cellset file receives data from the data source demo and uses parameter arg1:
See the above instances for the data source configuration. The cellset file is saved as city.dfx. A dfx file can be saved in the main directory set in raqsoftConfig.xml. If there are many dfx files, we can put them in the dfx’s search path for easy management and maintenance. Below is how to configure a search path:
Add the following content under <\Esproc> in raqsoftConfig.xml:
<dfxPathList>
<dfxPath>D:\software2\Jaspersoft\esProc\dfx</dfxPath>
</dfxPathList>
<!--Configure dfx file’s search path; multiple paths are allowed and separated by the semicolon -->
Create a new report and data source as the previous instances show. Then create the parameter param1 and set its data type as text, and drag it to its position in the query. The final query is: call city($P{param1})。
Report preview:
Enter the parameter on Parameter prompt window to preview the report.
Here’s the finished report:
We can also use *dfx(… ) *directly without the call when calling a dfx file in JasperReport. In this case, the query is: city $P{param1}.
Enter the parameter to preview the report. The query result will be the same.