Simple Thread Problem

[abhi_elementx]

Hello folks,
I m tying to implement a Singleton pattern;

Iwant connect to MySQL databaseusing only a single Connection object(Singleton). I have implemented the application using threads.
I run 2 threads. The second thread should use the Connection object created by the first thread which is not happening.
My code:

//MySQLConnector.java
package singletondemo;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Abhi
 */

import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MySQLConnector {//This uses Singleton
   public  MySQLConnector(){}

   private static Connection con = null;
   
   public static Connection initiate(){//return a Singleton
       try{
        if(con == null){ 
            System.out.println("Making new connection(object)..");
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/tox_erp","root","tiger");
            System.out.println("Done");    
        }
        else{
            System.out.println("Connection(object) already exists...returning old connection(object)..");
            return con;
        }    
       }catch(Exception e){} 
       return con;
   }//initiate() ends
   
   public Statement connectdb(){
       Statement s = null;
    try {
      System.out.println("Requesting new connection(object)..");  
      Connection con = initiate();  
      s = con.createStatement();
      if(s == null)System.out.println("Cannot create Statement..");
     } catch(Exception e) {System.err.println("Exception: " + e);}   
    return s;
   } //connectdb() ends
   
   public void disconnectdb(){
        try {
            System.out.println("Closing connection...");
            con.close();
            System.out.println("Done");
        } catch (SQLException ex) {
            Logger.getLogger(MySQLConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
   }//disconnectdb() ends
}

//ShowResultSet_1.java

package singletondemo;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author Abhi
 */
import java.sql.*;

class Client extends Thread{
	Thread t;
	public Client(String str){
			t = new Thread(str);
			//t.setName(str);
			t.start();
		}
	
	public void run(){
		System.out.println(this.getName()+"  executing..");
        MySQLConnector mysqlconnector = new MySQLConnector();
        Statement s = null;
        try{
            System.out.println(this.getName()+" :First time connection..");
            s = mysqlconnector.connectdb();
            ResultSet R = s.executeQuery("select * from login_info;");
            while(R.next()){
                System.out.println(this.getName()+"Uname in db is : "+R.getString("uname"));
            }
            s.close();
            
        }catch(Exception e){System.out.println(e);}
        
        finally{//make sure connection is closed
            try{
                mysqlconnector.disconnectdb();
            }catch(Exception e){}
        }
	}
	
}

public class ShowResultSet_1 {
    
    public static void main(String args[]){
        Client c1 = new Client("Client 1");
        c1.start();
        
        Client c2 = new Client("Client 2");
        c2.start();
        
    }
}

//Output
Thread-0 executing..
Thread-1 executing..
Thread-1 :First time connection..
Thread-0 :First time connection..
Requesting new connection(object)..
Requesting new connection(object)..
Making new connection(object)..
Making new connection(object)..
Done
Done
Thread-1Uname in db is : single
Closing connection...
Thread-0Uname in db is : single
Done
java.sql.SQLException: Operation not allowed after ResultSet closed
Closing connection...
Done
Thanks.

[stephen84s]

The problem I think is being caused by :-

private static Connection con = null;

Because of that only one instance of your Database connection is maintained. But I think that is what you intend to do.
However let us take a look at the effects of this because you see in multithreading you have to think parallel and not sequential.

Now look at the "finally" block in your run() method.

finally{//make sure connection is closed
            try{
                mysqlconnector.disconnectdb();
            }catch(Exception e){}
        }

It calls the disconnectdb() method, which I feel is fine, because it should properly dispose of the database connection it acquired. However let us look at the disconnectdb() method :-

public void disconnectdb(){
  try {
    System.out.println("Closing connection...");
    con.close();
    System.out.println("Done");
  } catch (SQLException ex) { 
    Logger.getLogger(MySQLConnector.class.getName())
        .log(Level.SEVERE, null, ex);
  }
}

It just directly closes the database connection irrespective of whether it might be used in another different instance of "Client", So when your second object tried to access the database it finds that the connection has been closed (either while querying the database or while fetching the results from the ResultSet depending on the timing of completion of first thread).

Also remember that the same database connection cannot be used simultaneously by two threads for querying at the same time, you will need to close the resultset of the first thread and only then you can use the same connection for the second query, else you will get a "Connection Already in Use" or some similar problem.