Close Database connection

[RicardoPereira]

Hi,
I'am using a class to access from a jsp page to an sql server 2000 database. I think i must include in the code below something to close the connection after de execution of the code. What do i need to include ? Anyone could help me ?

import java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
Connection con;

static {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}
catch( ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
}

public AccessDB() throws SQLException {
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
}

public ArrayList getAuth(String username, String password) throws SQLException {
ArrayList res=new ArrayList();
PreparedStatement pstmt=
con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

ResultSet rslt=pstmt.executeQuery();
while (rslt.next()) {
res.add(new xpto.TblUsers (rslt.getString(1)) );
}
return res;
}
}

 

[sedj]

Before you return the "res" object in the AccessDB() method, you should do the following :

rslt.close();
pstmt.close();
con.close();

If you do not close all objects related to database access you may have open cursors left on the database, even though you think they are no longer used.

You should also consider using database connection pooling

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

Could you please explain summary what is that database connection pooling ?

 

[sedj]

Go to :

http://www.primrose.org.uk/faq.jsp

and click on the "What is a connection pool" link.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

But now i have this message
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.

Here is my code:

import java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
Connection con;

static {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}
catch( ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
}

public AccessDB() throws SQLException {
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
}

public ArrayList getAuth(String username, String password) throws SQLException {
ArrayList res=new ArrayList();
PreparedStatement pstmt=
con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

ResultSet rslt=pstmt.executeQuery();
while (rslt.next()) {
res.add(new xpto.TblUsers (rslt.getString(1)) );
}
rslt.close();
pstmt.close();
con.close();
return res;
}
}

 

[sedj]

Before this line :
PreparedStatement pstmt = con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

add :

if (con == null || con.isClosed()) {
AccessDB();
}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

but if i had more arrays i must close all the arrays connections ?
Should i do something like this:

import java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
Connection con;

static {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}
catch( ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
}

public AccessDB() throws SQLException {
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
}

public ArrayList getAuth(String username, String password) throws SQLException {
ArrayList res=new ArrayList();
if (con == null || con.isClosed()) {
AccessDB();
}
PreparedStatement pstmt=
con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

ResultSet rslt=pstmt.executeQuery();
while (rslt.next()) {
res.add(new xpto.TblUsers (rslt.getString(1)) );
}
return res;
}

public ArrayList getPerms(String data, String param) throws SQLException {
ArrayList res=new ArrayList();
if (con == null || con.isClosed()) {
AccessDB();
}
PreparedStatement pstmt=
con.prepareStatement("exec Extranet_Params'" + data + "', '" + param + "'");

ResultSet rslt=pstmt.executeQuery();
while (rslt.next()) {
res.add(new xpto.TblUsers (rslt.getString(1)) );
}
return res;
}

}

 

[sedj]

Well, I would reorganise your code a bit, make it a bit mre generic, so that you keep the JDBC code in the relevant generic methods, and the specific methods away from actual JDBC code. Something a bit more like :

mport java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
    
    static {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        }
        catch( ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }
    
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
    }
    
    public ArrayList executeStatement(String sql) {
    	Connection con = this.getConnection();
    	ArrayList res = new ArrayList();
        PreparedStatement pstmt = con.prepareStatement(sql);

        ResultSet rslt = pstmt.executeQuery();
        while (rslt.next()) {
            res.add(new xpto.TblUsers (rslt.getString(1)) );
        }
        
        rslt.close();
        pstmt.close();
        con.close();
        
        return res;
    }
    
    public ArrayList getAuth(String username, String password) throws SQLException {
        ArrayList res = this.executeStatement("exec Extranet_permission'" + username + "', '" + password + "'");
        return res;
    } 

    public ArrayList getPerms(String data, String param) throws SQLException {
        ArrayList res = this.executeStatement("exec Extranet_Params'" + data + "', '" + param + "'");
        return res;
    }

}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

But that code works if i use only the class Tblusers ... and if i have something like this:

import java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
Connection con;

static {
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
}
catch( ClassNotFoundException e) {
e.printStackTrace();
System.exit(1);
}
}

public AccessDB() throws SQLException {
con = DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
}

public ArrayList getAuth(String username, String password) throws SQLException {
ArrayList res=new ArrayList();
PreparedStatement pstmt=
con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

ResultSet rslt=pstmt.executeQuery();
while (rslt.next()) {
res.add(new xpto.TblUsers (rslt.getString(1)) );
}
return res;
}

