pooled connection not being freed after used to create JasperReport

Question

We have a spring boot web application that we already using in prod. Recently the business asked to produce some EOD reconciliation reports as non editable PDFs.

We compiled some Jasper reports and start generating the reports using some code like below:

private void generatePdfReport(final Report report) throws SQLException, JRException {
    final JasperReport jasperReport = JASPER_REPORT_MAP.get(report);
    if (jasperReport == null) {
        throw new UnsupportedOperationException("The report is not supported: " + report.getName());
    } else {
        Connection connection = null;
        try {
            connection = dataSource.getConnection();
            final JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, dataSource.getConnection());
            final JRPdfExporter exporter = new JRPdfExporter();
            exporter.setExporterInput(new SimpleExporterInput(jasperPrint));
            exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(report.getFormattedFilename() + Report.EXT_PDF));
            exporter.setConfiguration(reportConfig);
            exporter.setConfiguration(exportConfig);
            exporter.exportReport();
            log.info("PDF Report ({}) exported successfully!", report.getName());
        } finally {
            if (connection != null) {
                log.info("about to close db connection");
                connection.close();
                connection = null;
            }
        }
    }
}

The reports run fine but after producing ten reports we started getting timeout exceptions from Hitaki data source complaining it could not acquire a connection in less than 30 seconds.

The default connection pool size for Hitaki data source is ten and putting some breakpoints in the Hitaki classes we could see that all ten connections are marked as IN_USE. Changing the data source to commons-dbcp did not change much. No timeouts this time but the processing blocked indefinitely after generating eight reports. Eight is the default pool size for dbcp data source.

Concluding it was not a data source problem we put some break points in the jasper jars and noticed that both result set and prepared statements created were properly closed.

Our Hitaki/DBCP datasources are wrapped by a spring TransactionAwareDataSourceProxy instance. Taking out that wrapping changed nothing.

In the end I replaced the datasource with a very basic data source (obviously not suitable for production) and all worked OK. See the code below showing as commented what we tried:

    @Bean
    public DataSource dataSource() {
        final String url = env.getProperty("database.url");
        final String userName = env.getProperty("gmm.schema");
        log.info("Creating DataSource for {}@{}", userName, url);
        // final HikariDataSource dataSource = new HikariDataSource();
        // final BasicDataSource dataSource = new BasicDataSource();
        final SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
        dataSource.setUrl(url);
        dataSource.setUsername(userName);
        dataSource.setPassword(env.getProperty("gmm.password"));
        // dataSource.setDriverClassName(env.getProperty("database.driver"));
       dataSource.setDriverClass(oracle.jdbc.driver.OracleDriver.class);
        return dataSource;
        // return new TransactionAwareDataSourceProxy(dataSource);
    }

Now my question:

What would cause such behavior and how should I fix or investigate further. There are no connections problems if we comment out the reporting job but on the other hand I don't see how using a very basic data source will fix this.

Being new to Jasper Reports I just hope I am not properly using it.

Thank you in advance.

Check this answer https://stackoverflow.com/a/64985187/6230007 using `DataSourceUtils.getConnection` — Panos K., Nov 24 '20 at 11:09

score 1 · Accepted Answer · answered May 28 '18 at 05:48

Try to Pass the connection object you created to fillReport method.

connection = dataSource.getConnection();
final JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, dataSource.getConnection());

to

connection = dataSource.getConnection();
        final JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null, connection);

Cheers

score 1 · Answer 2 · answered Dec 02 '20 at 15:06

For me worked with DataSourceUtils:

connection = DataSourceUtils.doGetConnection(dataSource);
JasperPrint jasperPrint = JasperFillManager.fillReport(report, params, connection);

...

DataSourceUtils.releaseConnection(connection, dataSource);

With the simple connection after every call I had -1 idle connection pool.

score 1 · Answer 3 · answered Feb 16 '21 at 14:45

1

For me try-with-resources worked:

JasperPrint jasperPrint;
    try (Connection connection = dataSource.getConnection()) {
      jasperPrint = JasperFillManager.fillReport(report, params, connection);
    }

answered Feb 16 '21 at 14:45

smaudi

83
7

pooled connection not being freed after used to create JasperReport

3 Answers3