JDBC in JSP/Servlet question

[Chad Patrick]

Im a real newbie in JSP/Servlet programming and I had a question about how it interacts with JDBC database connections. (wasnt sure if i should post here or jdbc forum but i decided here cause i wanted a chance at the book
Anyways,
With JDBC in applets, each applet will form a connection to the database.
I read that in servlets, you put your JDBC connection code into the init() method so its loaded as soon as the servlet is loaded and im guessing it forms a connection to the database that stays active the entire time the servlet is alive? (let me know if im wrong so far) So if 2 people request the same servlet at the same time, are both of them using the same connection to the database, or does it open a new connection to the database each time the servlet is called, or perhaps, does it put requests in a queue?
Any clarification or pointers would be appreciated.

[Perry McKenzie]

Hi Chad,
I would agree with what you have said about JDBC connections with servlets. It should be done in the init() method, and yes, it stays active the entire time the servlet is alive, and if two people call the same servlet, they share the connection.
The way we have dealt with the connection sharing is by using a connection pool.

[Lasse Koskela]

If the servlet instances are sharing the connection, aren't you risking concurrency issues with simultaneous JDBC calls? I would locate a connection pool in the init() method and request an available connection from the pool when needed. Or am I just paranoid (it's been some years since I did J2SE/JDBC stuff)?

[David O'Meara]

As long as the Connection is correct in the driver this should OK, but I'm more concerned about the load on the database doing things this way.
Database Connectoons are Database resources, not Java resources, and can be quite heavy on the database. If you have one Connection per servlet, I see two potential problems. Firstly, if a servlet gets hit rarely, it ties up a Connection for no real point. Also, if you have many servlets, you get many connections. ie load on the database is proportional to the number of servlets.
Connection Pooling every day, thanks. Otherwise you're just playing

[Darryl Failla]

If you use a ConnectionPool, can't you limit the maximum number of connections that are created?

[Perry McKenzie]

I believe you can limit the number of connections in the connection pool.
I do know that we use the MVC (Model-View-Controller) pattern, and our controller servlet is where the init() method is. As a result, only one servlet every creates a connection to the database (per application instance). We then locate the connection pool in the init() method and request an available connection from the pool when needed, like Lasse said.

[Pradeep bhatt]

If you are using different transaction isolation you will run into problems.

If the servlet instances are sharing the connection, aren't you risking concurrency issues with simultaneous JDBC calls?

Servlets are multi threaded.Wouldn't there be concurrency problem if a single database connection is used within an servlet instance?
I feel there will be no concurrency problem.