database connection in init() of servlet

[Navatha Cheguri]

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]

You should actually be doing this in a listener, more specifically a ServletContextListener. Your code would look something like this:

public class MyServletContextListener implements ServletContextListener {
    public void contextInitialized(ServletContextEvent event) {
        // This is where you would put your code to initialize the
        // database connection.  You could then set it as an attribute.
        // Now the entire application will have access to it.
        
        // If you really wanted to be cool you would put the database
        // initialization information in the DD as a context init parameter.
        // That way you would not have to change code if the database
        // changes, just the web.xml
    }
 
    public void contextDestroyed(ServletContextEvent event) {
        // Don't forget to close your database connection when the 
        // application is shutting down.  Put that code here!
    }
}

Now you have to configure it in the DD.

<listener>
  <listner-class>
    com.yourpackage.MyServletContextListener
  <listener-class>
</listener>

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!

[Frank Carver]

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.

[Paul Bourdeaux]

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]

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.

[Jack Wiesenthaler]

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]

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]

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]

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

Kindly explain the above statement.

[Jesper de Jong]

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):

// Obtain our environment naming context
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
 
// Look up our data source
DataSource ds = (DataSource)
  envCtx.lookup("jdbc/EmployeeDB");
 
// Allocate and use a connection from the pool
Connection conn = ds.getConnection();
... use this connection to access the database ...
conn.close();

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]

Thanks Jesper

[ankur rathi]

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]

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 ]

[Jesper de Jong]

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]

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]

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]

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]

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.

[Jesper de Jong]

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]

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]

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]

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]

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:

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();
//etc.

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]

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