Closing The connection (Database)

[Jigar Naik]

Hi,

In my web application.

i have one java class which contains more than 20 method. each method does database related operations. so i have created one constructor for that class.
which parse xml for connection and create one connection conn. I'm using 1 connection for all the 20 method.

now ideally where should i close the connection ???

if i close connection in the finally clause of every method. it will give run time exception on next method. as i'm creating connection once when my class is loaded(In Constructor).

some code bellow here can give you ruff idea.

   
public class Mail {
public static String connectionClass;
public static String connectionName;
 
Connection conn = null;
 
String user;
String password;
String total_connection;
 
public void parse_conn_xml() throws Exception{
...
...
...
Mail.connectionClass = list.getElementsByTagName("connectionClass").item(0).getTextContent();
...
...
...
Mail.connectionName = total_connection+ "?user=" + user + "&password=" + password;
...
...
...
 
}
//Constructor
Mail(){
try{
parse_conn_xml();  //calling method to parse connection xml
prop.load(connProp);
Class.forName(Mail.connectionClass);
conn = DriverManager.getConnection(Mail.connectionName);
}catch(Exception e){
e.printStackTrace();
}finally{
System.runFinalization();
}
}
}
 
//Other method which will use conn
public void send_mail(String from,String sub,String message) throws Exception{...}
 
public void resp_mail(String from,String sub,String message,String to) throws Exception{
try{
...
...}catch(....){
...
...
}finally{    //Same in all the methods
System.runFinalization();
}
 
public void get_dbcols(String table_name) throws Exception{{...}
 
...
... and so on
...
} //Mail Class Ends

[ May 08, 2007: Message edited by: Jigar Naik ]

[Jesper de Jong]

A very short introduction to using databases in web applications:

In a web application, you should not be opening and closing JDBC connections directly like that. You should configure a database connection pool in your servlet container, and then in your web application lookup the DataSource from JNDI. You get the connection from the DataSource object. After you're done, you just call close() on the connection, which doesn't really close it in that case; it just returns it to the pool.

Instructions for configuring a data source in Tomcat

Your code should look something like this:

import java.sql.*;
import javax.naming.*;
import javax.sql.*;
 
// ...
 
public void someMethod() {
    // Lookup the DataSource using JNDI
    Context ctx = new InitialContext().lookup("java:comp/env");
    DataSource ds = ctx.lookup("jdbc/MyDatabaseJNDIName");
 
    // Get a database connection from the connection pool
    Connection conn = ds.getConnection();
 
    // ... Use the connection here ...
 
    // Return the connection to the pool
    conn.close();
}

One remark about your code above:

}finally{    //Same in all the methods
System.runFinalization();
}

Why did you include this? This shouldn't be necessary. I'd remove the call to System.runFinalization() unless there is a very specific reason why you need this.

[Rahul Bhattacharjee]

    }finally{    //Same in all the methods
    System.runFinalization();
    }
 

Why did you include this? This shouldn't be necessary. I'd remove the call to System.runFinalization() unless there is a very specific reason why you need this.

I too feel that its not necessary.

Well when you close a connection then exception might occur , so it would be a good feature to check in the finally whether its closed or not again.In case its not closed then close it again and null the reference.

[Jigar Naik]

Well when you close a connection then exception might occur , so it would be a good feature to check in the finally whether its closed or not again.In case its not closed then close it again and null the reference

if i close connection in finally clause.

than won't it throw exception on next method which also need connection ?

The code bellow will call method of Mail class.

ControllerServlet

   
...
...
[B]line 1 : [/B]Mail mail = new Mail(); //Constructor runs so i will be having one conn open
mail.method1(); method runs fine and in the finally block it will close the connection
mail.method2(); another method call, won't it throw Connection Closed ??? as i have closed the connection in the finally block of method1 and my constructor will also get called on line 1 only.

[Rahul Bhattacharjee]

Originally posted by Jigar Naik:


if i close connection in finally clause.

than won't it throw exception on next method which also need connection ?

As I and Jasper have already mentioned that when you close a connection that has been taken from a connection pool , in that case the actual connection object will not be closed rather returned to the pool.Again when you need connection then you have to again ask the pool to give you a connection.