This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes Servlets and the fly likes database connection in init() of servlet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "database connection in init() of servlet" Watch "database connection in init() of servlet" New topic
Author

database connection in init() of servlet

Navatha Cheguri
Greenhorn

Joined: Nov 21, 2005
Posts: 5
Hi all,

I am trying to connect the database(Oracle) in init() method of servlet. I am getting the connection in the init() method but I am not able to access that connection object in my doPost() method. I am getting a nullpointer Exception.
Please tell me how to hold the connection so that my entire application can use it.
Thanks,
Navatha.
Paul Bourdeaux
Ranch Hand

Joined: May 24, 2004
Posts: 783
You should actually be doing this in a listener, more specifically a ServletContextListener. Your code would look something like this:Now you have to configure it in the DD.Hope that helps. FYI, there are several other types of listeners out there that are rather useful when developing web applicatins. You might want to give them a look!


“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
First, you need to know that this is a dangerous and unstable thing to do.

The "init" method is called once, when the servlet is loaded. The "doGet" method might potentially be called many hundreds or thousands of times over a period of weeks, months or years.

Almost all databases will "time out" and disconnect open connections if they are left open for too long. If you only open a connection in "init" it may even "time out" before you get to use it. This also implies that you have no way of re-opening a closed connection without reloading the servlet.

All in all, opening a database connection is "init" is a bad thing to do. A much better solution is to use one of the many implementations of a "connection pool". Instead of opening a connection in "init" you create a connection pool. That connection pool is then available whenever your servlets needs a database connection. Your software asks the pool for a connection, and the pool either hands over an existing open connection, opens a new one for you, or reopens one that has been closed by the database. Either way, your software should not need to worry about it.

I realize that this is not directly answering your question, but trying to work out what is wrong with your current approach might actually be wasted time for you.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Paul Bourdeaux
Ranch Hand

Joined: May 24, 2004
Posts: 783
A much better solution is to use one of the many implementations of a "connection pool".

Frank is exactly right, and I should have really been more clear about that in my last post! You always want to use a connection pool when working with servlets for the reasons he specified.

I would still advocate the use of a listener instead of putting the logic in a servlet's init method though. Good Luck.
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14278
    
  21

And Frank and Paul didn't even mention the thread concurrency issues you'll get yourself into.

Database connections cannot be used in multiple threads simultaneously. If multiple clients are connecting to your servlet at the same time, the servlet container will run your servlet in multiple threads at the same time and you will get into trouble if you use the same database connection at the same time for all clients.


Java Beginners FAQ - JavaRanch SCJP FAQ - The Java Tutorial - Java SE 8 API documentation
Jack Wiesenthaler
Ranch Hand

Joined: Jul 26, 2001
Posts: 75
Guys above are right, what you are doing is not good..consider what happens if the database goes down. Your connection will no longer be valid.
Navatha Cheguri
Greenhorn

Joined: Nov 21, 2005
Posts: 5
Thanks for all of you for giving all excellent suggestions. Actually I found that way of connecting to the database somewhere and I just thought of implementing it first for my little application. I know that there is a better way of doing it by using "connection poolling" though I don't have idea of what is connection Poolling :-) . So I don't know why my code is not working.

public class confirm extends HttpServlet
{
Connection con =null;

public void init(ServletConfig config) throws ServletException
{
super.init(config);

try{
URL[] urls = new URL[1];
urls[0] = new URL("file:/C:/oracle/product/10.2.0/db_1/jdbc/lib/classes12.zip");
String dbUrl = "jdbc racle:thin:@localhost:1521 rcl";
String dbUser = "system";
String dbPass = "myora";
URLClassLoader cl = new URLClassLoader(urls);


Class drvCls = Class.forName("oracle.jdbc.driver.OracleDriver", true, cl);
Driver driver = (Driver)drvCls.newInstance();
System.out.println(drvCls);
System.out.println("<br>");
System.out.println("Drivers are loaded successfully ");
System.out.println("<br>");

Connection con= DriverManager.getConnection(dbUrl,dbUser,dbPass);
System.out.println("Got Connection"+con);
}
catch(Exception E)
{
}

}

public void doPost(HttpServletRequest req, HttpServletResponse res)
throws IOException, ServletException
{
out.println("Got Connection"+con);
Statement st = con.createStatement();
/* some code here*/
}
}

