How to handle connection pooling with c3p0

Question

hi I have created connection pooling for my Java Stand alone program.

Okay to be more clear ..

  private static ComboPooledDataSource = pooledDataSource;

public static void createDataSource(String dbName)
 {

          if(dbName.equals("DB_ONE"))
         {
              pooledDataSource =  new ComboPooledDataSource();

               // Here passing parameters will be different for DB_ONE              
            pooledDataSource.setDriverClass(dbDriver);
            pooledDataSource.setJdbcUrl(dbUrl);
            pooledDataSource.setUser(userID);
            pooledDataSource.setPassword(password);
            pooledDataSource.setMinPoolSize(Integer.parseInt(minCon));
            pooledDataSource.setMaxPoolSize(Integer.parseInt(maxCon));
            pooledDataSource.setMaxIdleTime(Integer.parseInt(maxIdleTime));
            pooledDataSource.setInitialPoolSize(Integer.parseInt(intialPoolSize));
            pooledDataSource.setPreferredTestQuery(testQuery);
        }
        if(dbName.equals("DB_TWO"))
       {
              pooledDataSource =  new ComboPooledDataSource();

               // Here passing parameters will be different for DB_TWO              
            pooledDataSource.setDriverClass(dbDriver);
            pooledDataSource.setJdbcUrl(dbUrl);
            pooledDataSource.setUser(userID);
            pooledDataSource.setPassword(password);
            pooledDataSource.setMinPoolSize(Integer.parseInt(minCon));
            pooledDataSource.setMaxPoolSize(Integer.parseInt(maxCon));
            pooledDataSource.setMaxIdleTime(Integer.parseInt(maxIdleTime));
            pooledDataSource.setInitialPoolSize(Integer.parseInt(intialPoolSize));
            pooledDataSource.setPreferredTestQuery(testQuery);

         }

    }

    public Connection getConnection(String dbName)
     {
          createDataSource(dbName);
       return  pooledDataSource.getConnection();

        }

In my DAO class whenever I call this method getConnection(dbName)

Swithcing between Two differnt databases frequesntly.. for every connection calling that particualr database pool is getting created again.

I dont want to create duplicate pools ...

How to avoid creating new connection pool ?

any help Thanks in adavance.

Answer 1

Maybe you can think about something like this :

private static Map<String, ComboPooledDataSource> dataSources;

static {
    dataSources = new Map<String, ComboPooledDataSource>();
}

public static void createDataSource(String dbName) {
    ComboPooledDataSource pooledDataSource =  new ComboPooledDataSource();
    if(dbName.equals("DB_ONE")) {
        // Here passing parameters will be different for DB_ONE              
        pooledDataSource.setDriverClass(dbDriver);
        pooledDataSource.setJdbcUrl(dbUrl);
        pooledDataSource.setUser(userID);
        pooledDataSource.setPassword(password);
        pooledDataSource.setMinPoolSize(Integer.parseInt(minCon));
        pooledDataSource.setMaxPoolSize(Integer.parseInt(maxCon));
        pooledDataSource.setMaxIdleTime(Integer.parseInt(maxIdleTime));
        pooledDataSource.setInitialPoolSize(Integer.parseInt(intialPoolSize));
        pooledDataSource.setPreferredTestQuery(testQuery);
        dataSources.put(dbName, pooledDataSource);
    } else if(dbName.equals("DB_TWO")) {
        // Here passing parameters will be different for DB_TWO              
        pooledDataSource.setDriverClass(dbDriver);
        pooledDataSource.setJdbcUrl(dbUrl);
        pooledDataSource.setUser(userID);
        pooledDataSource.setPassword(password);
        pooledDataSource.setMinPoolSize(Integer.parseInt(minCon));
        pooledDataSource.setMaxPoolSize(Integer.parseInt(maxCon));
        pooledDataSource.setMaxIdleTime(Integer.parseInt(maxIdleTime));
        pooledDataSource.setInitialPoolSize(Integer.parseInt(intialPoolSize));
        pooledDataSource.setPreferredTestQuery(testQuery);
        dataSources.put(dbName, pooledDataSource);
    }
}

public synchronized Connection getConnection(String dbName) {
    if (!dataSources.containsKey(dbName)) {
        createDataSource(dbName);
    }
    return  dataSources.get(dbName).getConnection();
}

With this solution, you create your data sources only when you need (but keep it alive after its creation) and you can access connection from the data source you want without creating new pool.

Answer 2

look inside method:

return  pooledDataSource.getConnection();

whenever you call method you are creating new connection. Just put pooledDataSource.getConnection(); inside static content:

//change private variable to Connection
private Connection connection
static{

         pooledDataSource =  new ComboPooledDataSource();

        pooledDataSource.setDriverClass(dbDriver);
        pooledDataSource.setJdbcUrl(dbUrl);
        pooledDataSource.setUser(userID);
        pooledDataSource.setPassword(password);
        pooledDataSource.setMinPoolSize(Integer.parseInt(minCon));
        pooledDataSource.setMaxPoolSize(Integer.parseInt(maxCon));
        pooledDataSource.setMaxIdleTime(Integer.parseInt(maxIdleTime));
        pooledDataSource.setInitialPoolSize(Integer.parseInt(intialPoolSize));
        pooledDataSource.setPreferredTestQuery(testQuery);
        //here is new line
        connection = pooledDataSource.getConnection();
}

and in method do:

public Connection getConnection()
     {
       return  connection;

        }