Servlets with one DB connection

[Ferro]

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.

 

[Ed Thompson]

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.

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

 

[Digby]

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.

 

[Sboffo]

What is your webapp server? Is it Tomcat?

IBM WebSphere.

Thanks.
Ferro.

 

[Ben_]

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).

 

[Sboffo]

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.

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?

 

[Digby]

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.

 

[Sboffo]

(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.

 

[Digby]

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.

 

[Ben_]

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.

 

[Ferro]

How can I understand if my connection request really doesn't

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 :-(

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.

 

[Ben]

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 .

 

[Ferro]

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.

 

[Ben_]

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/recommendedreading.html#sec2).
It discusses the lifecycle of the pool, the settings, etc.

 

[Ferro]

Thank you very much: what you wrote is better then many books.

Thank you very much: now it is all light.

Ciao, Ferro.