Thread pool connection vs Singleton design pattern to get a single database connection

Question

I am really getting confused between these 2 now as below : 1. is the returning of only a Singleton instance to a db connection during the entire run time of a JAVA app 2. is the concept of Thread pool connections in general... I mean if we are planning to have only a singleton instance to a db connection obj, why even have a concept of pools, though i do get what exactly a pool is used for

Are these 2 not very opposite concepts, or am i mixing up anything here...?

Answer 1

is the returning of only a Singleton instance to a db connection during the entire run time of a JAVA app?

you may not want to return a Singleton object for a database connection. You can choose to do if database concurrency not required. In a multi threaded environment best option is to go for Connection pool.

is the concept of Thread pool connections in general..

Establishing a database connection is a very resource-intensive process and involves a lot of overhead. Moreover, in a multi-threaded environment, opening and closing a connection can worsen the situation greatly.

Creating JNDI in server and use it in your web app.

Context context=new InitialContext();
DataSource dataSource=(DataSource)
   context.lookup("jdbc/test_jndi");

so when DataSource uses connection pooling, the lookup return a connection from the pool of available connection objects. If there is no available connection, the lookup creates a new connection.

Connection connection=dataSource.getConnection
   ("testuser","testpwd");
// ...
connection.close();

Once the application is done with database processing, it explicitly closes the connection. This makes the connection available again for reuse again. The closing event of the pooled connection signals the pooling module to restore back to the connection pool.

Any resource shared requires overhead of handling concurrent access so you want to reduce that by not having singletons always. Moreover to reduce resource-intensive process connection pooling is preferred.