SomewhereDave SomewhereDave - 23 days ago 10
MySQL Question

How to share a databse connection among classes? (JDBC)

I'm trying to figure out how to share an established connection to a database among classes to execute different SQL statements.
I read through some of the topics, but as I'm fairly new to programming I have a hard time to adapt the given information to my problem.

Problem: I have two classes with one opening a connection to a database, executing some SQL statments and calling the other class to do the same only using different tables and SQL statments.
Now as long as I run the classes separatly with their own main method and connections everything works fine. But as one class calls the other I get different Exceptions, depending on my workarounds I tried so far (either MySQLNonTransientConnectionException: Data source rejected establishment of connection or a StackOverflowException).

Here is how I'm trying to established a connection that is used to execute some sql operations in two different classes:

public ClassA{

public static Connection dbConn;

//Set up a connection to the database
String dbURL = "jdbc:mysql://<some database>"; //put host, port and database here
Properties connectionProbs = new Properties();
connectionProbs.put("user", "root"); //insert USER here
connectionProbs.put("password", "root"); //insert PASSWORD here

dbConn = null;
try{
dbConn = DriverManager.getConnection(dbURL, connectionProbs);

PreparedStatement useStmt;
try{
useStmt = dbConn.prepareStatement("USE <some database>"); //insert DATABASE here
useStmt.executeUpdate();
}
catch(SQLException e){
e.printStackTrace();
}
//Do some SQL operations
//Call class B to do some SQL operations using the same connection

}
catch(SQLException e){
System.err.println("There was a problem connecting to the database");
e.printStackTrace();
}
finally{
if(dbConn != null)
try{dbConn.close();}catch(SQLException e){e.printStackTrace();}
}
}


Why can't class B use the connection of ClassA, for instance by doing something like this (This leads to a StackOverflow):

PreparedStatement Stmt = ClassA.dbConn.prepareStatement("INSERT INTO table(ID, name) VALUES (?,?)");


On the other hand, if I'm trying to establish two separate Connections (using the same code as above) to the same database (running at the same time) I get the MySQLNonTransientConnectionException: Data source rejected establishment of connection.

What's the best way to handle this? I stumbled upon ConnectionPooling in the forums, but I couldn't find a beginner friendly source to elaborate how to put this into practice. Is there a straight forward way to ensure different classes can connect and operate on one database?

Thanks for any feedback

Answer

You can achieve that by creating a non-static global variable for Connection in Class A and then creating a non-static public method to return this connection, as shown below.

public ClassA{
    // non-static global private Connection object
    private Connection dbConn = null;

    // non-static public method to get dbConn connection object
    public Connection getConnection() {
        // this condition will check if the Connection is not already open then open it.
        if(null == dbConn) {
            //Set up a connection to the database
            String dbURL = "jdbc:mysql://<some database>"; //put host, port and database here
            Properties connectionProbs = new Properties();
            connectionProbs.put("user", "root"); //insert USER here
            connectionProbs.put("password", "root"); //insert PASSWORD here

            try{
                dbConn = DriverManager.getConnection(dbURL, connectionProbs);

                PreparedStatement useStmt;
                try{
                    useStmt = dbConn.prepareStatement("USE <some database>"); //insert DATABASE here
                    useStmt.executeUpdate();
                }
                catch(SQLException e){
                    e.printStackTrace();
                }
                //Do some SQL operations
                //Call class B to do some SQL operations using the same connection

            }
            catch(SQLException e){
                System.err.println("There was a problem connecting to the database");
                e.printStackTrace();
            }
            finally{
                if(dbConn != null)
                    try{dbConn.close();}catch(SQLException e){e.printStackTrace();}
            }
        }
        return dbConn;
    }
}

And then in your class B, you can do something like this.

A a = new A();
PreparedStatement Stmt = a.getConnection().prepareStatement("INSERT INTO table(ID, name) VALUES (?,?)");

Hope this helps you out.

Comments