commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Kelly <m.f.ke...@arts.ac.uk>
Subject DBCP - not closing connections?
Date Fri, 04 Nov 2005 12:44:07 GMT
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.
-- 


Mike Kelly
Multimedia Developer
IT Research and Development Unit
University of the Arts London
020 7514 6206


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message