Connection Pooling, anyone help me please!

[Gustavo Santos]

Hello guys...
I'm having a bit of headache trying to figure out what's going on with my Connection Pooling since it's not working as it supposed to... it's been opening up to 150 connections for some reason which I don't know why... I've made it as simples as possible but the problem still remains.

Could anyone give me a hand on this one, Please?

package dao;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;

public class ConnectionFac {


    public static boolean getConnection(String tipo, String domain){	

        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        DataSource dataSource = setupDataSource("jdbc:postgresql://10.0.0.210:5432/myDB");
      
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;
        boolean result = false;

        try {

            conn = dataSource.getConnection();

            stmt = conn.createStatement();

            
            if(tipo.equalsIgnoreCase("isInDB")){

            	rset = stmt.executeQuery("select * from dom_com_br where dominio = '" + domain + "'");

    			if(rset.next() == true){
    				result = true;
    			}

            }else if(tipo.equalsIgnoreCase("setDomain")){
            	
            	stmt.executeUpdate("insert into dom_com_br (dominio) values ('" + domain + "')");
        		
        		System.out.println("*** Dominio inserido: " + domain + " ***");
            }
            
        } catch(SQLException e) {
            e.printStackTrace();
        } finally {
            try { rset.close(); } catch(Exception e) { }
            try { stmt.close(); } catch(Exception e) { }
            try { conn.close(); } catch(Exception e) { }
        }
        
        return result;
    }

    public static DataSource setupDataSource(String connectURI) {

        ObjectPool connectionPool = new GenericObjectPool(null);

        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI, "postgres", "mypassword");

        PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory,connectionPool,null,null,false,true);

        PoolingDataSource dataSource = new PoolingDataSource(connectionPool);

        return dataSource;
    }  
}

[Paul Clapham]

I notice that your setupDataSource method creates a new ConnectionPool every time it's called. Correct me if I misunderstand the design, but shouldn't there just be one ConnectionPool which is accessed repeatedly?

[Gustavo Santos]

Paul Clapham wrote:I notice that your setupDataSource method creates a new ConnectionPool every time it's called. Correct me if I misunderstand the design, but shouldn't there just be one ConnectionPool which is accessed repeatedly?

Exactly! so where do you think I should place it in a way that it opens only one ConnectionPool to be accessed repeatedly?

Thanks a lot for your help!

PS: I got this source from the Apache website and I thought it was right... I haven't made any changes in the code itself besides the selects and inserts.

[Paul Clapham]

Well, if you have data that has to last longer than a method call, the usual simple way to deal with that in Java is to have class-level variables for the data. That's not specific to JDBC, though, that's just basic Java programming.