When should we close database connection

Question

I'm using Spark to create a rest API.

also I'm using ormLite + mysql database for persistent database.

now the question is when should i close the connection to the database ?

or should i close the connection after each request ?

This is how I'm connecting to the database :

JdbcConnectionSource connectionSource = JdbcConnectionSource(databaseUrl);

connectionSource.setUsername("myUsername");
connectionSource.setPassword("myPassword");

After each request is punishing. After you're *done* is a better plan. — tadman, Nov 24 '17 at 21:37
I thinks it's duplicate question. to find answers see: https://stackoverflow.com/questions/11356937/good-practice-to-open-close-connections-in-an-asp-net-application , https://stackoverflow.com/questions/4439409/open-close-sqlconnection-or-keep-open , https://stackoverflow.com/questions/4111594/why-always-close-database-connection — Gholamali Irani, Nov 24 '17 at 21:47

score 4 · Answer 1 · edited Nov 27 '17 at 23:17

Database connections are expensive to open and thus valuable.

I would suggest that you investigate if you can use a connection pool that manages opening and closing connections in the background.

I have used the connection pool developed for Tomcat with some success. It is possible to use it standalone and thats what I do in a couple of Sparkjava applications.

The connection is returned to the pool when you call close() on it. This means that you close the connection after each call. The connection may be re-used when you ask the pool for a connection. Unless it is an old connection that the pool kills. These are details you don't have to care about as an application developer. From your perspective, you get a connection when you need it.

+1. Here is the docs about how to use ORMLite with a pooled connection source: http://ormlite.com/docs/pooled-cs — Gray, Nov 27 '17 at 23:16

score 0 · Accepted Answer · answered Nov 29 '17 at 19:49

Ok so actually based on Gray's comment this is how you do it with Spark :

JdbcPooledConnectionSource connectionSource = JdbcPooledConnectionSource(databasePath, username, password);

What I'm doing is using JdbcPooledConnectionSource instead of JdbcConnectionSource so we don't need to create a new connection for every reqeust or a single connection for all of the reqeusts.

But this is not all of it, we need some additional things for better performance :

connectionSource.setMaxConnectionsFree(10)
connectionSource.setMaxConnectionAgeMillis(20000)
connectionSource.setCheckConnectionsEveryMillis(5000)

By doing this you don't need to close connections by your self.

BTW if you think that this parameters need to change please let me know ;)

When should we close database connection

2 Answers2