3

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.

Snake
  • 283
  • 4
  • 10
  • What you do is correct, you don't want to be cluttered with all the info you don't need. But again you don't want to instantiate new db connections every time you need something while you already have an active connection. That could cause some threading issues and inconsistent data, what I tend to do is to use a **singleton pattern** for my db connection but there are much better ppl here than me to explain the rationale behind. So I'll just leave this here. – umutto Sep 29 '15 at 12:30
  • Thanks, I'll look into using the Singleton – Snake Sep 29 '15 at 12:48

1 Answers1

1

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.

Community
  • 1
  • 1
  • 1
    Thanks, I see I need to do some more research on what a connection pool does and how to implement it. – Snake Sep 30 '15 at 07:17