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");

Answer 1

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.