esProc Integration & Application: Integration with JasperReport

esProc provides standard JDBC interface to be easily integrated with reporting tools. This article will explain the integration of esProc and JasperReport respectively in development environment and WEB server-side deployment.

1. Integration in development environment

Access esProc JDBC to call the esProc script using JasperReport iReport Designer through the following steps:

1.1 Create configuration files

Create the two configuration files – config.xml and dfxConfig.xml – necessary for esProc JDBC.

1.1.1 config.xml

<?xml version=”1.0″ encoding=”UTF-8″?>

<!– Registration code –>

<!– Configure dfx file’s searching path, which is an absolute path. There can be multiple paths separated by semicolon(s). The dfx file can also be placed in the application’s classpath which has the priority over a searching path for loading the file –>

</dfxPathList>

<!– Method one for datasource configuration: configure connection pool in the application server and specify the datasource name –>

<!– Datasource name must be consistent with that in the dfx file. Here MySQL database is used –>

</DB></DBList>

<logLevel>DEBUG</logLevel>

</Config>

1.1.2 dfxConfig.xml

<?xml version=”1.0″ encoding=”iso-8859-1″?>

</dfxConfig>

1. 2 Load driver jars

esProc JDBC driver includes three basic jars – dm.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar. They can be found in esProc IDE’s [installation directory]\esProc\lib directory. If esProc also uses other databases as the datasources, then the driver jars of these databases need to be added too. For instance, mysql-connector-java-5.1.5-bin.jar has been added here. Place all these jars in classpath’s root path. About the jars for activating other esProc functions (like accessing Office files), see esPrc Integration & Application: Deploying JDBC.

Except for these jars, the directory (such as D:\lib\config) of the two configuration files – config.xml and dfxConfig.xml – created in the first step needs to be placed in the classpath’s root path too. Or compress the configuration files into the root directory of any of the jars above. config.xml is allowed to be renamed esprocJDBCconfig.xml in case there are already config.xml files for other purposes under classpath. esProc JDBC driver will find and use esprocJDBCconfig.xml first in preference to config.xml, which will be used when the former does not exist. Names of these configuration files’ names should be remain what they are specified here and must not be changed.

Configure the classpath on the Tool Option bar, as shown in the following figure:

esProc_integration_jasper_1

1.3 Create a datasource

Create a new datasource – esproc – on the datasource configuration window. Select the JDBC driver com.esproc.jdbc.InternalDriver and enter the JDBC URL as jdbc:esproc:local://. Leave the username and password blank.

esProc_integration_jasper_2

esProc JDBC is a fully embedded computing engine. All computations are performed in this embedded package, thus the URL is local. On the other hand, since it isn’t a complete database, it has neither the username nor the password.

After finishing all necessary information, click on “Test” to see whether the connection to the datasource has been successfully established.

1.4 Deploy esProc script

Copy the esProc script (dfx) to dfxPath configured in config.xml (such as D:/dfx configured in the above). Here we’ll take the query of the closing stock price in a certain month as an example. The esProc script is as follows:

	A
1	=connect(“mysql”)
2	=A1.query(“select * from STOCKRECORDS where year(tradingdate)*100+month(tradingdate)=?”,arg1)
3	Result A2

1.5 Call esProc script in IDE

Create a report and set the query

Create a new report – esProcReport, write the query statement and call the esProc script using call dfxName(), in which dfx name is stocks.

esProc_integration_jasper_4

Select all the fields and execute the code by clicking on Next Step to the end.

esProc_integration_jasper_5

Because the esProc script needs to receive the parameter representing the year and the month, it is necessary to set the parameter for use in the query for the report after it is created. In the following figure, add the parameter representing the year and the month and edit the query for the report.

esProc_integration_jasper_6

Then drag the parameter to the specified position of the query statement, thus the query becomes as follows:

esProc_integration_jasper_7

Edit the report

Drag all fields under Fields to the report’s detail band and complete the edition, as shown below:

esProc_integration_jasper_8

Preview the report

Click on Preview and enter the parameter to see the query result:

esProc_integration_jasper_9

This is the integration of esProc script and JasperReport in the development environment. Next we’ll move on to explain how to deploy them in the web server-side.

2. Deploy web server-side

Select a suitable application server. For this purpose, Tomcat6.0 is used here for illustration.

2.1 Create a web application

Create a J2EE application, which is named, say, JasperReport, and copy it to Tomcat’s webapps directory.

2.2 Create configuration files

Two configuration files – config.xml and dfxConfig.xml – are needed. The configuration information is the same as that for integration in development environment.

2.3 Deploy necessary jars

JasperReport driver jar

Deploy jars necessary for JasperReport into the application’s WEB-INF\lib directory. The JasperReport jar can be found in ireport\modules\ext directory of iReport Designer’s installation directory.

esProc JDBC’s driver jar

Copy the jars – dm.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar – used in the above integration in the development environment and MYSQL’s driver jar – mysql-connector-java-5.1.5-bin.jar – to the application’s WEB-INF\lib directory. Then copy config.xml and dfxConfig.xml to the application’s WEB-INF\classes directory.

2.4 Copy the report

Copy the completed report (whose file extension is .jasper) to the corresponding directory of the application. For example, copy esProcReport.jasper to the application’s reportFiles directory.

2.5 Define and Publish the JSP report

The following code is to define and publish the JSP report, such as showReport.jsp:

<%@ page language=“java” contentType=“text/html; charset=UTF-8”

pageEncoding=“UTF-8”%>

<%@ page

import=“java.io.*,

net.sf.jasperreports.engine.*,

net.sf.jasperreports.engine.util.*,

java.util.*,java.sql.*,

net.sf.jasperreports.engine.export.*”%>

<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”>

<html>

<head>

<title>Show JasperReport</title>

</head>

<body>

String report = request.getParameter(“report”);

File reportFile = new File(application.getRealPath(“reportFiles/”

+ report));

//Load report object

JasperReport jasperReport = (JasperReport) JRLoader

.loadObject(reportFile.getPath());

//Pass report parameter through Map

Map parameters = new HashMap();

//Assign value to parameter p1 of the report

parameters.put(“p1”, 200901);

//Connect to esProc

Class.forName(“com.esproc.jdbc.InternalDriver”);

Connection conn = DriverManager.getConnection(

“jdbc:esproc:local://”, “”, “”);

//Export the report to the page

JasperPrint jasperPrint = JasperFillManager.fillReport(

jasperReport, parameters, conn);

JRHtmlExporter exporter = new JRHtmlExporter();

exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, out);

exporter.setParameter(

JRHtmlExporterParameter.IS_USING_IMAGES_TO_ALIGN,

Boolean.FALSE);

exporter.exportReport();

out.flush();

conn.close();

</body>

</html>

esProc offers standard JDBC interface. Besides, as a database without the storage mechanism, esProc allows users to connect it by configuring the connection pool. In the above code, esProc is connected through JDBC:

Class.forName(“com.esproc.jdbc.InternalDriver”);

Connection conn = DriverManager.getConnection(

“jdbc:esproc:local://”, “”, “”);

With JNDI connection pool, the code will become this under Tomcat:

Context c = new InitialContext();

DataSource ds = (DataSource)c.lookup(“java:comp/env/esproc”);

Connection conn = ds.getConnection();

Note: About the configuration of connecting esProc to a database, see esProc Integration & Application: Deploying JDBC.

2.6 Preview the report

Launch Tomcat and access the website http://127.0.0.1:8080/JasperReport/reportJsp/showReport.jsp?report=esProcReport.jasper to preview the report as shown below, which means the deployment is completed.

esProc_integration_jasper_10