Servlets, jconnect/JDBC2.0 and efficient database connection management
When using servlets I know it is possible to preserve connections to a database by using various methods. As servlets are threaded you can let multiple request threads share one connection or you can create a data connection pool management class to pool connections. However the following questions arise(to me at least): 1) Letting servlets share a single connection seems the fastest way to me, except that transactional queries and prepared statments must be synchronized - is this correct. 2) Pooling connections still means that you're sharing a set of connections, so threads have to wait for the connections to be put back in the pool to be serviced and as pools probably contain only around 10 connections on average, with 1000 threads/servlets trying to grab a connection I can see a performance problem. 3) Finally, the JDBC 2.0 spec says it supports connection pooling. Does this mean that when I connect to the database through JDBC with DriverManager.getConnection() it's pooling open connections anyway so I don't need to worry?
I'm confused.
Peter J. Boettcher posted this at 15:39 — 17th October 2000.
They have: 812 posts
Joined: Feb 2000
davidg,
It's best to let the system handle the connections. A big mistake a lot of ASP developers make is to store the connection object in an application or session variable, this might appear to be a logical thing to do since it saves you from having to code the connection in every page. The problem with this is, connections stored in those type of variables cannot be pooled, so with a busy site this would slow things down considerably. In any case pool threading offers considable performance gains, especially if you do many similar type of db calls.
Regards,
Peter J. Boettcher
PJ | Are we there yet?
pjboettcher.com
davidg posted this at 17:14 — 17th October 2000.
They have: 19 posts
Joined: Feb 2000
Thanks for your reply, I'll resort to pooling connections then. However I don't think the standard JBDC method getConnection() methods copes with pooling. I think a connection pool manager needs to manage your connections otherwise every time a servlet is run, there is a reconnection to the database - which can be costly. I do know that JDBC2.0 supports connection pooling, but it only has interfaces available so you have to write the basic code. Do you know of any implementations of these interfaces, esp. jconnect/Sybase's version of JDBC?
Peter J. Boettcher posted this at 19:15 — 17th October 2000.
They have: 812 posts
Joined: Feb 2000
davidg,
Unfortunately I don't have a lot of experience using Java Servlets so I can't really help you with that. From my experience using COM+ objects within ASP pages (which are similar to Servlets) is that if you mark them (object) as recyclable or poolable, MTS (Microsoft Transaction Server) can then pool those objects. Whether or not the JDBC supports that I'm not sure.
I'm sorry I can't help you more but my area of expertise is ASP,ASP+,VB,VC++,SQL 7/2000
Regards,
Peter J. Boettcher
PJ | Are we there yet?
pjboettcher.com
Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.