In init() I am able to create connection but In doPost() method the con object is null. Am I doing any silly mistake?
Heonkoo Lee
Ranch Hand

Joined: Feb 10, 2005
Posts: 85
Because you assigned the connection to the local variable instead of the member variable. The member variable 'con' was never assigned the connection reference.

As already mentioned by others, instead of getting a connection in the init method, it is better to obtain a connection on demand and close after use if your site load is not heavy. I would create some ConnectionFactory helper class so that I don't have write the code to get a connection everytime. Better yet, use servlet container provided connection pool.

Regards,
Vishnu Prakash
Ranch Hand

Joined: Nov 15, 2004
Posts: 1026

A much better solution is to use one of the many implementations of a "connection pool".


Kindly explain the above statement.


Servlet Spec 2.4/ Jsp Spec 2.0/ JSTL Spec 1.1 - JSTL Tag Documentation
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14278
    
  21

Connection pooling

Instead of getting a connection directly from DriverManager, you should use javax.sql.DataSource to get your database connections from, and in addition you should set up a connection pool in the servlet container.

The servlet container registers the DataSource object in JNDI, and in your application you lookup the DataSource and call the getConnection() method on it to get your database connection. Your source code will look like this (copied from Tomcat 5.5 documentation):


Note: You should call this in the doPost() method, every time you need a database connection - not just once in the init() method. The idea of a connection pool is that the pool contains a number of open database connections, and whenever your application needs to use the database it borrows a connection from the pool for a short while.

You can just call "conn.close()" on the connection like in the example above - when you do that, the connection is not really closed; it's only returned to the pool.

For information on how to setup a connection pool in Tomcat, see the Tomcat JNDI Datasource HOW-TO.

You can probably find more elaborate and complete examples in the samples and documentation of Tomcat or if you search with Google.
Vishnu Prakash
Ranch Hand

Joined: Nov 15, 2004
Posts: 1026
Thanks Jesper
ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
Originally posted by Jesper de Jong:
And Frank and Paul didn't even mention the thread concurrency issues you'll get yourself into.

Database connections cannot be used in multiple threads simultaneously. If multiple clients are connecting to your servlet at the same time, the servlet container will run your servlet in multiple threads at the same time and you will get into trouble if you use the same database connection at the same time for all clients.


Could you please explain this a bit more. What problem can occur?

Thanks.
Jeff Albertson
Ranch Hand

Joined: Sep 16, 2005
Posts: 1780
Originally posted by rathi ji:


Could you please explain this a bit more. What problem can occur?



Problems abound.

For simplicity, imagine a desktop app with two threads using
the same connection object. Suppose one thread detects a mistake
in its transaction and calls connection.rollback()! What do you
think happens to the work the other thread is doing? Moral:
independent threads shouldn't share connections.
[ November 23, 2005: Message edited by: Jeff Albrechtsen ]

There is no emoticon for what I am feeling!
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14278
    
  21

Originally posted by rathi ji:
Could you please explain this a bit more. What problem can occur?

The Connection object simply wasn't made for concurrent access by multiple threads. The Connection object represents a single connection to a database. Suppose that two threads try to send an SQL statement to the database simultaneously through the same connection. That is like two people trying to talk through the same telephone to someone on the other side. The person on the other side most likely isn't going to understand what they're saying.
Navatha Cheguri
Greenhorn

Joined: Nov 21, 2005
Posts: 5
thank You Heonkoo Lee ...
I did a stupid mistake in my program...Now I got it!.Next my aim is to understand "Connection Pooling"
Thanks alot for everyones help.
ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
Thanks Jeff and Jesper.

