JSqlServerBulkInsert with Spring JDBC Connection is Closed

When we use JSqlServerBulkInsert, we provide a database connection from the pool to bulk insert data even in Spring JDBC. As a result, it might not work well with Spring. Either we could get “Connection is Closed” errors or cause a database connection leak. The JSqlServiceBulkInsert is the Java equivalent of SqlServiceBulkInsert, a .NET library to bulk insert data to an SQL Server.

Sample Code Snippet

Using SqlServerBulkInsert in Java is a bit old-school, like pre-Spring. The issue is understandable as it has its roots in .NET. How do we use SqlServerBulkInsert in Java? Consider the following Java codes from their Integration test.

...
 SqlServerBulkInsert<Person> bulkInsert = new SqlServerBulkInsert<>(mapping);
        // Measure the Bulk Insert time:
        MeasurementUtils.MeasureElapsedTime("Bulk Insert 1000000 Entities", () -> {
            // Now save all entities of a given stream:
            bulkInsert.saveAll(connection, persons.stream());
        });
...

...

SqlServerBulkInsert<Person> bulkInsert = new SqlServerBulkInsert<>(mapping);

// Measure the Bulk Insert time:

MeasurementUtils.MeasureElapsedTime("Bulk Insert 1000000 Entities", () -> {

// Now save all entities of a given stream:

bulkInsert.saveAll(connection, persons.stream());

});

...

JSqlServerBulkInsert “Connection is Closed” with Spring JDBC

Best practices suggest that we should always close database connections. We would naturally use the try-catch-finally. Then, close the connection in the finally clause. However, JSqlServerBulkInsert will encounter the “Connection is closed” errors. Out of desperation for a quick fix, some of us may resort to not closing database connections in Spring. But this will lead to a database connection leak.

JSqlServerBulkInsert Could Cause Database Connection Leak

We introduce database connection leaks when we skip closing database connections for JSqlServerBulkInsert in Spring JDBC. Gosh, another headache! How do we fix both issues?

A Simple Solution in Spring JDBC the Connection Issues

We could use a simple solution to avoid the two issues regarding database connection. However, there is a small problem – Hiraki will always detect a database connection leak and give off warnings.

[HikariPool-1 housekeeper] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for org.postgresql.jdbc.PgConnection@3fd29f40 on thread tornadofx-thread-2, stack trace follows
java.lang.Exception: Apparent connection leak detected
...

[HikariPool-1 housekeeper] WARN com.zaxxer.hikari.pool.ProxyLeakTask - Connection leak detection triggered for org.postgresql.jdbc.PgConnection@3fd29f40 on thread tornadofx-thread-2, stack trace follows

java.lang.Exception: Apparent connection leak detected

...

The solution is to cache a database connection for JSqlServerBulkInsert. The database pool will always have one less connection for other operations. In a post-construct method, we could create a single component that gets a connection from a DataSource object.

// Untested - but you get the idea
@Service
public class JSqlServerBulkInsertConnectionCache {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private Connection connection;

    @PostConstruct
    public void init() {
        connection = jdbcTemplate().getDataSource().getConnection();
    }
    
    public Connection getConnection()
    {
        return connection;
    }
}

// Untested - but you get the idea

@Service

public class JSqlServerBulkInsertConnectionCache {

@Autowired

private JdbcTemplate jdbcTemplate;

private Connection connection;

@PostConstruct

public void init() {

connection = jdbcTemplate().getDataSource().getConnection();

}

public Connection getConnection()

{

return connection;

}

Then, we auto-wire the service in our class and retrieve and use the connection object.

The Right Solution to Using JSqlServerBulkInsert

The solution we mentioned has one major drawback – we will have a stale connection after a DB server restart (or something to that effect). Using the stale connection in any SQL operations will result in another “Connection is closed.” runtime error! We can do better!

Consider the following new codes wherein we use the DataSourceUtils class from Spring. First, we get a connection object and then release it using the releaseConnection method, passing the DataSource object along.

...
 Connection connection = DataSourceUtils.getConnection(this.dataSource);

 try {
    SqlServerBulkInsert<Person> bulkInsert = new SqlServerBulkInsert<>(mapping);
   
    // Measure the Bulk Insert time:
    MeasurementUtils.MeasureElapsedTime("Bulk Insert 1000000 Entities", () -> {
        // Now save all entities of a given stream:
        bulkInsert.saveAll(connection, persons.stream());
    });
 catch(Exception e) {
...
 }
 finally {
    DataSourceUtils.releaseConnection(connection, this.dataSource);
 }
...

...

Connection connection = DataSourceUtils.getConnection(this.dataSource);

try {

SqlServerBulkInsert<Person> bulkInsert = new SqlServerBulkInsert<>(mapping);

// Measure the Bulk Insert time:

MeasurementUtils.MeasureElapsedTime("Bulk Insert 1000000 Entities", () -> {

// Now save all entities of a given stream:

bulkInsert.saveAll(connection, persons.stream());

});

catch(Exception e) {

...

}

finally {

DataSourceUtils.releaseConnection(connection, this.dataSource);

}

...

This solution helps us to avoid stale connections (from naively caching them). Also, we may no longer need to close the connections explicitly or via try-resource statements.