persistent database connection

Question

I'm making a small program in java that is going to run on an embedded device. the purpose of it is to store some values (let's say temperature) in a database located in a cloud.

• Scenario 1: data gets stored every possible cycle (connect-store-store-store... -disconnect)
• Scenario 2: data gets stored every 5 minutes (connect-store-wait-store-wait... -disconnect)
• Scenario 3: data gets stored every hour

Is it better to maintain a constant connection with the database or to connect only when needed? Why?

What would happen if deployed 100 of this devices (one can never have enough temperature data)?

Answer 1

Unless there are special circumstances, it's almost always connect only when needed. There are several advantages:

• Resources are held for the shortest time possible, thus maximising their availability.
• The lifetime scope of connections is controlled at a single point in code, minimising the chance of resource leaks.
• Data is written frequently, thus minimising loss of data due to crashes.
• Retry logic is simpler, since connection state is localised to a single point in code

In general: Open connections as late as possible, as briefly as is possible, and close as quickly as possible

(Many database systems implement connection pooling which makes this process very efficient)

Why always close Database connection?