Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB connection in a servlet question

 
Simon Good
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
There's a question in one of the JWeb+ exams that goes
You are writing a servlet that needs to fire SQL queries to retrieve the data from a database and generate html pages for HTTP GET requests. Which is the most appropriate place to create database connection?

They reckon the answer is to create a db connection from within the init() the comment being
Creating database connections is time consuming. So, generally, applications maintain a pool of connections and take a connection from the pool whenever needed. In this case, a connection can be created in the init() method and can be reused for each request.
If you create a connection in service(), doGet() or right before firing the query, a new connection will be created for each request which is waste of time.


Does anyone want to argue with this? I reckon the best place to create the connection is right before you execute the SQL ie get a connection from the connection pool, create your statement, resultset etc etc then put the connection back in the pool.
Anyone agree. Holding onto database connections for the lifetime of a servlet doesn't seem to be a great idea.
 
Mukhtar Ahmed
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Simon Good:

Anyone agree. Holding onto database connections for the lifetime of a servlet doesn't seem to be a great idea.

how is it different from taking a connection from pool and giving it back? the connection will still be open till the lifetime of the servlet.
You know the servlet 2.4 specification has given the same example where they say that you should create a connection in ServletContextListener.
 
Simon Good
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I set up my datasource when the context is created, then when ever I need a connection I get one from the datasource using getConnection(), use the connection, then close it.
If I create that connection from the init method of the servlet I'm now hogging a database connection for the lifetime of the servlet.
Maybe I've missed the point.
 
Murat Balkan
Ranch Hand
Posts: 127
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What if the db server drops the connection???
Will there be a chance that can java triggered?
 
Paul Anilprem
Enthuware Software Support
Ranch Hand
Posts: 3819
10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Simon,
Yes, getting a connection from the pool and returning it back is the best option. In this case also all the connections in the pool are always open. When you return a connection to the pool, the connection is not really closed*. That's the whole point of having a connection pool. So that you don't have to create and close the connections every time you use it. Another benefit is effective utilization of database connections. If you have a license for say only 5 simultaneous connections, you can still satisfy more than 5 clients because not every client is going to use a connection continuously.
Creating one connection is just a special case of a connection pool in which there is only one connection. You should never open and close a connection for every query. Init is one such place where you can create a connection and keep it. You can close it in the destroy method. You can also create a connection in ServletContextListner and add to ServletContext as an attribute.
*When you retrieve a connection for a datasource, you do call connection.close(). But this close() method is implemented by the application server. It does not physically close the connection but only returns it back to the connection pool.
HTH,
Paul.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic