Opinions - Singleton or Connection Pool?

[Jeffrey Hunter]

Hi all,
So, I'm calling for opinions. Suppose I have the following two options to connect to a database:

create a Singleton class which creates and maintains a Connection object as an instance member; when an outside class requires connectivity, the class accesses the Singleton's Connection object; at the end of the application's lifecycle, the Singleton class releases (closes) the Connection

implement a Connection pool (self-explanatory, I hope)

Now, most of the time we'd want some type of Connection pool, but is there a case where it may be too much overhead? Suppose the number of users is minimal? Would it be better to implement some type of Singleton pattern, where the same Connection object is re-used, over and over?

Thanks for your thoughts.

[Julian Kennedy]

The only situations that I can conceive of where having a single connection makes more sense is where you have only a single user or your database is in single user mode, i.e. can only support one connection at a time. There's really no overhead in using a connection pool. The implementation is typically all done for you in your JDBC driver or app server. You just have to call the methods. In a multi-user application, without connection pooling, you have no database concurrency.

Jules

[Jeanne Boyarsky]

I would only use a singleton if you are running the application on a client's machine, like from an applet. If you have an app server available, there is no reason too rewrite connection pool code.

I wouldn't use a singleton in the case Jules brought up about a single user database. If you decided to upgrade the database, you would have to update the code too. I like to keep my code as independent of the database as possible.