X

Geertjan's Blog

  • May 14, 2011

Providing Database Settings Externally

Geertjan Wielenga
Product Manager

The question of the day comes from Diane Kramer from VisiTrend in Boston:

"I don't understand how to create a properties file (where
do I put it?) that can be read when the program first starts up."

The
reason Diane wants to do this is that she's working on a
database-oriented application for neuroscientists where the database
settings need to be provided externally. I.e., there should be a file
that the neuroscientists can get to very easily (so, not somewhere
within a user directory or something like that). The simplest place
would be, I imagine, within the installation directory of the
application. That's also dangerous because if the neuroscientist
uninstalls the application, the database settings will also be gone.
However, I don't know of a better, clearer, place to put database
settings than in the installation directory, so, in this particular
situation, where you want to let the user easily (i.e., these
are not techy users, so they're not going to appreciate being directed
to an obscure file location somewhere) provide database settings in a
file outside the application, which is then read by the application,
the procedure that follows seems reasonable to me.

Let's
simulate the scenario. I have a TopComponent where my data access code
is found, which is a very bad idea if you've been following my
capabilitiy-based development series on NetBeans Zone
, but is OK for a
prototype. In the constructor of my TopComponent:

public DBTopComponent() {

    initComponents();
    setName(NbBundle.getMessage(DBTopComponent.class, "CTL_DBTopComponent"));
    setToolTipText(NbBundle.getMessage(DBTopComponent.class, "HINT_DBTopComponent"));

    Connection connection = null;
    try {
        Class.forName(DRIVER);
        connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        PreparedStatement ps = connection.prepareStatement(QUERY);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            String column = rs.getString(COLUMN);
            if (column != null) {
                jTextArea1.append(column + "\n");
            }
        }
        connection.close();
    } catch (SQLException ex) {
        Exceptions.printStackTrace(ex);
    } catch (ClassNotFoundException ex) {
        Exceptions.printStackTrace(ex);
    }

}

So,
above, I have a set of properties, for db connection, user, password,
etc, which the user should be able to configure from outside the
application. We imagine a properties file in the installation directory
with this content:

#Person database via MySQL:
#URL=jdbc:mysql://localhost:3306/travel
#USERNAME=root
#PASSWORD=root
#DRIVER=com.mysql.jdbc.Driver
#QUERY=SELECT * FROM person
#COLUMN=name

#Customer database via Derby:
URL=jdbc:derby://localhost:1527/sample
USERNAME=app
PASSWORD=app
DRIVER=org.apache.derby.jdbc.ClientDriver
QUERY=SELECT * FROM CUSTOMER
COLUMN=NAME

Of course, not a very secure approach, but that's not the point. The point is how to implement this. So,
let's say the above file is found in the installation directory of the
application. It is named "database.properties". A simpler name and a
simpler location is hard to imagine. If the file does not exist, we can
create it when the application starts. We'll then fill it with some
predefined values. The neuroscientist should be able to change the file
outside the application and then restart the application with the new
values being used.

  1. Put your "database.properties"
    file, with the content above, into a folder named "release" in your
    module. To do so, switch to the Files window, and (if it does not
    already exist) create a folder named "release", at the same level as
    "nbproject", "src", and "test". When you create a ZIP distribution (or
    you use the installer generator), the "database.properties" file will
    be placed within the top level of your cluster. (Try it. Create a ZIP
    distribution via "Package as | ZIP Distribution" and then look in
    "dist/dbvis.zip/dbvis" in the Files window. You'll see
    "database.properties" there, within the folder for your cluster, i.e., not in "bin" or in "etc" or "platform", but in "dbvis" or whatever your cluster is named.)

  2. Now we can initialize the values in our TopComponent using the external properties file, as follows, which in this simple prototype is added right above the previous code snippet:
File databaseFile = InstalledFileLocator.getDefault().locate("database.properties", "org.my.code.name.base", false);
//Do some null checking...
FileObject databaseFO = FileUtil.toFileObject(databaseFile);
Properties databaseProperties = new Properties();
try {
    databaseProperties.load(databaseFO.getInputStream());
    DRIVER = databaseProperties.getProperty("DRIVER");
    USERNAME = databaseProperties.getProperty("USERNAME");
    PASSWORD = databaseProperties.getProperty("PASSWORD");
    URL = databaseProperties.getProperty("URL");
    COLUMN = databaseProperties.getProperty("COLUMN");
    QUERY = databaseProperties.getProperty("QUERY");
} catch (IOException ex) {
    Exceptions.printStackTrace(ex);
}

That's all. The user can now change the properties file, restart the app, and have different data available. For further reading, see org.openide.modules.InstalledFileLocator and this blog entry. Hope it helps, Diane!


Join the discussion

Comments ( 2 )
  • guest Saturday, May 14, 2011
    why code does not highlighted? :)
    File - > Print as HTML works well :)
  • Jesse Glick Thursday, May 19, 2011
    This is bad advice. Files in the installation should never be considered writable. That will break when you use Auto Update. Provide an option panel (or other GUI) in the app that lets you configure database settings, and use NbPreferences to persist them. Also do not store passwords in cleartext. Use the Keyring API.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.