File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Connection pooling implementation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Connection pooling implementation" Watch "Connection pooling implementation" New topic

Connection pooling implementation

Prasad Darsi

Joined: Sep 26, 2004
Posts: 16

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

Joined: Sep 26, 2004
Posts: 16
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 ?
Ranch Hand

Joined: Jun 21, 2001
Posts: 57

Check out the URL, might be useful.
Jyothi Lature
Ranch Hand

Joined: Oct 31, 2001
Posts: 60

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..

Jyothi<br />Sun Certified Java Programmer<br />Brainbench Certified for Javascript
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
its like wht server you are using if it 9iAppServer then Javaoops suggestion might work, otherwise look at this
I agree. Here's the link:
subject: Connection pooling implementation
jQuery in Action, 3rd edition