Opening a new database connection for every client that connects to the server application?

Question

I am in the process of building a client-server application and I would really like an advise on how to design the server-database connection part. Let's say the basic idea is the following:

1. Client authenticates himself on the server.
2. Client sends a request to server.
3. Server stores client's request to the local database.

In terms of Java Objects we have

• Client Object
• Server Object
• Database Object

So when a client connects to the server a session is created between them through which all the data is exchanged. Now what bothers me is whether i should create a database object/connection for each client session or whether I should create one database object that will handle all requests.

Thus the two concepts are

1. Create one database object that handles all client requests
2. For each client-server session create a database object that is used exclusively for the client.

Going with option 1, I guess that all methods should become synchronized in order to avoid one client thread not overwriting the variables of the other. However, making it synchronize it will be time consuming in the case of a lot of concurrent requests as each request will be placed in queue until the one running is completed.

Going with option 2, seems a more appropriate solution but creating a database object for every client-server session is a memory consuming task, plus creating a database connection for each client could lead to a problem again when the number of concurrent connected users is big.

These are just my thoughts, so please add any comments that it may help on the decision.

Thank you

Answer 1

Option 3: use a connection pool. Every time you want to connect to the database, you get a connection from the pool. When you're done with it, you close the connection to give it back to the pool.

That way, you can

• have several clients accessing the database concurrently (your option 1 doesn't allow that)
• have a reasonable number of connections opened and avoid bringing the database to its knees or run out of available connections (your option 2 doesn't allow that)
• avoid opening new database connections all the time (your option 2 doesn't allow that). Opening a connection is a costly operation.

Basically all server apps use this strategy. All Java EE servers come with a connection pool. You can also use it in Java SE applications, by using a pool as a library (HikariCP, Tomcat connection pool, etc.)