aspose file tools*
The moose likes JDBC and the fly likes Connection Pooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Pooling " Watch "Connection Pooling " New topic
Author

Connection Pooling

Deepika Wadhwa
Ranch Hand

Joined: Aug 05, 2000
Posts: 57
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
Ranch Hand

Joined: Jan 23, 2001
Posts: 46
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


Sun Certified Programmer for Java2 Platform
Deepika Wadhwa
Ranch Hand

Joined: Aug 05, 2000
Posts: 57
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
Ranch Hand

Joined: Mar 09, 2000
Posts: 320
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.

SOURCE CODE should be SURROUNDED by "code" tags.
Arunagiri, Shanmugham
Ranch Hand

Joined: Jan 23, 2001
Posts: 46
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
Ranch Hand

Joined: Jan 23, 2001
Posts: 46
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
Ranch Hand

Joined: Mar 03, 2001
Posts: 350
thanx Arunagiri for the link it was help full
vivek


Vivek Viswanathan SCJP 1.2, SCJP 1.6,SCJD,SCEA,SCWCD,IBM-484,IBM-486,IBM-141,Ms.NET C# 70-316,SCMAD, LPIC-I
Premkumar N
Greenhorn

Joined: Apr 21, 2001
Posts: 22
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 ...

  • <a href="mailto:pondyprem@yahoo.com" rel="nofollow">pondyprem@yahoo.com</a> <br />Sr.Programmer Analyst ( Bioinformatics)<br />Monsanto Entrprises
    manjumv
    Greenhorn

    Joined: Jun 21, 2001
    Posts: 1
    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
    Greenhorn

    Joined: Jul 12, 2001
    Posts: 3
    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.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Connection Pooling