 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / October 2003Servlets with one DB connection
On an usually WEB applications, is easy to find a lot of servlet that execute SQL statement on a DB. It is very ugly to open a DB connection everytime that user perform a submit (and relative doGet), because from a submit and another my connection object is destroyed everytime. So I ask: how can I take up the "connection pool" object (on a tipic WAS)? Where do I have to save the "connection pool"? Is to save it on a HttpSession correct? I don't think is a beautiful solution to create a connection everytime from a submit and another: do you think? Thank you very much. Ferro. > On an usually WEB applications, is easy to find a lot of servlet > that execute SQL statement on a DB. [quoted text clipped - 13 lines] > Thank you very much. > Ferro. What is your webapp server? Is it Tomcat? Check out: http://www.mysql.com/articles/connection_pooling_with_connectorj.html http://jakarta.apache.org/tomcat/tomcat-4.0-doc/jndi-resources-howto.html > What is your webapp server? Is it Tomcat? IBM WebSphere. Thanks. Ferro. WebSphere has support for connection pooling. Basically, the idea is that WebSphere manages a set of connection and you use one when you need one and close it as soon as you have finished (you use a close statelebt but WebSphere keeps it opened behind the scene). You'll see it's quite easy and straightforward for the developer (one line or two to adapt to move from regular JDBC connection to a DataSource / connection pool). As regards the configuration, you have to define the properties of the pool (what db, how many connections, etc) and map a JNDI name to it. I suggest you download the WebSphere/WSAD programming RedBooks (www.redbooks.ibm.com). > (you use > a close statelebt but WebSphere keeps it opened behind the scene). Finally :-))) So I can open and close connection how many time I want, that WebSphere keeps the connections opened. When does WebSphere really open a new connection? When another user (another httpsession) asks it? When another process asks it? When? How can I understand if my connection request really doesn't use resource, or uses resource (new connection)? Is there a rule for understanding this? Thankyou very much. Ferro. > When does WebSphere really open a new connection? When WebSphere starts it will open as many connection as configured (from 0 to n). Settings are like min & max connections to keep in the pool, time-outs (orphan, idle, etc). > How can I understand if my connection request really doesn't > use resource, or uses resource (new connection)? Don't know what you mean here. > Is there a rule for understanding this? There are many articles on the web and it is explained with details in the RedBooks. > > How can I understand if my connection request really doesn't > > use resource, or uses resource (new connection)? > Don't know what you mean here. If I run getConnection from my servlet, WAS can open a new connection or can use a recycled connection (getting it from the pool). Two solution are different. In the first case my servlet can do a getConnection everytime I want, without loss of performance (because WAS really doesn't open new connection), but in the second case I have loss of performance, becuse WAS really open new connection. I need to understand which cases WAS really open a new connection, and which cases WAS use a connection from pool. I'm sorry for my bad english :-( > > Is there a rule for understanding this? > There are many articles on the web and it is explained with details in the > RedBooks. I read that redbooks, but meny times they speak about concepts of pool, but I don't find where they speak about the my questions. Thank you very much. Ferro. > If I run getConnection from my servlet, WAS can open a new > connection or can use a recycled connection (getting it from the > pool). Evolution of the pool is dictated by the following settings: min pool size, max pool size, connection timeout, idle timeout, orphan timeout. With WebSphere default settings, for example, when you start your application and require the first connection, you won't ll have a performance hit because one connection is opened (min pool size). When you have finished with it (you call the close method), the connection returns to the pool and remains opened for a while (idle timeout). When a second connection is required (two *concurrent* access to the database must be done -- this is different from two *subsequent* accesses), a new connection will be opened and the pool will keep them opened for a while (idle timeout). When more and more connections are required, the pool will get populated with new connections until the max pool size is reached. If more connections are required than the number of connections available, the requests will be queued, waiting for a connection to become available. > I read that redbooks, but meny times they speak about concepts of > pool, but I don't find where they speak about the my questions. What about sg246176 (WebSphere Handbook), Ch. 16.1.2 "Configuring JDBC providers and data sources" ? It's merely what I explained here above. You'll have to read it if you want to know what the orphan timeout and the statement cache size are :-). > When a second connection is required (two *concurrent* access to the > database must be done -- this is different from two *subsequent* accesses), > a new connection will be opened and the pool will keep them opened for a > while (idle timeout). This is the big thing to understand: my servlet ask a connection to the pool, after a JSP submit. During the execution the servlet uses the opened connection returned from pool (good optimization). But after using that connection, my servlet is killed. 10 seconds after, a user ask other data from the same JSP page. So user press the submit button, JSP open another time the servlet, that ask another time a connection to the pool. What kind of connection is returned from pool, after this second request? An opened connection (with optimization of performance), or a new connection (without optimization)???? This is my problem! This is what redbook doesn't say! How is the second request considered from pool? It can come from another user, or from the same user! In the first case it is concurrent, and in the second it is subsequent! What does the pool do? Thanks very much for your patience :-) Ferro. > What kind of connection is returned from pool, after this second request? The answer is "an opened connection", because I assume the user submits the second page within the 30 minutes and you use the default settings (min pool size is 1). So if he is alone on your site, he will never use a closed connection. With the default settings, exactly one connection is opened when WebSphere starts and maximum 10 connections will be opened. When a connection has been used, it returns to the pool and remains opened for 1800 seconds (idle timeout). So if the application issues exactly one request at a time at least every 30 minutes, exactly one connection will be created ever, and it will remain opened all the time. Now let's say person B comes to the web site while person A continues to work. B will have to pay the price for a second connection *when both users hit the submit button at the same time* because the pool has to grow from one to two opened connection. Afterwards, two connections are opened and two concurrent requests can be served. Now imagine person B goes away for lunch, while A stays working at his desk. When B comes back, the pool only has one connection left (due to the idle timeout). Then B will have to pay the price to open a second connection *when both users hit the submit button at the same time*. Then the pool has two connections. But usually, users won't hit the submit button at the same time and you can imagine that the first request will be served so rapidly that the second can be issued with the same connection. And that's exactly the idea behind pooling: it is not likely that all users will need a connection at the same time. So, basically, you don't need one connection per user and you'll want to tune the pool size and the timeouts to reduce overhead of opening connections while limiting resource usage. > It can come from another user, or from the same user! > In the first case it is concurrent, and in the second it is subsequent! No, to me "two subsequent requests" means "two requests executed one after the other", while "two concurrent request" means "two requests at the same time". Whether it's the same user or not is not relevant. What is relevant is the moment when the requests are issued. The pool manager doesn't bother knowing who uses the connection. It just has connections and gives one when requested, provided one is available. > Thanks very much for your patience :-) Be my guest. By the way, you may want to read the "WebSphere Connection Pooling paper" (topic 2, d at http://www7b.software.ibm.com/wsdd/library/techarticles/0305_issw/recommendedrea ding.html#sec2). It discusses the lifecycle of the pool, the settings, etc. Thank you very much: what you wrote is better then many books. Thank you very much: now it is all light. Ciao, Ferro. Have a look at JNDI and looking up a connection pool from the server config. See: http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-resources-howto.html for details with Tomcat. The code for the Web application should apply to any servlet engine that follows the J2EE guidelines, although the configuration might be different. > On an usually WEB applications, is easy to find a lot of servlet > that execute SQL statement on a DB. [quoted text clipped - 13 lines] > Thank you very much. > Ferro. > Have a look at JNDI and looking up a connection pool from the server config. > [quoted text clipped - 3 lines] > any servlet engine that follows the J2EE guidelines, although the > configuration might be different. All right, my code works as written in the HOWTO document. But what I don't understand some thing, don't written in the HOWTO. My servlet open a connection to DB so: Context initCtx = new InitialContext(); Context envCtx = (Context) initCtx.lookup("java:comp/env"); DataSource ds = (DataSource) envCtx.lookup("jdbc/EmployeeDB"); Connection conn = ds.getConnection(); The problem is that connection is opened and closed after every SUBMIT. Is this right? I don't understand if WAS uses everytime the SAME connection (using a pool) or if WAS give me everytime a new connection. What I understant about connection pool is this: when my servlet ask a new connection (from the same session), the WAS don't create everytime a new connection, but uses a pool. Is this right? Thankyou very much. Ferro. PS: my WAS is WebSphere, but I think that pool concepts are indipendent from WAS. Is this right? Part of the server configuration is how many connections you want to allow, as well as timeout periods if a connections isn't freed up after it's used. Basically, you just borrow one of the pool - let's say there are 8 - and when you're done, you give it back. Make sure you do release the connection though, or you might find you run out and crash your JVM. The server can maintain the pool any way it likes behind the scenes, but Tomcat uses the Commons Pool & DBCP libraries, which you can read all about at http://jakarta.apache.org/commons and explains quite a lot. For WebSphere, I guess you need their documentation. > > Have a look at JNDI and looking up a connection pool from the server > config. > > > > See: http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-resources-howto.html > > for details with Tomcat. The code for the Web application should apply to > > any servlet engine that follows the J2EE guidelines, although the [quoted text clipped - 25 lines] > my WAS is WebSphere, but I think that pool concepts are > indipendent from WAS. Is this right? As you can see from a later post, it's not that straightforward. Make sure you read: http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html as well if you're doing it on Tomcat. > Have a look at JNDI and looking up a connection pool from the server config. > [quoted text clipped - 26 lines] > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003 Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.