Java connecting to multiple databases

Question

I am creating a java application that connects to multiple databases. A user will be able to select the database they want to connect to from a drop down box.

The program then connects to the database by passing the name to a method that creates an initial context so it can talk with an oracle web logic data source.

public class dbMainConnection {

    private static dbMainConnection conn = null;
    private static java.sql.Connection dbConn = null;
    private static javax.sql.DataSource ds = null;
    private static Logger log = LoggerUtil.getLogger();

    private dbMainConnection(String database) {

         try {

            Context ctx = new InitialContext();

            if (ctx == null) {
                log.info("JDNI Problem, cannot get InitialContext");
            }

                database = "jdbc/" + database;
                log.info("This is the database string in DBMainConnection" + database);
                ds = (javax.sql.DataSource) ctx.lookup (database);


        } catch (Exception ex) {
            log.error("eMTSLogin: Error in dbMainConnection while connecting to the database : " + database, ex);
        }

    }

    public Connection getConnection() {

        try {

            return ds.getConnection();

        } catch (Exception ex) {
            log.error("Error in main getConnection while connecting to the database : ", ex);
            return null;
        }

    }

    public static dbMainConnection getInstance(String database) {

        if (dbConn == null) {
            conn = new dbMainConnection(database);
        }

        return conn;

    }

    public void freeConnection(Connection c) {
        try {
            c.close();
            log.info(c + "  is now closed");
        } catch (SQLException sqle) {
            log.error("Error in main freeConnection : ", sqle);
        }
    }

}

My problem is what happens if say someone forgets to create the data source for the database but they still add it to the drop down box? Right now what happens is if I try and connect to a database that doesn't have a data source it errors saying it cannot get a connection. Which is what I want but if I connect to a database that does have a data source first, which works, then try and connect to the database that doesn't have a data source, again it errors with

javax.naming.NameNotFoundException: Unable to resolve 'jdbc.peterson'. Resolved 'jdbc'; remaining name 'peterson'.

Which again I would expect but what is confusing me is it then grabs the last good connection which is for a different database and process everything as if nothing happened.

Anyone know why that is? Is weblogic caching the connection or something as a fail safe? Is it a bad idea to create connections this way?

did you try to null the ds in the catch scope of dbMainConnection ? — A.B.Cade, Dec 13 '11 at 15:35

score 5 · Accepted Answer · answered Dec 13 '11 at 15:43

You're storing a unique datasource (and connection, and dbMainConnection) in a static variable of your class. Each time someone asks for a datasource, you replace the previous one by the new one. If an exception occurs while getting a datasource from JNDI, the static datasource stays as it is. You should not store anything in a static variable. Since your dbMainConnection class is constructed with the name of a database, and there are several database names, it makes no sense to make it a singleton.

Just use the following code to access the datasource:

public final class DataSourceUtil {
    /**
     * Private constructor to prevent unnecessary instantiations
     */
    private DataSourceUtil() {
    }

    public static DataSource getDataSource(String name) {
        try {
            Context ctx = new InitialContext();
            String database = "jdbc/" + name;
            return (javax.sql.DataSource) ctx.lookup (database);
        }
        catch (NamingException e) {
            throw new IllegalStateException("Error accessing JNDI and getting the database named " + name);
        }
    }
}

And let the callers get a connection from the datasource and close it when they have finished using it.

Awesome. Thanks for the help. Obviously I am a java newb so thanks for the clear explanation and code. Just to make sure I understand. I get rid of the dbconnect class instead use the dataSourceUtil class to get the datasource. Then use it to create my connection to the different databases. Afterwords close the database connection and the initial context. Is that right? — enderjs, Dec 13 '11 at 18:20
Right (except for the InitialContext close, which you can't close, but I don't think it matters to let it open). — JB Nizet, Dec 13 '11 at 18:51

score 0 · Answer 2 · answered Dec 13 '11 at 15:44

You're catching JNDI exception upon lookup of the nonexistent datasource but your singleton still keeps the reference to previously looked up datasource. As A.B. Cade says, null reference to ds upon exception, or even before that.

On a more general note, perhaps using Singleton is not the best idea.

Java connecting to multiple databases

2 Answers2

Linked