Why we need a connection pooling for JDBC? [closed

Question

• What are the benefits of using a JDBC connection pooling tool like DBCP or c3p0 ?

• in case of a small CRUD application with one user, can we just create one connection session as a singleton ?

PS: I'm building a small javafx application back-ended with tiny h2 database (5 tables).

Answer 1

From Jon Skeet's answer to What is the benefit of Connection and Statement Pooling?:

Creating a network connection to a database server is (relatively) expensive. Likewise asking the server to prepare a SQL statement is (relatively) expensive.

Using a connection/statement pool, you can reuse existing connections/prepared statements, avoiding the cost of initiating a connection, parsing SQL etc.

And the following, from Kent Boogaart's answer:

I am not familiar with c3p0, but the benefits of pooling connections and statements include:

1. Performance. Connecting to the database is expensive and slow. Pooled connections can be left physically connected to the database, and shared amongst the various components that need database access. That way the connection cost is paid for once and amortized across all the consuming components.

2. Diagnostics. If you have one sub-system responsible for connecting to the database, it becomes easier to diagnose and analyze database connection usage.

3. Maintainability. Again, if you have one sub-system responsible for handing out database connections, your code will be easier to maintain than if each component connected to the database itself.

Answer 2

Creating connections is costly and it doesn't make sense to create a new connection for each transaction that might only take a few milliseconds. Managing database connections in a pool means that applications can perform database transactions in a way that avoid the connection creation time (The connections still need to be created, but they are all created at startup). The downside is that if all connections are in use, and another connection is required, the requesting thread will have to wait until a connection is returned to the pool.