Closing connection in HikariPool [duplicate]

Question

I have a standalone java application that gets streams of messages, batches and inserts them to a SQL Server database using Hikaricp.

Currently what I do is the following:

• Get a connection from the pool.
• Create a prepared statement to insert using the connection
• Execute the batch insert.

Note that I never close the connection! And once I reach the maxPoolSize (20), I get an error when I tried to get a new connection.

Should I be closing the connection after every batch insert?

• Get a connection from the pool.
• Create a prepared statement to insert using the connection
• Execute the batch insert.
• Close the prepared statement & connection.

However, this means that I am incurring the cost of getting a connection from the pool + creating a new prepared statement after every batch insert.

Is this the recommended approach or are there any alternatives which can reduce this extra cost?

Answer 1

You need to close connection. usually pools return connections wrapped into another object. And when you call close on wrapper it simply marks internal database connection as free and returns it to the pool.

Also you can reuse existing PreparedStatement objects if they are the same. If each of your tasks has to use its unique PreparedStatement (due to your business logic) then there is no other way and you have to create new PreparedStatement for each task.