Java

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.

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.

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.

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.

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.

Got comments or suggestions? We disabled the comments on this site to fight off spammers, but you can still contact us via our Facebook page!.


You Might Also Like