Connection pooling implementation

[Prasad Darsi]

Hi,

I am using Oracle 9i database and the apache http server supplied with it. I want to implement connection pooling in the JSP pages.

If I write a class that connects to the database using javax.sql.DataSource,
and call it in the JSP pages to execute queries, does it mean that I am using connection pooling mechanism ? Or is there any other way ?

Where can I get the sample code to implement connection pooling ?

Thanks in advance..

[Prasad Darsi]

Hi,
I have one more question.. I am Using the Oracle 9i thin driver and developing JSP pages using Oracle HTTP server.

In every JSP page, I am including another jsp page (DBConn.jsp) which registers the oracle driver and opens the Connection.

After processing the oracle queries, I am closing all resultsets, statements and finally the connection object.

So, in every page - registering the driver, opening the connection, query processing, closing all objects - is taking place.

The maximum no. of users to the application at any time is not greater than 50. So far the same mechanism is used while 15 users are accessing the system. Will there be any problem, if I continue using this approach if the users are increased in the future? or I have to use some kind of connection pooling mechanism ?

Which is the better approach ?

[Javaoops]

Hi,

Check out the URL, might be useful.

http://www.oracle.com/technology/products/ias/daily/jan04.html

[Jyothi Lature]

Hi,

I would strongly suggest you to use Connection pooling. Registering a driver, connecting to the database for every transacation is a very bad practice of coding. Creating new database connections is an expensive operation. The optimal technique is to make sure that database connections opened in one request are reused in subsequent requests.
Creating a statement object and ResultSet object and then closing them after every transaction is a must.

To know more about Connection pool and for example code, refer to this link:
Connection Pooling

Hope this is useful..

[Adeel Ansari]

its like wht server you are using if it 9iAppServer then Javaoops suggestion might work, otherwise look at this