Can my connection object made available on all my forms?

Question

I'm developing a java based application using NetBeans. My app opens with a window with asks the user to enter their credentials and based on data entered, a connection is established between the app and my MySQL client (I'm using JDBC for this purpose).

My issue: I want the connection object (which is declared and initialized after checking the credentials of the user) to be available for use in all my form. Previously, i have being doing this by passing the connection object from one form to another. But i don't want to do that! I want once this connection object is declared, it's made available to all the forms in the app.

Answer 1

I want the connection object (...) to be available for use in all my form

You should not have an open connection while your application lives. Instead, use a database connection pool of 1 or 2 connections that will be available for all the application and add a shutdown hook to close this data source when the application finishes. The connection pool will take care to maintain the connections alive and use low resources for it.

For example: your user opens the application and enters its credentials, then leaves the room because he/she has to do some paperwork and takes 30 mins, then goes back to the pc and try to use an option. If using a static Connection con object, you manually opened a physical connection to the database and you're in charge to control the connectivity for all these 30 minutes, and if you don't do any action in that time then probably the physical connection was closed by the database engine. If using a connection pool, this will take care of opening/closing physical connections and maintaining them in sleep state so your connection won't be lost.

Note that your Connection object and related resources (PreparedStatement, ResultSet, etc). should be in the narrowest possible scope.

---

Here's a minimal example of doing this using BoneCP as database connection pool.

public class ConnectionProvider {
    private static DataSource dataSource;
    private static boolean initialized = false;

    public static void init(Map<String, String> conf) {
        if (!initialized) {
            //synchronization to avoid multiple threads accesing to this part of the method
            //at the "same time"
            synchronized(DataSourceProvider.class) {
                //double validation in case of multi threaded applications
                if (!initialized) {
                    //you may add more validations here
                    //in case you want to use another datasource provider
                    //like C3PO, just change this part of the code
                    BoneCPDataSource bds = new BoneCPDataSource();
                    bds.setDriverClass(conf.get("driver"));
                    bds.setJdbcUrl(conf.get("url"));
                    bds.setUsername(conf.get("user"));
                    bds.setPassword(conf.get("password"));
                    //this should be obtained as configuration parameter
                    bds.setMaxConnectionsPerPartition(2);
                    //you can add more BoneCP specific database configurations
                    dataSource = bds;
                    initialized = true;
                }
            }
        }
    }

    public static Connection getConnection() {
        if (dataSource == null) {
            //this should be a custom exception in your app
            throw new RuntimeException("Data Source was not initialized.");
        }
        return dataSource.getConnection();
    }
}

And the client (once you have called the init method and provided the database configurations). I'm avoiding exception handling for brevity:

public class SomeDao {
    private Connection con;
    //using Dependency Injection by composition for DAO classes with connection
    public SomeDao(Connection con) {
        this.con = con;
    }
    public SomeEntity getSomeEntity(int id) {
        String sql = "SELECT id, col1, col2 FROM someEntity WHERE id = ?";
        //PreparedStatement and ResultSet go on the narrowest possible scope
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setInt(1, id);
        ResultSet rs = pstmt.executeQuery();
        SomeEntity someEntity = new SomeEntity();
        if (rs.hasNext()) {
            someEntity.setId(rs.getInt("id");
            //similar for other columns...
        }
        //don't forget to close the resources after its usage
        return someEntity;
    }
}

public class SomeService {
    public SomeEntity getSomeEntity(int id) {
        //retrieving the connection at this level
        //a service may access to several daos
        Connection con = ConnectionProvider.getConnection();
        //performing the operations against DAO layer
        SomeDao someDao = new SomeDao(con);
        SomeEntity someEntity = someDao.getSomeEntity(id);
        //closing the connection. This is A MUST
        //here the connection pool won't close the physical connection
        //instead put it to sleep
        con.close();
        //return the proper data at a single point of the method
        return someEntity;
    }
}

Answer 2

Don't use the same Connection in your application! But what you want to achieve could be done using static variable. For example, add the following code to any of your classes, or create a new class for it:

private static Connection con = null;
public static Connection getConnection (String url)
{
    if (con == null)
        con = DriverManager.getConnection(url);
    return con;
}

Then, call MyClass.getConnection("jdbc:mysql://localhost:3306/") or whatever the connection string is, and it will return one Connection that you could use for all classes.