aspose file tools*
The moose likes JSP and the fly likes JDBC in JSP/Servlet  question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "JDBC in JSP/Servlet  question" Watch "JDBC in JSP/Servlet  question" New topic
Author

JDBC in JSP/Servlet question

Chad Patrick
Greenhorn

Joined: Feb 01, 2003
Posts: 11

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
Greenhorn

Joined: Jul 16, 2003
Posts: 23
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
author
Sheriff

Joined: Jan 23, 2002
Posts: 11962
    
    5
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)?


Author of Test Driven (2007) and Effective Unit Testing (2013) [Blog] [HowToAskQuestionsOnJavaRanch]
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

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
Ranch Hand

Joined: Oct 16, 2001
Posts: 129
If you use a ConnectionPool, can't you limit the maximum number of connections that are created?


Darryl Failla
Sun Certified Java 2 Programmer
Perry McKenzie
Greenhorn

Joined: Jul 16, 2003
Posts: 23
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
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

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.


Groovy
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC in JSP/Servlet question