Connection Pooling

[Deepika Wadhwa]

Hi all ,
I have a few Questions regarding connection pooling
1) is it database vendor specific or not ?
2) we can change setting for no of connection at a time thru web server or servlet container?
in my application I am using Apache web server with Tomcat.
while one query is being running on the database if some other servlet try to open a connection, it simply give a error stating that database is locked by (computer name).
How to handle this situation ?

please help
Thanx in advance
Deepika

[Arunagiri, Shanmugham]

Hi,
You can handle this eigther by implementing the SingleThreadedModel Interface to the servlet code or by handling with Multiple Thread on the Servlet code. Implementing the SingleThreadModel Interface will automatically create new instances of the Connection everytime, the request is made.
Arun
SCJP

[Deepika Wadhwa]

Hi Arun
thanx for ur response
but i don't think this is the solution , it will be an overhead implementing singleThreadModel just to create new connection ...
any other suggestion ?

thanx in advance
Deepika

[John Bateman]

Originally posted by Deepika Wadhwa:

1) is it database vendor specific or not ?
2) we can change setting for no of connection at a time thru web server or servlet container?
in my application I am using Apache web server with Tomcat.

Hi
In a nutshell, connection pooling is not (in ALL cases) dependant on the DB server you are using. To be able to interact with a connection pool for databases you should look into the JNDI api , and the "Datasource" method of connection in javax.sql.*.
JNDI is a java API that will allow you interact and manage diffrent kinds of resources (referred to as directories) such as printers, modems, file systems and even db connections. To do this you need some kind of 'service provider' to be the 'bind' between the LDAP/JNDI and your program. There are many diffrent service providers and some (most) are rolled into the EJB application servers you may see us talking about here. Example would be Weblogic, Websphere, Jrun, (and even Resin) although resin is a servlet engine and not an application server.
IN a database world a service provider will allow you to setup and maintain collections or pools of database connections. Tomcat (as far as I know) currently has no connection pooling ability, but, you can go try out my favorite Servlet Engine at http://www.caucho.com. The engine is called Resin and it allows you to do connection pooling quite easily. I also reccommend that you quickly look at the JNDI doc at Sun when you have a chance http://java.sun.com/jndi
As for your second question, yes you can control how many connection are created and how long they live.
I hope this helps.

[Arunagiri, Shanmugham]

Hi Deepika,
Check this link http://jakarta.apache.org/turbine/getting-started.html. This link talks about a software called Turbine. Which can be installed in apache web server. It also comes with Tomcat Servlet Engine. This whole topic also covers implementation of Connection Pooling too.
Hope this helps u a little.
Arun
SCJP

[Arunagiri, Shanmugham]

Hi,
http://www.webdevelopersjournal.com/columns/connection_pool.html
This site explains u about coding Connection pool class in Servlet in detail. Can go thru this.. if u are not comfortable with above.
Arun
SCJP

[Vivek Viswanathan]

thanx Arunagiri for the link it was help full
vivek

[Premkumar N]

You can implement your own connection pooling class
Some what like .. MyConnectionPool

have a vector that stores connection objects.

you may initialize 10 to 20 connections at the beginning

make accessor functions to access the connecitons
eg Connection con = MyConnectionPool.getConnectionOut();

Put the connections back after use -- very important
e.g. MyConnectionPool.putConnectionBack( conn );

when a connection is taken out make an entry in some hash table

U've to define min and max limits

u can write code that will check for this limits and do the appropriate actions.

The advantage of making your own connectionPool mechanism is that ... u code will suit any web server ... JRun ... Tomcat ...or whatever ...

[manjumv]

Hi friends,
I have implemented the connection pooling with the code what arun has specified. But i have implemented this in JSP . And very time i return the connetion to the Pool ( free connection is perfect based Max connection in Db.properties file - Checking the hash table size). Where as if i go the oracle & check the active procees it is always less than the maxconnection. My only worry is it is returing the connection to pool.
I am checking the active procee In V$process table in oracle.
For me always max connection & active proceess should be equal.
Please guide how to correct the bug.
Thanks in advance,
Manjunath

[James Chrystal]

Please note - if you're using db2 and the code specified at http://www.webdevelopersjournal.com/columns/connection_pool.html, you may have problems. DB2's implementation of Connection.isClosed() does not always return false if the connection is closed. You will need to trap the error when you prepare your SQL statement.