CODEWITHSUNDEEP
database-connection
Snake
Snake

Reputation: 291

Is it smart to open multiple connections on the same database at the same time or shall I avoid that?

I'm creating an application where I need to connect with a database. One thing I need to do often is to get a list of Users from the database by a groupId.

In every method I need this information, I make a command which retrieves the information in stead of (how I learned in school) having a separate method that gets the information and passes it on.

I've done this because I want to set up as few connections as possible, open at the same time. However, I'm not sure which way is best. I searched for answers on the Internet but didn't find any. What is the best way to do this?

If I haven't been clear enough about this issue, just ask.

Upvotes: 3

Views: 9595

Answers (1)

user4398985
user4398985

Reputation:

Is it smart to open two connections to the same database at the same time or should I avoid that?

Yeah, its smart to set up multiple connection using connection pool for many reasons. Using a connection/statement pool, you can reuse existing connections/prepared statements, avoiding the cost of initiating a connection, parsing SQL etc.

Opening/Closing database connections is an expensive process and hence 

connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool. It facilitates 

reuse of the same connection object to serve a number of client requests. 

Every time a client request is received, the pool is searched for an 

available connection object and it's highly likely that it gets a free 

connection object.

  1. I suggest you, implement singleton pattern (or any other) and set up a connection pool in your project.

  2. Make a class "DatabaseConnection", you can have different methods, one of them as you pointed out getAllValues() {}

  3. You can implement different patterns AbstractFactory, DAO, and singleton. Singleton is better for a beginner, singleton is not as easy also. Please refer here more about singleton: What is an efficient way to implement a singleton pattern in Java?

  4. Need Code to create Connection Pool in java

  5. I worked on HIKARI for connection pooling, there are also others if you can search. Why we need a connection pooling for JDBC?

Fundamentally reason for a connection pool?

Imagine you built a website like I did, Movie Rental Application for my student project. My professor came up and asked me a question which blew my mind.

You have only one class (object) for database connection (singleton), what if a million users requests for some results obj.getAllUser() at once. How do you handle this scenario? and he left

I was baffled and I immediately went on Google and typed the same problem and saw one stackoverflow post...connection pool is the answer. I went straight to him and said I will handle this using a connection pool. He was impressed and I got 'A' in his class. Hope this helps.

Upvotes: 1

Related Questions