Mahdi Nouri Mahdi Nouri - 6 months ago 73
MySQL Question

When should we close database connection

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download