connection closed, now won't open! :-s 1

[RiazKhanmohamed]

Managed to close my connection. my current connectionmanager class looks like:

***CODE START***
import java.sql.*;

public class ConnectionManager{

private static ConnectionManager conMan;
private Connection con;

private ConnectionManager(){
try {
// Load the Oracle Driver
Class.forName("oracle.jdbc.driver.OracleDriver");

try{
// Get a connection from the connection factory
con = DriverManager.getConnection("jdbcracle:thin[servername]:1521:dec", "[username]", "[password]");
}
catch (SQLException e){
System.out.println("Cannot connect");
System.out.println(e.getMessage());
}
}
catch (ClassNotFoundException ex){
System.out.println("Cannot find driver");
//System.exit(-1);
}
}

public static ConnectionManager getInstance(){
if(conMan == null)
conMan = new ConnectionManager();
return conMan;
}

public Connection getConnection(){
return con;
}
}
***CODE STOPS****

works fine.

i now have multiple mappers. each has a connection function:

***CODE START***
public void conConnect()throws SQLException{
ConnectionManager man = ConnectionManager.getInstance();
con = man.getConnection();
}
***CODE STOPS***

and a close connection function:

***CODE STARTS***
public void conClose() throws SQLException{
if (!con.isClosed()){
con.close();
}
}
***CODE STOPS***

I have multiple functions in my middle/problem domain running these queries - each makes a connection, runs several queries, then closes the connection.

THE PROBLEM:

Once the first function closes the connection, any subsequent connectoin creations that occur don't work, refusing access to the database.

Before i simply created the connection on creation of the database mapper instance, but this keps a single connectoin open permanently, which eventually timed out - i know i had to close it somewhere so this is what i have.

Please note code is valid, and it connects/disconnects the first time round, but never again.

Please help, this is really starting to bug me badly!

Your assistance is greatly appreciated.

 

[sedj]

Your problem is that ConnectionManager.getInstance() returns a static (ie only ever one instance) reference to itself. So once you create a new ConnectionManager instance, there is only ever one Connection object associated with it. Once your first call opens and then closes the Connection, all other calls to that ConnectionManager instance are stuffed because that instance's (remember its static) COnnection object is closed.

Consider this Test scenario :

public class Test {

	public static void main(String args[]) throws Exception {

		new Test().qqq();
		new Test().qqq();
		new Test().qqq();
		new Test().qqq();

	}

	public void qqq() throws Exception {
		Connection conn = conConnect();
			java.sql.Statement s = conn.createStatement();
			java.sql.ResultSet rs = s.executeQuery("show tables");
			while (rs.next())
				System.err.println(rs.getString(1));


			rs.close();
			s.close();
			conn.close();
	}

    public Connection conConnect()throws SQLException{
        ConnectionManager man = ConnectionManager.getInstance();
        Connection con = man.getConnection();
        System.err.println(man);
        System.err.println(con);
        return con;
    }

}

If you take out the close() statement then all is well and all qqq() methods run. But if the first call to qqq() closes the Connection, then the rest will barf.

To fix it, stop returning a static COnnectionManager class, or return a new COnnection on each of tht static classe's getCOnnection() call.

If you wish to use the same Connection objects over and over without actually closing them, then look into Connection pooling (google it, theres loads of examples) plus db vendors also tend to supply their own pools sometimes.

 

[RiazKhanmohamed]

I always assumed it was once per creation of that instance. Guess that makes more sense really. I guess connection pooling is my only real practical solution here.

Thanks.

 

[RiazKhanmohamed]

ps i'd rather hard code it - my client doesn't wish to be tied to one vendor as they aren't sure if they wish to stay with the server kit they use at present. I'd rather keep the options open.

 

[Khanjan]

FredPerry Hi,
You had replyed in a thread about a subject, You wrote:
"Don't know if this would help, but i've got a similar system, although it's not as high level secure. What i do is on login store the loginid in a session variable. then on every page within the system, it checks if there is one, and if not returns to the login page. it's tedious but works. i also have frames, so i put this code on them, so if people bookmark my site it still returns them to the login screen.

i just used an if statement to check and then redirected the user to the login."

Could you send the code that you use, the check jsp and the jsp where you put the loginid into a session variable,
Thanx,

hope this helps