You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Vijay Anjur <vi...@gmail.com> on 2006/04/28 22:45:15 UTC
DBCP Connection Pool Leak.
Hi,
I am having problem with DBCP/JNDI/TOMCAT servlet. I am using the following
code for connection pooling.
But I can see that physical connections are not getting closed by the
eviction thread of dbcp pool package.
Any ideas???
Thanks.
import java.io.*;
import java.net.*;
import java.sql.*;
import java.util.concurrent.*;
import javax.naming.*;
import javax.sql.*;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.DataSourceConnectionFactory;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.log4j.*;
import org.xml.sax.*;
import beehive.utils.*;
import java.text.ParseException;
public class SqlServer extends HTTPServlet
{
private static final Logger logger = Logger.getLogger(SqlServer.class);
private static InitialContext ic = null;
private static Context envCtx = null;
private static DataSource dsINTLDB;
private static ObjectPool poolINTLDB;
private static PoolingDataSource pdsINTLDB;
private static BasicDataSource bdsINTLDB;
public SqlServer()
{
try
{
ic = new InitialContext();
envCtx = (Context) ic.lookup("java:comp/env");
setupDataSourceConnectionPool();
}
catch (Exception e)
{
logger.error("error getting initial context for Datasource : "+e);
}
}
//==========================================================================
private static void setupDataSourceConnectionPool()
{
try
{
dsINTLDB = (DataSource) envCtx.lookup("ds/intldb");
dsTMS = (DataSource) envCtx.lookup("ds/tms");
dsPMDB = (DataSource) envCtx.lookup("ds/pmdb");
if (dsINTLDB != null)
{
bdsINTLDB = (BasicDataSource)dsINTLDB;
ConnectionFactory cf1 = new
DataSourceConnectionFactory(dsINTLDB);
poolINTLDB = new GenericObjectPool(null,
bdsINTLDB.getMaxActive(),
(byte)1,
bdsINTLDB.getMaxWait(),
bdsINTLDB.getMaxIdle(),
bdsINTLDB.getMinIdle(),
bdsINTLDB.getTestOnBorrow(),
bdsINTLDB.getTestOnReturn(),
bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
bdsINTLDB.getNumTestsPerEvictionRun(),
bdsINTLDB.getMinEvictableIdleTimeMillis(),
bdsINTLDB.getTestWhileIdle()
);
PoolableConnectionFactory pcf1 = new
PoolableConnectionFactory(cf1, poolINTLDB, null, null, false, true);
pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
pdsINTLDB = new PoolingDataSource(poolINTLDB);
poolINTLDB.addObject();
logger.info("Pooling Datasource for INTLDB is setup.");
}
}
catch (Exception ex)
{
logger.error("Error in setup datasource pools : "+ex);
}
}
//=============================================================================
public synchronized Connection getConnectionFromPool(String pds)
{
Connection c = null;
try
{
c = pdsINTLDB.getConnection();
}
catch (Exception ex)
{
logger.error("UNABLE TO GET A CONNECTION FROM POOL FOR :"+pds+".
Because : "+ex);
}
return c;
}
//==========================================================================
protected Message executeQuery(String connection, String sql, boolean
stats) throws ServiceException, SQLException, ParseException, SAXException,
IOException, BeehiveMessageException
{
Connection con = getConnectionFromPool(connection);
try
{
return executeQuery(con, sql, stats);
}
finally
{
if (con != null)
{
if(!con.isClosed())
con.close();
}
}
}
//==========================================================================
protected Message executeQuery(Connection con, String sql, boolean stats)
throws ServiceException, SQLException, ParseException, SAXException,
IOException, BeehiveMessageException
{
if (con == null) {
throw new ServiceException("Unable to Obtain Connectionfrom from
Data Source. Please exit the application and try again.");
}
ResultSet rs = null;
Statement sta = null;
Message response = null;
String eMessage = "";
try {
sta = con.createStatement();
rs = sta.executeQuery(sql);
response = (Message)rs;
}
}
finally {
if (rs != null)
{
rs.close();
}
if (sta != null)
{
sta.close();
}
}
return response;
}
//==========================================================================
My JNDI properties in server.xml Is below:
<Resource
name="jdbc/tms"
auth="Container"
type="javax.sql.DataSource"
password="myPassword"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
maxIdle="5"
maxWait="6000"
username="myUsername"
url="jdbc:jtds:sybase://mydb:11111"
removeAbandoned="true"
removeAbandonedTimeout="300"
logAbandoned="true"
maxActive="80"
initialSize="3"
minIdle="0"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle="true"
numTestsPerEvictionRun="10"
timeBetweenEvictionRunsMillis="100000"
minEvictableIdleTimeMillis="80000"
validationQuery="Select 1"/>
//==========================================================================
Re: DBCP Connection Pool Leak.
Posted by Sandy McArthur <sa...@gmail.com>.
Read this: http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html
Unless you're doing something really advanced, which it appears you
aren't, then you're over engeneering the code.
As that link will show, once you've setup the resourse in your
container all the code you need is:
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();
// do your work
conn.close(); // call when done to clean up
On 4/28/06, Vijay Anjur <vi...@gmail.com> wrote:
> Hi,
>
> I am having problem with DBCP/JNDI/TOMCAT servlet. I am using the following
> code for connection pooling.
>
> But I can see that physical connections are not getting closed by the
> eviction thread of dbcp pool package.
>
> Any ideas???
>
>
>
> Thanks.
>
>
>
>
>
>
>
> import java.io.*;
>
> import java.net.*;
>
> import java.sql.*;
>
> import java.util.concurrent.*;
>
> import javax.naming.*;
>
> import javax.sql.*;
>
>
>
>
>
> import org.apache.commons.pool.impl.GenericObjectPool;
>
> import org.apache.commons.dbcp.PoolableConnectionFactory;
>
> import org.apache.commons.dbcp.DataSourceConnectionFactory;
>
> import org.apache.commons.pool.ObjectPool;
>
> import org.apache.commons.dbcp.ConnectionFactory;
>
> import org.apache.commons.dbcp.BasicDataSource;
>
> import org.apache.commons.dbcp.PoolingDataSource;
>
>
>
> import org.apache.log4j.*;
>
> import org.xml.sax.*;
>
> import beehive.utils.*;
>
> import java.text.ParseException;
>
>
>
>
>
> public class SqlServer extends HTTPServlet
>
> {
>
>
>
>
>
> private static final Logger logger = Logger.getLogger(SqlServer.class);
>
>
>
> private static InitialContext ic = null;
>
> private static Context envCtx = null;
>
> private static DataSource dsINTLDB;
>
> private static ObjectPool poolINTLDB;
>
> private static PoolingDataSource pdsINTLDB;
>
> private static BasicDataSource bdsINTLDB;
>
>
>
>
>
> public SqlServer()
>
> {
>
>
>
> try
>
> {
>
> ic = new InitialContext();
>
> envCtx = (Context) ic.lookup("java:comp/env");
>
> setupDataSourceConnectionPool();
>
> }
>
> catch (Exception e)
>
> {
>
> logger.error("error getting initial context for Datasource : "+e);
>
> }
>
> }
>
>
>
>
> //==========================================================================
>
> private static void setupDataSourceConnectionPool()
>
> {
>
> try
>
> {
>
> dsINTLDB = (DataSource) envCtx.lookup("ds/intldb");
>
> dsTMS = (DataSource) envCtx.lookup("ds/tms");
>
> dsPMDB = (DataSource) envCtx.lookup("ds/pmdb");
>
> if (dsINTLDB != null)
>
> {
>
> bdsINTLDB = (BasicDataSource)dsINTLDB;
>
> ConnectionFactory cf1 = new
> DataSourceConnectionFactory(dsINTLDB);
>
> poolINTLDB = new GenericObjectPool(null,
>
> bdsINTLDB.getMaxActive(),
>
> (byte)1,
>
> bdsINTLDB.getMaxWait(),
>
> bdsINTLDB.getMaxIdle(),
>
> bdsINTLDB.getMinIdle(),
>
> bdsINTLDB.getTestOnBorrow(),
>
> bdsINTLDB.getTestOnReturn(),
>
>
> bdsINTLDB.getTimeBetweenEvictionRunsMillis(),
>
>
> bdsINTLDB.getNumTestsPerEvictionRun(),
>
>
> bdsINTLDB.getMinEvictableIdleTimeMillis(),
>
> bdsINTLDB.getTestWhileIdle()
> );
>
> PoolableConnectionFactory pcf1 = new
> PoolableConnectionFactory(cf1, poolINTLDB, null, null, false, true);
>
> pcf1.setValidationQuery(bdsINTLDB.getValidationQuery());
>
> pdsINTLDB = new PoolingDataSource(poolINTLDB);
>
> poolINTLDB.addObject();
>
> logger.info("Pooling Datasource for INTLDB is setup.");
>
> }
>
>
>
> }
>
> catch (Exception ex)
>
> {
>
> logger.error("Error in setup datasource pools : "+ex);
>
> }
>
> }
>
>
>
> //=============================================================================
>
> public synchronized Connection getConnectionFromPool(String pds)
>
> {
>
> Connection c = null;
>
> try
>
> {
>
> c = pdsINTLDB.getConnection();
>
> }
>
> catch (Exception ex)
>
> {
>
> logger.error("UNABLE TO GET A CONNECTION FROM POOL FOR :"+pds+".
> Because : "+ex);
>
> }
>
>
>
> return c;
>
> }
>
>
>
>
> //==========================================================================
>
>
>
> protected Message executeQuery(String connection, String sql, boolean
> stats) throws ServiceException, SQLException, ParseException, SAXException,
>
> IOException, BeehiveMessageException
>
> {
>
> Connection con = getConnectionFromPool(connection);
>
> try
>
> {
>
> return executeQuery(con, sql, stats);
>
> }
>
> finally
>
> {
>
> if (con != null)
>
> {
>
> if(!con.isClosed())
>
> con.close();
>
> }
>
> }
>
> }
>
> //==========================================================================
>
> protected Message executeQuery(Connection con, String sql, boolean stats)
> throws ServiceException, SQLException, ParseException, SAXException,
>
> IOException, BeehiveMessageException
>
> {
>
>
>
> if (con == null) {
>
> throw new ServiceException("Unable to Obtain Connectionfrom from
> Data Source. Please exit the application and try again.");
>
> }
>
>
>
> ResultSet rs = null;
>
> Statement sta = null;
>
> Message response = null;
>
> String eMessage = "";
>
>
>
> try {
>
> sta = con.createStatement();
>
> rs = sta.executeQuery(sql);
>
> response = (Message)rs;
>
> }
>
> }
>
> finally {
>
> if (rs != null)
>
> {
>
> rs.close();
>
> }
>
> if (sta != null)
>
> {
>
> sta.close();
>
> }
>
> }
>
> return response;
>
>
>
> }
>
> //==========================================================================
>
>
>
> My JNDI properties in server.xml Is below:
>
> <Resource
>
> name="jdbc/tms"
>
> auth="Container"
>
> type="javax.sql.DataSource"
>
> password="myPassword"
>
> factory="org.apache.commons.dbcp.BasicDataSourceFactory"
>
> driverClassName="net.sourceforge.jtds.jdbc.Driver"
>
> maxIdle="5"
>
> maxWait="6000"
>
> username="myUsername"
>
> url="jdbc:jtds:sybase://mydb:11111"
>
> removeAbandoned="true"
>
> removeAbandonedTimeout="300"
>
> logAbandoned="true"
>
> maxActive="80"
>
> initialSize="3"
>
> minIdle="0"
>
> testOnBorrow="true"
>
> testOnReturn="false"
>
> testWhileIdle="true"
>
> numTestsPerEvictionRun="10"
>
> timeBetweenEvictionRunsMillis="100000"
>
> minEvictableIdleTimeMillis="80000"
>
> validationQuery="Select 1"/>
>
>
>
> //==========================================================================
>
>
--
Sandy McArthur
"He who dares not offend cannot be honest."
- Thomas Paine
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org