Java

JSqlServerBulkInsert with Spring JDBC Connection is Closed

When we use JSqlServerBulkInsert, we explicitly need to 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

To avoid the two issues regarding database connection, we could use a simple solution. 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 to choose from 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.

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