My input follows:
> > * some people say, it's best practice to put 1 connection
> > into 1 user's http
> > session and use it for all requests of that user
> This only works if you don't have a lot of users concurrently,
> say a small intranet application, that doesn't care scalibility.
> The reason is database connection can't be serialized for sure
> and connection in http session can't be replicated across your
> web server farm. Hence each user in a session requires to use
> the same machine in your web farm. This could be terrible if you
> intend to serve thousands of users concurrently. Secondly
> database licensing is an issue. Some database vendors charge you
> by concurrent opened connection. If your site has a thousand
> users concurrently, you will need to purchase a thousand licenses
> (That is the thing scares me most).
Agreed with replier..you would NEVER want to do this. If you are not careful
you can lose the connections which will ultimately crash the database and/or
the web server. VERY bad design.
>
> > * other people say it's best practice to use a connection for
> > every request.
> > (and if more than one methods are involved, the connection
> > should be given
> > as a method parameter)
> >
> I would rather use this per-requset approach. Only open a
> connection whenever a request comes in and needs database access.
> I can't think of a reason why passing around connection as
> parameter is a bad idea as long as it is in the same request.
This isn't bad. There are better ways.
>
> > * again other people argue that they don't care at all about reusing
> > connection, they just open and close them when they need/want to
> I would not like this idea since opening a database connection is
> expensive unless there is a connection pooling mechanism in place.
This is VERY slow, and in some tests its 100's of times slower than
implementing a connection pool. For the original sender (and Conrad if you
are not aware of this), there are two methods I would choose over the others
above. The first is connection pooling. Servlets in the same web app
maintain a Servlet Context that is a good place to store "global" objects.
By making a connection pool class and putting it in the servlet context when
the web app is first loaded, the connection pool is available to all
servlets (and JSP pages). Beyond that, a connection pool allows you to open
one or more connections and keep them alive while the web app is running.
Because of this, instead of going over the network and opening a connection
on each request, then closing that connection, you are given an immediate
opened connection from the pool on every request. This class is relatively
easy to implement as well.
Option 2 is better when you are dealing with application servers, especialy
with Orion. Orion has a very easy built-in connection pooling capability.
Generally the EJB layer would use this to get and set data in the database,
but your JSP/Servlets can do the same as well. Each Orion app server
(assuming you are using a cluster for fail-over, load-balancing and
scalability) can have its own connection pool settings. You provide the
database driver, number of connections, and login/password. The rest is done
through JDBC 2.0 DataSources. Now..I am not a pro at this yet..so I am
taking a stab at how this actually works. I still use my old connection pool
class. None the less, I am pretty sure its this easy. My only question to
anyone that knows is..can Orion (and other app servers for that matter) have
multiple connection pools per app server? On our site we maintain 3
different databases and each request needs a connection from one of the
three..depending on where the user is on the site. I would assume this is
possible..otherwise alot of sites wouldn't use app server specific database
connection pooling.
Anyways..for what its worth..I hope that helps.
