How should i have my connection to the database

Question

Hii ,

I am developing an application where I ran across into this problem ...I have this GUI where in I have many buttons ... Each button performs some action like INSERT , SELECT , UPDATE , DELETE , VIEW etc... on a database . My question is ...

Is it better to have a single connection open for the entire lifetime of this particular class and close when this class is no longer in use ... or should i open a DB Connection on the fly and close immediately the operation is performed...

If i choose the first case , what if we require to do some processing and only then can we perform the operation ...in which case the connection is going to be idle ..

If i choose the second case , will it not impose any overhead on frequent opening and closing of the connections... given the user can only perform only DB Operations from this screen (lets say) ...

If we require multiple connections , is Connection Pooling the best option ... ?

I actually dont know and please correct me if i am wrong ... Does establishing a DB Conncetion impart any significant overhead to the system in terms of memory ... ?

Answer 1

Generally speaking, it's recommended to open connection as late as possible and then close it as soon as possible. And yes, connection pooling works just fine in most major systems, making a cost of obtaining a new connection almost negligible compared to actual call to the database.

Answer 2

Ravi, maybe the best answer for you is neither. You can use an existing Java framework like Spring (http://www.springsource.org/) that handles database connections for you. Using a framework lets you focus primarily on the business logic; the framework handles most of the boiler plate database connections. If you find the database component is running slowly after you complete the first iteration of your app, you can optimize it then.

Answer 3

Is it better to have a single connection open for the entire lifetime of this particular class and close when this class is no longer in use ... or should i open a DB Connection on the fly and close immediately the operation is performed...

It is always required to have a connection open and do all your transaction and once you've finished with your transactions, commit and then close.

In concurrent systems, worker threads are created for every session call to your application, so you don't want every JDBC transaction connections created for every worker thread. The solution is Connection Pooling

From Oracle's MySQL Page

What is Connection Pooling?

Connection pooling is a technique of creating and managing a pool of connections that are ready for use by any thread that needs them.

This technique of "pooling" connections is based on the fact that most applications only need a thread to have access to a JDBC connection when they are actively processing a transaction, which usually take only milliseconds to complete. When not processing a transaction, the connection would otherwise sit idle. Instead, connection pooling allows the idle connection to be used by some other thread to do useful work.

In essence, connection pooling allows that idle connections can be used by another thread instead of creating connections for every thread.

Just bear in mind, that database connections should be opened for a short duration of time (or optimal time, due to timeout) such that your SQL queries should be very optimal for the connection to connect to DB, get results and return objects very efficiently.

Hope this helps your situation.

Answer 4

You might consider the following two points when handling a database in your application:

1. Single-User database/Embedded database: The database is exclusive to your application.
2. Single-User application: Your application is a desktop application.
3. Multi-User application: the typical web application.

For 1. and 2. there is no point against keeping one exclusive connection for the application. Just make sure the transaction logic works the right way. Also don't forget to handle a connection timeout gracefully.

For 3. you definitely want to have Connection Pooling and aquire one of the pooled connection only for the time you really need it.

Concerning the overhead: I don't think it's a memory overhead but a possible performance loss, especially when using a remote database, because you need to initialize a new connection resource everytime you do a 'fresh' connect.

Also depending on the frameworks/libraries you use you might want to look into how they can support your database programming.