Using JDBC in JSP Sessions

[Clive Backham]

I'm in the early stages of experimenting with JSP talking to
databases. (I'm not sure if this is the correct newsgroup for
questions on this subject. If not, could someone suggest a better
group to try?)

All the examples I've come across so far show a database connection
being made, used, and closed in a single page. In the course of my
experiments, I've determined that the database connection can be saved
as a session attribute and remains available to other pages in the
application without the need to re-establish the database connection
on each page. This clearly has some performance advantages.

The question is: is this regarded as acceptable? Obviously I can close
the connection if the user of the web application explicitly issues a
logout of some sort. But what if they just leave the website? Is it OK
to rely on the garbage collector to close the database connection when
the session itself gets timed out?

 

[Lew]

I'm in the early stages of experimenting with JSP talking to
databases. (I'm not sure if this is the correct newsgroup for
questions on this subject. If not, could someone suggest a better
group to try?)

All the examples I've come across so far show a database connection
being made, used, and closed in a single page. In the course of my
experiments, I've determined that the database connection can be saved
as a session attribute and remains available to other pages in the
application without the need to re-establish the database connection
on each page. This clearly has some performance advantages.

You mean disadvantages. How many connections does your database support? How
many users would you like to support? How is your thread safety?

The question is: is this regarded as acceptable?

"regarded", not relevant. "acceptable" by what criteria? It's a complicated,
unscalable and bug-prone approach, for what that's worth.

Obviously I can close the connection if the user of the web application explicitly issues a
logout of some sort. But what if they just leave the website? Is it OK
to rely on the garbage collector to close the database connection when
the session itself gets timed out?

No, because the garbage collector (GC) doesn't close connections, and you
can't even be sure it'll release the connection object.

Don't put connections in the session. Use a pooling driver.

 

[Clive Backham]

Thanks for your forthright response.

Don't put connections in the session. Use a pooling driver.

Ah, right. I hadn't got as far as connection pooling yet. Sorry for
asking a dumb question.