Re: [OT] JDBC confusion
I agree with Nix, use a connection pool. One connection per user may be ok when you've got, say, 5 concurrent users but when you've got 1000, 1, Not scalable. http://tomcatfaq.sourceforge.net/database.html http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html Jon john-paul delaney wrote: Hello List... newbie question coming up: If I set up a jdbc connection object in one servlet, do I have to close it each time or can the same connection be re-utilized by other servlets each using a different sql statement (perhaps storing the connection as a session attribute?). Is there a significant resource saving by not creating a JDBC connection object for each database access? I'm thinking of storing a connection for each user in the session, as opposed to pooling - primarily because I'm not sure how a database pool works nor how to set it up. Should I do a re-think on this? I'm using one postgresql 7.3.3 thanks /j-p. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [OT] JDBC confusion
> Hello List... newbie question coming up: Phaser charged... Shields at 80%... Photon torpedos loaded... > If I set up a jdbc connection object in one servlet, do I have to close it each time > or can the same connection be re-utilized by other servlets each using a different > sql statement (perhaps storing the connection as a session attribute?). This might give you a speed boost, since openning a connection can be time consuming. But that is the main idea behind JDBC connection pooling, so why re-invent the wheel? Poorly, I might add. > Is there a significant resource saving by not creating a JDBC connection object for > each database access? That depends on a particular JDBC driver, but usually no. You'll most likely save time. > I'm thinking of storing a connection for each user in the session, as opposed to > pooling > - primarily because I'm not sure how a database pool works nor how to set it up. A JDBC connection pool does what you want and more. It opens several connections and keeps them ready and waiting in the connection pool. The minimum and maximum number of connections are usually configurable (at least with DBCP in Tomcat). Physical maintainance of connections (checking if it is OK) is also implemented in DBCP. So, why don't you save yourself a lot of trouble and use JDBC connection pool over JNDI, as described in Tomcat docs? I had it working with Tomcat 2.1.24 and PostgreSQL 7.3.2. It also couples nicely with JSTL's "sql" tags. > Should I do a re-think on this? Most definitely. > I'm using one postgresql 7.3.3 Switching to 7.3.4 is recomended - there is a critical startup bug introduced in 7.3.3. Nix.
Re: [OT] JDBC confusion
The "preferred" way of doing this is to use connection pooling. The primary reasons are scalability, speed and simplicity. If you have 5,000 people using your app at the same time, by your method you will have to have 5,000 connections to the database (one per user) stored in the various sessions. However, those 5,000 connections aren't being used at the same time: each one probably has only an infrequent burst of activity on it. This is where connection pooling comes in. The connection pool has n connections (say, 10 - 20) that are kept active. When an application needs a connection to the database, it checks one out from the pool (it doesn't need to create and open one because it is already created and open -- a win in terms of speed), uses it and closes it just any other database connection. However, when it closes the connection, the connection isn't really closed; it's just returned to the pool. So, the application developer is hidden from the complexity of actually creating the connections to the database, making sure they are in good working order, etc. To get started with connection pooling in Tomcat, see http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html And check out the mailing list archives: the question of setting up connection pooling comes up very frequently. HTH Andrew P.S. Depending on the size/complexity of your project, you might be better off using an O/R mapping tool like OJB (http://db.apache.org/ojb/) or Hibernate (http://hibernate.bluemars.net/) instead of using JDBC directly. john-paul delaney wrote: Hello List... newbie question coming up: If I set up a jdbc connection object in one servlet, do I have to close it each time or can the same connection be re-utilized by other servlets each using a different sql statement (perhaps storing the connection as a session attribute?). Is there a significant resource saving by not creating a JDBC connection object for each database access? I'm thinking of storing a connection for each user in the session, as opposed to pooling - primarily because I'm not sure how a database pool works nor how to set it up. Should I do a re-think on this? I'm using one postgresql 7.3.3 thanks /j-p. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
