aspose file tools*
The moose likes JDBC and the fly likes Connection pooling using Tomcat Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection pooling using Tomcat" Watch "Connection pooling using Tomcat" New topic
Author

Connection pooling using Tomcat

Archana Annamaneni
Ranch Hand

Joined: Jan 29, 2003
Posts: 147
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
Ranch Hand

Joined: Mar 14, 2002
Posts: 102
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

Joined: Jan 29, 2003
Posts: 147
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

Joined: Mar 14, 2002
Posts: 102
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection pooling using Tomcat