DB connection from servlets

[Kiran Baratam]

Hi
I am working on a servlet which needs get the data from a DB and display as a report.
My question is if i put the driver loading,connection creation and statement creation inside init method of servlet i can not access the variables in my doGet method.
if i create class level variables i am worrying will there be any problem as more than one user can access it at the same time???
if i use SingleThreadModel then there could be performance problem as users need to be in que till the first request got processes.
Can you please suggest me some solution.
Regards
Kiran

[Jason Davies]

You're better off putting the creation of the JDBC Connection inside the doGet method. I would also advise the use of a connection pool if you're worried about performance.

[Kiran Baratam]

If i use Connectionpool can i make the connection when servlet is instantiated once and use the same connection???
Do i need to write a sepetrate class ???
kiran
[ September 18, 2003: Message edited by: Kiran Baratam ]

[Andres Gonzalez]

Originally posted by Jason Davies:
You're better off putting the creation of the JDBC Connection inside the doGet method. I would also advise the use of a connection pool if you're worried about performance.

I'd put it in the init method of the servlet, this way you'll know that the connection is created only once, because the servlet specification states that the init() method is invoke only once.
Remember that creating connection to database are very expensive, in terms of resources. In the init method you can instantiate only one object and put it in the servletcontext interface. You can then reuse the same object throughout your web application.
try also not to use singlethreadmodel
HTH
[ September 18, 2003: Message edited by: Andres Gonzalez ]

[Andres Gonzalez]

// in your servlet
public void init(ServletConfig config) throws ServletException 
{
        super.init(config);
// create your connection here
        try 
{
  myConnection1 = new DBConnection(".......") 
} 
catch (IOException e5)  
{ 
   // catch it
}
        
// then put it in servletcontext
        ServletContext sctx = getServletContext();
        sctx.setAttribute("myConnection",myConnection1);
}

[Gregg Bolinger]

I'd put it in the init method of the servlet
Very bad idea in my opinion because of mutliple requests on the same object. Can cause all sorts of problems. I would go with Jason on this one...
try also not to use singlethreadmodel
There is no try, there is only do...
If you are putting your connection in the init() method you better not use single thread model.
Connection Pooling is the only way to fly.
If i use Connectionpool can i make the connection when servlet is instantiated once and use the same connection???
If you use ConnectionPooling, all the connections are already made for you when the Web App starts. What happens is when you use ConnectionPooling, there is basically a pool of connections. When you request a connection, the Pool Manager hands you a connection. When you are done with that connection, simply call the close() method, which doesn't actually kill the connection, it just hands it back to the pool so it can be used by someone else.
I don't know what App Server you are using. Different Web Apps deal with connection pooling a little different, but here is a link to the Tomcat docs on Connection Pooling so maybe you can get a general idea.
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html
Have Fun.

[Andres Gonzalez]

Andres: I'd put it in the init method of the servlet
Gregg: Very bad idea in my opinion because of mutliple requests on the same object. Can cause all sorts of problems. I would go with Jason on this one...
Then I require clarification on the question:

[i]It is entirely possible that we will want to initialize the servlet with some data whenit is instantieted. Once the container creates the servlet instance, it calls init(ServletConfig) method on this newly created instance. The servlet is initialized after the init() method returns.
For example, if we wanted to create a database connection in the servlet we would not want to hard-code the user/pwd and the database URL in the servlet. the init() method allows us to specify them in the deployment descriptor. It does not make sense to initialize an object repetedly; therefore, the framework guarantees that the servlet container will call the init() method once and only once on a servlet instance.
SCWCD study kit
Deshmuk, Malavia

I probably misunderstood the question, but I still think a connection to a database should be made in the init method. In fact, I've used this ConnectionBroker before, and the example they have show they made a connection in the init method and put that object in the context, just as I explained before.
Jason:You're better off putting the creation of the JDBC Connection inside the doGet method.
I don't understand why this solution. If you click on a link that invokes your servlet, for example, it'll generate a GET request and you will be creating a JDBC connection again. I still don't think this is a good idea. You should create a broker with X number of connections once and reuse connections everytime you need access to the database. As gregg said, you should release that connection (close()) once you're done (connection pooling)
My $0.02
[ September 18, 2003: Message edited by: Andres Gonzalez ]

[Jeroen Wenting]

Originally posted by Andres Gonzalez:
Andres: I'd put it in the init method of the servlet
Gregg: Very bad idea in my opinion because of mutliple requests on the same object. Can cause all sorts of problems. I would go with Jason on this one...
Then I require clarification on the question:

I probably misunderstood the question, but I still think a connection to a database should be made in the init method. In fact, I've used this ConnectionBroker before, and the example they have show they made a connection in the init method and put that object in the context, just as I explained before.

When you use connection pooling, you either initialise the connection pool in the servlet init() or at the first request.
When you do not, NEVER initialise database connections in init() unless you plan to close them again during init.
In fact, ALWAYS close any database connection before returning from a servlet (whether from init(), service() or a doXXX() method) or you're in for very big trouble due to running out of connections quickly when your application gets under load.
Those connections are lost in the void, no way to close or reuse them, when you exit the method. Only way they'll ever close is when the database session (NOT the HTTP session) times out.
As to using class variables to hold connections, this sounds nice in theory but would cause serious concurrency problems when several requests try to use the connection at the same time, and you loose any means your database has to track transactions.

Jason:You're better off putting the creation of the JDBC Connection inside the doGet method.
I don't understand why this solution. If you click on a link that invokes your servlet, for example, it'll generate a GET request and you will be creating a JDBC connection again. I still don't think this is a good idea. You should create a broker with X number of connections once and reuse connections everytime you need access to the database. As gregg said, you should release that connection (close()) once you're done (connection pooling)
My $0.02
[ September 18, 2003: Message edited by: Andres Gonzalez ]

Your understanding is false.
Even when you use a broker (connection pool is the correct word) you still need to return the connection to the pool when you exit your request method.
This is the same as when you create the connections yourself in each request (again, CLOSE ALL CONNECTIONS BEFORE EXITING ALWAYS!).
Whether the connection pool then closes the connection or keeps it alive is not your concern, but if you fail to return it to the pool you have exactly the same problems as when you had no pool at all as the pool will be constantly opening new connections because the old ones are never released back to it.

[Andres Gonzalez]

Originally posted by Jeroen Wenting:

Your understanding is false.
Even when you use a broker (connection pool is the correct word) you still need to return the connection to the pool when you exit your request method.
This is the same as when you create the connections yourself in each request (again, CLOSE ALL CONNECTIONS BEFORE EXITING ALWAYS!).
Whether the connection pool then closes the connection or keeps it alive is not your concern, but if you fail to return it to the pool you have exactly the same problems as when you had no pool at all as the pool will be constantly opening new connections because the old ones are never released back to it.

Hi Jeroen-

Andres: As gregg said, you should release that connection (close()) once you're done (connection pooling)

This is why I specified "connection pooling" . When I wrote close() this is what I meant: close() in the context we're talking about means releasing the connection to the broker, or to the pool.
I still need an explanation why to put a connection in the doGet() method
thanks
[ September 19, 2003: Message edited by: Andres Gonzalez ]

[Mark Spritzler]

Personally, I'd use EJB's for things like accessing the Database. Using the App Servers Connection pooling process and DataSource objects.
If it is a one time query, then use a stateless session object, if something more permenent the use Entity EJBs accessed via a stateless session bean.
What this can do is allow you to use the EJB in other GUI's too besides your servlet.
But that is my two cents, and I am not a Servlets expert yet.
Mark