Create Database-driven Resource Bundles with Java and MySQL | 2

Create Database-driven Resource Bundles with Java and MySQL [con't]

Creating a DBResourceBundle

As I explained in my Isolate Translatable Text in XML Files Using the Java 6 ResourceBundle.Control Class article, instantiation of a resource bundle is generally handled by the newBundle() method of the ResourceBundle.Control class. The advantage of overriding the newBundle() method is that it allows you to customize the default implementation of bundle instantiation to suit your specific needs. In this section, you will establish a connection to the database as you did previously so that you can populate ResourceBundles from your resource_bundle table. To fetch data from the table, you need to use the Connection object's createStatement(int resultSetType, int resultSetConcurrency) method. The resultSetType parameter can be ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE. You only need to scroll forward. The resultSetConcurrency can either be ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE. You can make yours read only, as you aren't modifying any data.

String Utilities

Working with databases requires you to pay special attention to strings. A common mistake is to insert a variable into a table like so:

Without performing some kind of filtering on the variables, there is nothing to escape single quotes from the names. Notice the extra quote in the following SQL statement:

You will use three specialized string utilities to optimize the generation of SQL: StrngBuilder, StringUtils, and StringEscapeUtils.

The StringBuilder is used to construct the query string, as it is designed for use as a replacement for StringBuffer in places where the string buffer is used by a single thread (as is generally the case). The Java docs recommended using this class instead of StringBuffer because it is faster under most implementations.

The StringUtils object is included with the MySQL Connector/J driver. Its static isNullOrEmpty() method replaces the cumbersome (somevalue == null || somevalue = "") test.

The StringEscapeUtils is what escapes special characters from query variables. It is a third-party utility included in the org.apache.commons.lang3 package, which is available from their site. Like MySQL Connector/J, the commons-lang-2.5.jar file must be copied to the your $JAVA_HOME/jre/lib/ext directory or added to your CLASSPATH environment variable.

Fetching the Properties

An if statement adds criteria to the query string based on the locale info provided. The completed SQL statement is then executed and stored in a Resultset object. You can then iterate through the results using the Recordset.next() method and add the name/value pairs to the Properties. Finally, the connection is closed and a new DBResourceBundle is instantiated with the Properties object. Here is the complete code for the newBundle() method:

public ResourceBundle newBundle(String      baseName,
                                Locale      locale,
                                String      format,
                                ClassLoader loader,
                                boolean     reload)
    throws IllegalAccessException, InstantiationException, IOException {
    if ((baseName == null) || (locale == null) || (format == null) || (loader == null)) {
        throw new NullPointerException();
    }
    if (!format.equals(FORMAT_DB)) {
         return null;
    }
    Properties p   = new Properties();
    Connection con = null;
    Statement stmt = null;
    ResultSet rs   = null;
    try
    {
        //Register the JDBC driver for MySQL.
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        //Define URL of database server for
        // database named db_resource_bundle_article on the localhost
        // with the default port number 3306.
        String url ="jdbc:mysql://localhost:3306/db_resource_bundle_article";
        //Get a connection to the database
        //with an id and password
        con = DriverManager.getConnection(url, "root", "root");
        //Get a Statement object
        stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                   ResultSet.CONCUR_READ_ONLY);
        StringBuilder query = new StringBuilder();
        query.append("select string_id, value from resource_bundle where bundle='"
                    + StringEscapeUtils.escapeSql(baseName) + "' ");
        if (locale != null)
        {
            if (!StringUtils.isNullOrEmpty(locale.getCountry()))
            {
                query.append("and country_code='" + StringEscapeUtils.escapeSql(locale.getCountry()) + "' ");
            }
            if (!StringUtils.isNullOrEmpty(locale.getLanguage()))
            {
                query.append("and language_code='" + StringEscapeUtils.escapeSql(locale.getLanguage()) + "' ");
            }
            if (!StringUtils.isNullOrEmpty(locale.getVariant()))
            {
                query.append("and variant_code='" + StringEscapeUtils.escapeSql(locale.getVariant()) + "' ");
            }
            rs = stmt.executeQuery(query.toString());
            while (rs.next())
            {
                p.setProperty(rs.getString(1), rs.getString(2));
            }
        }
    }
    catch (Exception e)
    {
        e.printStackTrace();
        throw new RuntimeException("Can not build properties: " + e);
    }
    finally
    {
        try {
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return new DBResourceBundle(p);
}

That's enough coding for one session. I believe that I hear a latte calling. In this information-packed article, you learned how to communicate between Java and a MySQL database using the MySQL Connector/J driver. You also created a test database and table, reviewed some specialized string utilities and overrode the newBundle() method to fetch locale-aware strings from a database. My Next article will walk through creating the DBResourceBundle class and creating a test program that will output text for different locales to illustrate what is happening behind the scenes. Last but not least, I'll discuss some ways to fine tune the loading of your ResourceBundles using the getCandidateLocales() method.

Have a suggestion for an article topic? Do you have a product or service that you'd like reviewed? Email it to Rob .

Rob Gravelle combined his love of programming and music to become a software guru and accomplished guitar player. He created systems that are used by Canada Border Services, CSIS and other Intelligence-related organizations. As a software consultant, Rob has developed Web applications for many businesses and recently created a MooTools version of PHPFreechat for ViziMetrics. Musically, Rob recently embarked on a solo music career, after playing with Ivory Knight since 2000. That band was rated as one Canada's top bands by Brave Words magazine (issue #92) and released two CDs. Rob's latest release is called "The Rabbit of Seville". Loosely based on Rossini's The Barber of Seville overture, Rob's amazing rendition includes a full orchestra and numerous guitar tracks. It is sold on his site as a high bitrate MP3 for only $0.99 cents! Rob is available for short-term software projects and recording session work. to inquire, but note that, due to the volume of emails received, he cannot respond to every email. Potential jobs and praise receive highest priority!

Original: July 8, 2010

[prev]

Create Database-driven Resource Bundles with Java and MySQL | 2

Create Database-driven Resource Bundles with Java and MySQL [con't]

Creating a DBResourceBundle

String Utilities

Fetching the Properties

Find a programming school near you