• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Connection pooling using Tomcat

 
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 147
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the reply.
I do close all the connections.

Here is one of my class files.



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
Ranch Hand
Posts: 102
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic