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 Oracle Connection Pooling 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 "Oracle Connection Pooling" Watch "Oracle Connection Pooling" New topic

Oracle Connection Pooling

Brian Sweeting

Joined: Feb 14, 2003
Posts: 1
I have a need for some simple Oracle connection pooling in a jsp. I found this example but it seems to defeat the purpose of reusing connections if new ones are created each time the page is refreshed.

[ February 14, 2003: Message edited by: Brian Sweeting ]
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
You are right -- this example does use the connection pooling infrastructure, but does not actually use pooling. I assume you found that example in the Oracle JDBC documentation?
The ConnectionPoolDataSource is a factory for PooledConnection objects, which are connection (or actually connection wrappers) that can be used by a connection pooling implementation. They do not implement the actual connection pooling, but provide extra infrastructure (an event model) that is necessary to build robust connection pools.
To turn this into a true connection pool, you need additional classes that build on ConnectionPoolDataSource and friends. Oracle ships a simple implementation as part of its JDBC driver and, for some reason I don't quite understand, refers to it as connection caching. So what you can do is instantiate an OracleConnectionCacheImpl, stick your ConnectionPoolDataSource in there, and bind the cache in the application context or JNDI so you can re-use it time and time again.
The right way to go about it, however, is to get the application server to bind a DataSource in the JNDI tree. This application server DataSource will generally use the JDBC drivers' ConnectionPoolDataSource support to implement connection pooling. You never get to worry about this -- you simply look up the DataSource in JNDI and use connections from it, treating the connections as if they were ordinary un-pooled Connections.You would never include code like this in your JSP, of course -- you'd use Data Access components called from the controller to implement this. If you really have to access the database from the JSP, I would very strongly suggest you look in the SQL tags that are part of the JSTL. They offer considerable functionality and can seamlessly work with DataSources available from JNDI.
To indicate that your web application needs access to a JDBC DataSource, you include a snippet like the following in your web.xml deployment descriptor:You will need to modify the container configuration as well to indicate where this EmployeeAppDB should come from, i.e. driver name, database URL, username, password, etc. This part of the configuration is server-specific.
- Peter
[ February 15, 2003: Message edited by: Peter den Haan ]
Kalpesh Soni
Ranch Hand

Joined: Jan 02, 2001
Posts: 312
I dont see why u guys are saying that connections are not pooled in this code
since you are using datasource, it is managing connections
and when u say connection.close() or pooledconnection.close() the db connection is not really closed, but it is returned to the datasource as an available one
am i right ?
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
No, you aren't right, and Brian wasn't using a DataSource -- he was using a ConnectionPoolDataSource which is, as stated above, a factory class for use by connection pool implementations.
- Peter
I agree. Here's the link:
subject: Oracle Connection Pooling
It's not a secret anymore!