Commons DBCP pooling problem

Hello,
I am using Commons DBCP to pool my sqlserver 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.

.

Relevant Pages

  • Re: BindException (Address already in use) when connecting to MSSQL
    ... Your code creates many connections to the same local ... > DataSource is merely for simplicity, the behaviour is, again, the ... actually uses a connection pool if you ensure that the pool has a ... necessary in the single-threaded test scenario you present. ...
    (comp.lang.java.programmer)
  • Re: How to Change datasource, username and password on batch import
    ... You will probably find that the wizard generated copies of connections and you probably only have around 4 actual connections. ... I want to be able to store the DTS package into a file so ... give the datasource, user and password to the DTS for ALL of my ...
    (microsoft.public.sqlserver.dts)
  • database login
    ... i've setup my report to use project data>current connections as my ...
    (microsoft.public.vb.crystal)
Loading