We are working on a project, using weblogic 6.0, 10 EJB (stateless session), oracle database. I would like to know any predefined patters for (database connections) that, by which, we can provide more connection pooling, and the pros+cons about database connections.
what u can do is wrap the fuctionality of obtaining a database conection from the pool in a common fuctionality wrapper class. it involves obtaining the datasource object from JNDI and then calling getConnection on the datasource object. so that u dont have re-code the functionality in every bean. to do this u can very well apply lets say a factory pattern or a class with a static method getConnection wud work too.
database connection are basically socket connection which servers open to listen to client requests. there is an overhead involved in opening this connection to the database, so if u already have a connection opened up u can just obtain it from the pool. also most of databases have a limit on the number of database connection u can open depending on the license u have purchased. so u fix up these max value at the app server level.
cons , obvioulsy if there r number of requests then ur client will have to wait for the app server to free up a connection for u to use.
in all connection pool is gud idea, u just gotta know the reqirements of ur application
hope this helps
I like your process. Just to clarify a little :
>>>lets say a factory pattern or a class with a static method getConnection wud work too.
If I use a static method to get and close connection, as by Facade pattern, wouldn't it be a performance bottle-neck?
The server will manage the connection pools for you (It's part of the latest JDBC spec.)
You sometimes have licensing issues though.
You say: I have a connection pool and I'm only using 10 concurrent connections. Here is my cheque for a 10 user license.
RDBMS Vendor says: Give us a break, this is an internet site with thousands of registered users, all using our database product, pay us!
This is becoming less frequent these days, where the licensing model has changed, but a year or so back it was a big issue with a lot of financial institutions who had big "block deals" with the RDBMS shops.
On a more technical note, you need to know how much concurrent usage your site will have to determine how big the pool should be. Only you can know that, based on your usage projections. Of course, right now, you probably don't know how many different database calls each "request" will result in.... ;-)