public ArrayList getPerms(String data, String param) throws SQLException {
ArrayList res=new ArrayList();
PreparedStatement pstmt=
con.prepareStatement("exec Extranet_Params'" + data + "', '" + param + "'");

ResultSet rslt=pstmt.executeQuery();
while (rslt.next()) {
res.add(new xpto.TblData (rslt.getString(1)) );
}
return res;
}

}


How do i do ?

 

[sedj]

Ricardo :

I realise the code I posted is not going to run "out of the box" - but try to understand the point I was making :

You should attempt to keep database access and execution away from the logic of your application. You should separate the two out.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

I understand your point of view and i realise that too but, i really need some help on this.
Assuming that you have this code, what's your suggestion?
If you could give some example I am thankful.

 

[sedj]

import java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
    Connection con;
    
    static {
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        }
        catch( ClassNotFoundException e) {
            e.printStackTrace();
            System.exit(1);
        }
    }
    
    public AccessDB() throws SQLException {
    	if (con == null || con.isClosed() {
        	con = DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
        }
    }
    
    public ArrayList getAuth(String username, String password) throws SQLException {
        ArrayList res=new ArrayList();
        AccessDB();
        
        PreparedStatement pstmt=
        con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

        ResultSet rslt=pstmt.executeQuery();
        while (rslt.next()) {
            res.add(new xpto.TblUsers (rslt.getString(1)) );
        }
        return res;
    } 

    public ArrayList getPerms(String data, String param) throws SQLException {
        ArrayList res=new ArrayList();
        AccessDB();
        
        PreparedStatement pstmt=
        con.prepareStatement("exec Extranet_Params'" + data + "', '" + param + "'");

        ResultSet rslt=pstmt.executeQuery();
        while (rslt.next()) {
            res.add(new xpto.TblData (rslt.getString(1)) );
        }
        return res;
    } 

}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

Ok, that makes sence. But where do i close the connection ?

 

[sedj]

As I said earlier, befire you return the "res" object in the get* methods, you should do the following :

rslt.close();
pstmt.close();
con.close();


--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

But if i close the connection before the return the "res" object i got this error:
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.

 

[sedj]

import java.sql.*;
import java.lang.*;
import java.util.ArrayList;
import xpto.*;

public class AccessDB {
    
    
    public Connection getConnection() throws SQLException {
	    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
	    return DriverManager.getConnection("jdbc:microsoft:sqlserver://server:1433;User=***;Password=***;DatabaseName=Test;");
    }
    
    public ArrayList getAuth(String username, String password) throws SQLException {
        ArrayList res=new ArrayList();
        Connection con = getConnection();
        
        PreparedStatement pstmt=
        con.prepareStatement("exec Extranet_permission'" + username + "', '" + password + "'");

        ResultSet rslt=pstmt.executeQuery();
        while (rslt.next()) {
            res.add(new xpto.TblUsers (rslt.getString(1)) );
        }
	rslt.close();
	pstmt.close();
	con.close();  
	
        return res;
    } 

    public ArrayList getPerms(String data, String param) throws SQLException {
        ArrayList res=new ArrayList();
        Connection con = getConnection();
        
        PreparedStatement pstmt=
        con.prepareStatement("exec Extranet_Params'" + data + "', '" + param + "'");

        ResultSet rslt=pstmt.executeQuery();
        while (rslt.next()) {
            res.add(new xpto.TblData (rslt.getString(1)) );
        }
	rslt.close();
	pstmt.close();
	con.close();         
        
        return res;
    } 

}

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

Yeap, now it works.

Do you think using de connection pooling i got a substantial database access time redution ?
This implies a deep alteration in the code?

 

[sedj]

You will see HUGE reduction in the time it takes to execute SQL statements, because (unless you are doing 5 minute SQL queries) the most amount of time taken during a JDBC/SQL transaction is actually getting a COnnection object from the database.

Database pooling means that a pool of Connection objects is maintained for the clients.

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk

 

[RicardoPereira]

Could you help me to implement the "connection pooling" in my code ?

 

[sedj]

There is extensive documentation on the pooling code I wrote here :

http://www.primrose.org.uk/faq.jsp

http://www.primrose.org.uk/documentation.jsp

or if you wish to use tomcat's pooling, then here :

http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html

Note that from a developer's perspective, both pools require the same client code, its just the config that is very slightly different (and of course I think my pool is better !! )

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk