JNDI datasources in Subreports (Jasperserver)

German Translation (Original): http://blog.itransparent.de/datenquellen-aus-jndi-in-subreport-jasperserver/

My friend Dr. Robert Nagy has written a  Whitepaper and describes how to combine heterogeneous data sources in subreports. One of my customers has had the concrete issue of a different data source in a subreport that is called in the detail band of the main report very often. This lead to many (too many) open connections.

The common hint provided by Jaspersoft is the use of JNDI for management of JDBC data sources. In “Ultimate Guide” for JasperReports Server they write

Using JNDI-Datenquelle for main reports is straightforward. Data sources can be chosen either via Web application or via iReport/Jaspersoft Studio. Connection Expressions and Data Source Expressions respectively  describe differing data sources for subreports. However, it is not obvious how to define a connection expression that creates a connection by using JNDI-managed data sources of the Repository.

There has been a helpful article in the Jaspersoft-Community (http://community.jaspersoft.com/questions/542097/subreports-and-jndi, last visited 2013-10-01). They suggested a piece of  Java that returns a connection after a lookup in JNDI. I try to avoid additional classes where possible as these have to be deployed to the  JasperServer. The corresponding one-liner can used immediatly as connection expression (sugarcrm example as provided by Jaspersoft as sample data):

((javax.sql.DataSource)(new javax.naming.InitialContext()) .lookup("java:comp/env/jdbc/sugarcrm")).getConnection()

This solution did not satisfy my requirements as JNDI name is managed by expression in the main report and not by the repository as expected. The RepositoryService can be looked-up within a report by

com.jaspersoft.jasperserver.api.engine.jasperreports.util .RepositoryUtil.getThreadRepositoryContext().getRepository()

The JNDI name of a data source in the repository of a JasperServer running the report can be obtained by

((com.jaspersoft.jasperserver.api.metadata.jasperreports.domain  .JndiJdbcReportDataSource) (com.jaspersoft.jasperserver.api.engine.jasperreports.util  .RepositoryUtil.getThreadRepositoryContext().getRepository()) .getResource(null,"/analysis/datasources/SugarCRMDataSourceJNDI")).getJndiName()

A compact one-liner for a connection expression is

((javax.sql.DataSource)(new javax.naming.InitialContext()) .lookup("java:comp/env/" +   ((com.jaspersoft.jasperserver.api.metadata.jasperreports.domain    .JndiJdbcReportDataSource)   (com.jaspersoft.jasperserver.api.engine.jasperreports.util    .RepositoryUtil.getThreadRepositoryContext().getRepository())   .getResource(null, "/analysis/datasources/SugarCRMDataSourceJNDI"))  .getJndiName())).getConnection()

Due to performance reasonse, I recommend to keep a javax.sql.DataSource object as a variable in the main report instead of the one-liner. This prevents a repository lookup and a JNDI lookup for each row of the main report.

Hint: For compiling reports in iReport Designer with connection expressions as described above, the classpath  has to contain the relevant JAR files of JasperServer.