C3p0 initializes new pool on each database query

A common issue!

By default, C3P0 (a popular connection pooling library for Java) initializes a new pool on each database query. This can lead to performance issues and increased resource usage.

To avoid this, you can configure C3P0 to reuse the existing pool by setting the initialPoolSize and maxPoolSize properties. Here's an example:

import com.mchange.v2.c3p0.ComboPooledDataSource;

// Create a ComboPooledDataSource instance
ComboPooledDataSource cpds = new ComboPooledDataSource();

// Set the initial pool size and maximum pool size
cpds.setInitialPoolSize(3);
cpds.setMaxPoolSize(10);

// Set the acquire increment (how many connections to add to the pool when it's exhausted)
cpds.setAcquireIncrement(2);

// Set the timeout for getting a connection from the pool
cpds.setCheckoutTimeout(30000);

// Set the idle timeout (how long a connection can be idle before it's closed)
cpds.setIdleConnectionTestPeriod(300);

// Set the test query (used to test the connection)
cpds.setTestQuery("SELECT 1");

// Set the username and password
cpds.setUser("your_username");
cpds.setPassword("your_password");

// Set the JDBC URL
cpds.setJdbcUrl("jdbc:mysql://localhost:3306/your_database");

// Set the driver class
cpds.setDriverClass("com.mysql.cj.jdbc.Driver");

// Set the properties
cpds.setProperties({
    "user", "your_username",
    "password", "your_password"
});

In this example, we set the initialPoolSize to 3, which means that C3P0 will create 3 connections to the database initially. The maxPoolSize is set to 10, which means that C3P0 will create up to 10 connections to the database if needed. The acquireIncrement is set to 2, which means that C3P0 will add 2 connections to the pool when it's exhausted.

By setting these properties, you can ensure that C3P0 reuses the existing pool instead of creating a new one on each database query. This can significantly improve performance and reduce resource usage.