I got the reason but not connection pooling. probably I will have to read something for it because I also need it.

Thanks.
ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
Originally posted by Jesper de Jong:

The Connection object simply wasn't made for concurrent access by multiple threads. The Connection object represents a single connection to a database. Suppose that two threads try to send an SQL statement to the database simultaneously through the same connection. That is like two people trying to talk through the same telephone to someone on the other side. The person on the other side most likely isn't going to understand what they're saying.


But If I am creating Connection object in doGet() method (local object) then every thread will gets it's own copy. So it is not sharing of same Connection object....

The problem may occurs if there are too many Connection objects are alive (too many threads are alive).....

Hope my point is clear.



So if the application is not for too many user then we can create Connection object inside doGet() method and use it....
[ November 25, 2005: Message edited by: rathi ji ]
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by rathi ji:


But If I am creating Connection object in doGet() method (local object) then every thread will gets it's own copy. So it is not sharing of same Connection object....

The problem may occurs if there are too many Connection objects are alive (too many thread are alive).....

Hope my point is clear.



So if the application is not for too many user then we can create Connection object inside doGet() method and use it....

If your site is busy enough for this to be a problem, then you should be using connection pooling.
You're going to have even more problems if all of your threads are trying to share a single connection.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Jesper de Jong
Java Cowboy
Saloon Keeper

Joined: Aug 16, 2005
Posts: 14278
    
  21

But If I am creating Connection object in doGet() method (local object) then every thread will gets it's own copy. So it is not sharing of same Connection object....

That's right. If you use DriverManager to open a connection in doGet() (instead of init()) a new connection will be made for every request.

The problem may occurs if there are too many Connection objects are alive (too many thread are alive)..... So if the application is not for too many user then we can create Connection object inside doGet() method and use it....

Yes, you could do that, and yes, your application will not be scaleable. But besides this point, there's something else: opening a database connection is a relatively slow operation (it could take a few seconds), so opening a database connection for each request will also slow down your application considerably.
ankur rathi
Ranch Hand

Joined: Oct 11, 2004
Posts: 3830
Originally posted by Ben Souther:

You're going to have even more problems if all of your threads are trying to share a single connection.


How can it be possible if I am creating Connection object locally. Every thread has it's own copy.
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Originally posted by rathi ji:


How can it be possible if I am creating Connection object locally. Every thread has it's own copy.


If you're creating the connection in one of your service methods, yes, each will have it's own copy.

My point was:
If you're running running out of connections by doing this, creating one connection in your init method is not going to give you a better solution.
Navatha Cheguri
Greenhorn

Joined: Nov 21, 2005
Posts: 5
Hi All,
I read about connection pooling here:
http://tomcat.apache.org/tomcat-5.0-doc/jndi-datasource-examples-howto.html#Oracle%208i%20with%20OCI%20client

It says to update server.xml, so that it contains all the info to connect to the database such as username and password etc.,and then we have to implement getConnection() a method of the interface "DataSource". Is this right? I think I am not getting it. Please help me by suggesting me a tutorial link or by explaining me.

Thanks in advance,
Navatha.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

The page you linked to contains sample code. The short description is that by configuring your database connection through the server, you allow it to manage the database resources, and you then ask the server to provide these resources to you:

In the above code, the only thing that usually changes is the "jdbc/oracle" name that you give to your DataSource.

We're getting further from servlet questions and closer to JDBC questions, but we'll keep the thread here for now
Mohamed Iqzas
Ranch Hand

Joined: Jan 02, 2012
Posts: 63

Jesper de Jong wrote:Connection pooling

You can just call "conn.close()" on the connection like in the example above - when you do that, the connection is not really closed; it's only returned to the pool.


Exactly the info i was looking for a while since I started using the connection pooling... Thanks. Didnt find that else where
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: database connection in init() of servlet