Centralized DB connection management in Java

Question

For compliance reasons, my organization is looking to take our database connection settings out of properties/XML config files and into a central registry - ideally federated over multiple physical machines, to avoid a single point of failure.

I have been looking at the possibility of using JNDI to achieve this, but haven't really got much experience in using it myself. Has anyone got experience attempting to do anything similar? Or any better ideas of how this might be achieved?

I should add that our apps are standalone and do not run within any J2EE container, therefore any container specific connection management techniques may not be particularly useful.

Thanks in advance!

Answer 1

What regulation are you attempting to be compliant with?

One thing that you can do is to depend on a datasource that is exposed via JNDI. This will mean, however, that your connection settings are located at the site of your configuration of the datasource.

I doubt that you need to 'go horizontal' with you database connection settings. Is there a specific reason that you believe that having the settings in one place could introduce risk to your application(s)?

Answer 2

It might be simpler to stick with the XML/properties file approach, but use some kind of centralised configuration management system to control the content of those files.

Presumably if you're having to consider centralising things like datasource configuration, you'll already have something in place (CFEngine, puppet, chef, rdist, ...) to manage operating system configuration files - so why not just use that?

Answer 3

This might be a good start:

HashMap<String,String> dsNames = new HashMap<String,String>();
System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.fscontext.RefFSContextFactory");
    System.setProperty(Context.PROVIDER_URL, "file:/home/user/tmp");
    InitialContext ic = new InitialContext();
    dsNames.put("yourDataSourceName", "jdbc/your_jndi_name");
    // Construct BasicDataSource reference
    Reference ref = new Reference("javax.sql.DataSource",
            "org.apache.commons.dbcp.BasicDataSourceFactory", null);
    ref.add(new StringRefAddr("driverClassName", "theDriver"));
    ref.add(new StringRefAddr("url","theDBURL"));
    ref.add(new StringRefAddr("username", "obvious"));
    ref.add(new StringRefAddr("password", "obvious"));
    ref.add(new StringRefAddr("maxActive", "10"));
    ref.add(new StringRefAddr("maxIdle", "3"));
    ref.add(new StringRefAddr("initialSize", "3"));
    ref.add(new StringRefAddr("maxWait", "5"));
    ic.rebind("jdbc/your_jndi_name", ref);

    //And to get a reference to your data source anywhere else in your program:
    InitialContext ic2 = new InitialContext();
    DataSource myDS = (DataSource) ic2.lookup(dsNames.get(dsName));

Now as you can see, I'm using a pooled connection factory, but you can replace that to suit your needs. Also, the initial context factory is an FSContext which means the JNDI name will be looked up in a regular file system, an URL Context Factory must be available but I have never used it. Most classes used are in the javax.naming package.

Hope this helps.