Connection pooling using Tomcat

[Archana Annamaneni]

Hi all,

I use DBCP Connection pooling with the Tomcat server.Number of database connections are keep on increasing , i am just wondering the way i am using the pool is coorect.

How do exactly pooling will work?

Does it creates all the connections at once and keep it ,please shud somelight on that .

Here is the java code where i access the connection from



public class SingletonDatabaseConnection {

private static SingletonDatabaseConnection singleinstance = null;
DataSource ds;
Context ctx;

/**
*Constructor for the SingletonDatabaseConnection object
*/
private SingletonDatabaseConnection() {
System.out.println("Singleton object created");
}


/**
* Description of the Method
*
* @return Description of the Return Value
*/
public static SingletonDatabaseConnection instance() {
if (null == singleinstance) {
singleinstance = new SingletonDatabaseConnection();
}
return singleinstance;
}

/**
* Gets the dataSource attribute of the SingletonDatabaseConnection object
*
* @return The dataSource value
* @exception Exception Description of the Exception
*/
public DataSource getDataSource() throws Exception {
try {

ctx = new InitialContext();
if (ctx == null) {
throw new Exception("Boom - No Context");
}


ds = (DataSource) ctx.lookup("java:comp/env/jdbc/NBDCORE_DATABASE");

} catch (Exception e) {
throw new SQLException();
}
return ds;
}




And here is the configuration i am using in the server.xml



<context>
<Context path="/xmlgateway" docBase="xmlgateway"
debug="0" reloadable="true" crossContext="true">
<Resource name="jdbc/NBDCORE_DATABASE"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/NBDCORE_DATABASE">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>

<!-- Maximum number of dB connections in pool. Make sure you
configure your mssql max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>50</value>
</parameter>

<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>

<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>

<!-- MSSQL dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value>bl1b1t</value>
</parameter>

<!-- Class name for mssql JDBC driver -->
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>

<parameter>
<name>url</name>
<value>jdbc:microsoft:sqlserver://192.168.1.9:1433;DatabaseName=NBD_CORE_LIVE</value>
</parameter>
</ResourceParams>
</context>




And this is how access a connection from java file


SingletonDatabaseConnection sdc = SingletonDatabaseConnection.instance();
DataSource ds = sdc.getDataSource();
try{
if (ds != null) {
conn = ds.getConnection();
}
}
catch(Exception e)
{

}



Thanks

[Kareem Qureshi]

Hi,

It looks like you are not closing the connection once they are created.Or I didnt see the code where you are closing the connections.
I would write a finally block after all the exception handling and then close connections there so that they are returned to the pool to be used again.

Hope this helps.

Thanks
Kareem

[Archana Annamaneni]

Thanks for the reply.
I do close all the connections.

Here is one of my class files.

 public class CheckConfig {
/**
 *  Checks users entered the valid configuration for the product
 *
 * @param  affiliate      AffiliateLoginForm object
 * @return                AffiliateLoginForm
 * @exception  Exception  Description of the Exception
 */
 static Logger dlog = Logger.getLogger("com.nbd.xmlgateway1");
 
public AffiliateLoginForm checkConfig(AffiliateLoginForm affiliate) throws Exception {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
boolean configflag = false;
String jurisdictionschosen = null;
 
SingletonDatabaseConnection sdc = SingletonDatabaseConnection.instance();
DataSource ds = sdc.getDataSource();

try
{
if (ds != null) {
conn = ds.getConnection();
}
//all the querys will be here 
if(rs!= null && rs.next()){
jurisdictionschosen =  rs.getString("JurisdictionsChosen");
}
 
rs.close();
rs = null;
psmt.close();
psmt = null;
conn.close();
// Return to connection pool
conn = null;
// Make sure don't close it twice
}else {
psmt = conn.prepareStatement(copsquery);
psmt.setInt(1,affiliateid);
psmt.setInt(2,productid);
rs = psmt.executeQuery();
if(rs!= null && rs.next()){
jurisdictionschosen =  rs.getString("JurisdictionsChosen");
}
configflag = true;
rs.close();
rs = null;
psmt.close();
psmt = null;
conn.close();
// Return to connection pool
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
 
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
throw e;
}
rs = null;
}
if (psmt != null) {
try {
psmt.close();
} catch (SQLException e) {
throw e;
}
psmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw e;
}
conn = null;
}
}

return affiliate;
}
}
 

I am wondering weather I am doing anything wrong in creating pool and accessing it , if you see the SingletondatabaseConnection class i posted earlier.

I appreciate any help,this is reallu urgent.

Thanks

[Kareem Qureshi]

Hi,
I think this might help you.
Thanks
Kareem

There is one problem with connection pooling. A web application has to explicetely close ResultSet's, Statement's, and Connection's. Failure of a web application to close these resources can result in them never being available again for reuse, a db connection pool "leak". This can eventually result in your web application db connections failing if there are no more available connections.

There is a solution to this problem. The Jakarta-Commons DBCP can be configured to track and recover these abandoned dB connections. Not only can it recover them, but also generate a stack trace for the code which opened these resources and never closed them.

To configure a DBCP DataSource so that abandoned dB connections are removed and recycled add the following paramater to the ResourceParams configuration for your DBCP DataSource Resource:


<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>




When available db connections run low DBCP will recover and recyle any abandoned dB connections it finds. The default is false.

Use the removeAbandonedTimeout parameter to set the number of seconds a dB connection has been idle before it is considered abandoned.


<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>




The default timeout for removing abandoned connections is 300 seconds.

The logAbandoned parameter can be set to true if you want DBCP to log a stack trace of the code which abandoned the dB connection resources.


<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>




The default is false.