Connection Pooling with Commons DBCP - spot the problem?

[Mikey Kelly]

Hello,
I am using Commons DBCP to pool my database connections. Sometimes there is just an hourglass cursor when trying to access my application - looks like there are no free connections available.

Am I doing anything wrong in my code? Should I be closing the datasource, for example? Please see below.

Class for retrieving datasource:

package mypackage.name.here
 
import javax.sql.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
import org.apache.commons.dbcp.*;
import org.apache.commons.pool.*;
import org.apache.commons.pool.impl.*;
 
public class DatabaseUtil {
    
    static PoolingDataSource dataSource = null;
    public static final String DB_PROPERTIES = "myextension.properties";
     
    
    //  Returns a DataSource that implements connection pooling
    public static synchronized DataSource getDataSource(String configDir) throws Exception {

    if (dataSource == null){
    
        Properties p = new Properties();
    
try{
File cfg = new File(configDir, DB_PROPERTIES);
    p.load(new FileInputStream(cfg));
} catch (IOException e) {
System.out.println(e);
}
      
    String type = p.getProperty("dbType");
    String url = p.getProperty("url");
    String ip = p.getProperty("dbIP");
    String name = p.getProperty("dbName");
    String port = p.getProperty("dbPort");
    String sid = p.getProperty("dbSid");
    String fullUrl = null;
    if (type.trim().equals("ms")){
    // MSSQL
    fullUrl = url+ip+"/"+name;
    } else {
    // ORACLE
    fullUrl = url+ip+":"+port+":"+sid;
    }
    
    BasicDataSource ds = new BasicDataSource();
    ds.setUrl(fullUrl);
    ds.setDriverClassName(p.getProperty("driver"));
    ds.setUsername(p.getProperty("usr"));
    ds.setPassword(EncDoc.decode(p.getProperty("pass")));

    ds.setMaxActive(100);
 
    // Create a PoolableDataSource
    ObjectPool connectionPool = new GenericObjectPool(null);
    ConnectionFactory connectionFactory = new DataSourceConnectionFactory(ds);
    PoolableObjectFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);
    dataSource = new PoolingDataSource(connectionPool);
    }
 
    return dataSource;
    }
}

Sample JSP page:

 
...
DataSource dataSource = DatabaseUtil.getDataSource(configDir);
Connection myConnect = null;
Statement myStatement = null;
ResultSet myResults = null;
 
try{
myConnect = dataSource.getConnection();
myStatement = myConnect.createStatement();
...
myQuery = "select count (*) as 'total' from admin where courseID = '"+courseID+"'";
 
myStatement.executeQuery(myQuery);
myResults = myStatement.getResultSet();
while (myResults.next()) count = myResults.getInt("total");
 
...
 
}catch (SQLException e) {
    out.println("SQL Error: <br><br>"+e);
} finally {
    try { myResults.close(); } catch(Exception e) {}
    try { myStatement.close(); } catch(Exception e) { }
    try { myConnect.close(); } catch(Exception e) { }
}

Thanks all.
[ November 04, 2005: Message edited by: Mikey Kelly ]