In a report sometimes the data source / database / network is performing badly or maybe the query is just too complex. In some cases it would be useful to be able to run a query just once in the report, but then cache the data and reuse it in multiple components or datasets.
Attached to the bottom of this article (here), we provide a sample JRXML file to show one way to implement this via the JRDataSource interface, which is documented here:
- https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v71/working-jrdatasource-interface-0
- http://jasperreports.sourceforge.net/sample.reference/datasource/index.html#datasources
The custom code below is required for the report to work and needs to be compiled into a JAR file and made available where the report is running (e.g. in Jaspersoft Studio or JasperReports Server).
See also here for other possible approaches: How to use multiple data sources in Jaspersoft Studio or JasperReports Server
This example is based on the product table which is available in the Foodmart sample database.
Please be aware that this sample code is provided AS IS, it is your responsibility to review it and make sure it satisfies your requirements. Feel free to modify it and adapt it to your needs. Also bear in mind that LOADING TOO MUCH DATA MIGHT EXHAUST YOUR SERVER MEMORY, so use with caution.
Custom Bean
Create a class that holds fields required by your report, for example:
package com.example.jaspersoft.reportcache;
// Custom Bean
public class CBean {
private String brand_name;
private String product_name;
private Float gross_weight;
public void setbrand_name(String brand_name) {
this.brand_name = brand_name;
}
public String getbrand_name() {
return this.brand_name;
}
public void setproduct_name(String product_name) {
this.product_name = product_name;
}
public String getproduct_name() {
return this.product_name;
}
public void setgross_weight(Float gross_weight) {
this.gross_weight = gross_weight;
}
public Float getgross_weight() {
return this.gross_weight;
}
}
Data Source
Create a class that iterates through a result set and stores the rows in a collection (JRBeanCollectionDataSource), for example:
package com.example.jaspersoft.reportcache;
import java.util.ArrayList;
import java.util.Collection;
import java.util.logging.Logger;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.design.JRDesignField;
public class CollectionDS {
static final Logger LOGGER = Logger.getLogger(CollectionDS.class.getName());
public static JRBeanCollectionDataSource jdbc2CollectionDS(JRDataSource res) throws JRException {
JRBeanCollectionDataSource ds = null;
LOGGER.fine("Inside JRBeanCollectionDataSource");
JRDesignField f = new JRDesignField();
f.setName("brand_name");
f.setValueClass(java.lang.String.class);
f.setValueClassName("java.lang.String");
JRDesignField f2 = new JRDesignField();
f2.setName("product_name");
f2.setValueClass(java.lang.String.class);
f2.setValueClassName("java.lang.String");
JRDesignField f3 = new JRDesignField();
f3.setName("gross_weight");
f3.setValueClass(java.lang.Float.class);
f3.setValueClassName("java.lang.Float");
LOGGER.fine("After JRDesignField");
Collection<CBean> coll = new ArrayList<CBean>();
if (res instanceof JRResultSetDataSource) {
while (((JRResultSetDataSource) res).next()) {
String brand_name = (String) res.getFieldValue(f);
String product_name = (String) res.getFieldValue(f2);
Float gross_weight = (Float) res.getFieldValue(f3);
CBean product = new CBean();
product.setbrand_name(brand_name);
product.setproduct_name(product_name);
product.setgross_weight(gross_weight);
coll.add(product);
}
} else {
throw new JRException("Your Data Source MUST be of type JRResultSetDataSource in JSS or JRS, or JSControlledResultSetDataSource in JRS");
}
ds = new JRBeanCollectionDataSource(coll);
LOGGER.fine("JRBeanCollectionDataSource ds: " + ds.getRecordCount());
return ds.cloneDataSource();
}
public static JRBeanCollectionDataSource rewind(JRBeanCollectionDataSource res) throws JRException {
// JRBeanCollectionDataSource ds = res.cloneDataSource();
res.moveFirst();
return res;
}
}
Report
In your report, use the existing JDBC connection to generate the result set, for example:
- Create a Variable with name myCollection:
- Value Class Name: net.sf.jasperreports.engine.data.JRBeanCollectionDataSource
- Calculation: System
- Initial Value Expression: com.example.jaspersoft.reportcache.CollectionDS.jdbc2CollectionDS(new JRResultSetDataSource($P{REPORT_CONNECTION}.createStatement().executeQuery("SELECT brand_name, product_name, gross_weight FROM product")))
- Leave empty any other fields
- If you want to use the query of the main report dataset, use this instead of the static query text: $P{JASPER_REPORT}.getQuery().getText()
- Create a Sub Dataset with name Dataset1, then use it in your element, for example an HTML5 Chart. Let's call it Chart1
- In Chart1: use a DataSource expression with value: $V{myCollection}
- Do the same above in another element, let's call it Chart2, but use this in the DataSource expression: com.example.jaspersoft.reportcache.CollectionDS.rewind($V{myCollection})
- See screenshots below as examples
We recommend setting the main report dataset to a dummy query (for example in PostgreSQL: SELECT 1), as your main query will run in a secondary dataset, to be then reused in other datasets. If you use it in both places, it will run twice and reduce the performance gain of running it only once.
Verify the query runs only once
First of all, please follow instructions from Configuring Apache Commons Logging and Log4J in Jaspersoft Studio to enable Apache Commons Logging in Jaspersoft Studio. Then add following lines to log.properties:
#jr caching customization com.example.jaspersoft.reportcache.CollectionDS.level = FINE com.example.jaspersoft.reportcache.CBean.level = FINE org.postgresql.level=FINEST
You should see similar records in your studio-common.log
Feb 08, 2019 12:40:19 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST: FE=> Parse(stmt=null,query="select 1",oids={})
...
Feb 08, 2019 12:40:19 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST: FE=> Parse(stmt=null,query="SELECT brand_name, product_name, gross_weight FROM product",oids={})To use the classes in your reports, put the compiled class files in WEB-INF/classes or the JAR file in WEB-INF/lib in your JasperReports Server directory, for example /Applications/jasperreports-server-7.1.0/apache-tomcat/webapps/jasperserver-pro/WEB-INF/lib.
Recommended Comments
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now