Java Tip 126: Prepare cross-server database access methods with JDBC

Write portable database access methods for generic database tables

Java has become increasingly popular as a server-side programming language for database-powered Web applications. Those applications' business logic-handling methods must work with data from backend databases. Therefore, you need a set of well-designed database access methods. Those access methods should meet the following requirements:

  1. They should be generic for any table schema. Users can supply the actual table schema information at runtime. Genericness lets you reuse the methods throughout the application to reduce code maintenance overhead and minimize chances for human errors. Genericness also lets you easily add new tables or change existing schema, therefore, making the application more expandable and flexible.

  2. They should be portable across different database servers so the Web application can deploy independent of the backend. Java's built-in, cross-operating system portability no longer suffices for Web applications.

  3. They should be optimized for speed.

In this tip, I discuss how to write generic, portable, and fast database access methods. To illustrate, I provide example code to access an imaginary database table called Article (see Article.sql), which has several fields with different types.

What's wrong with raw SQL statements?

Access methods can generate raw SQL statements at runtime using database table information provided by the access methods' caller method. The caller method knows the table schema and could generate the correct SQL text strings for each Java object it wants to store in the database. The access methods then assemble those strings into raw SQL statements and escape any illegal SQL characters.

This approach is simple but not intelligent. A lot of coding and runtime overhead results from the caller producing the correct SQL text string for each Java object. The solution is also slow. Every time the access method sends a raw SQL statement, it runs the overhead of parsing, compiling, and optimizing the statement. An even bigger problem: the raw SQL-based method cannot be cross-server portable. Different database servers have slightly different SQL syntax. For example, some databases expect YYYY-MM-DD type syntax for the SQL Date field while others might expect DD,MM,YYYY syntax. Databases can also have different SQL text escape requirements. For example, MySQL server uses backslash (\) to escape illegal characters while Microsoft SQL server uses single quote ('). That means any raw SQL-based implementation must target a specific database server.

To overcome the problems of the above raw SQL approach, you can use JDBC's (Java Database Connectivity) PreparedStatement interface.

PreparedStatement for cross-server portability

A PreparedStatement can take a parameterized SQL template and compile it for maximum efficiency. When you need to run a query/update using this template, you only need to reset the parameter values using PreparedStatement.setXXXX() method, where XXXX is the parameter's Java type. For example, setLong(1, articleID) resets the first parameter in the template to long type value articleID. JDBC's PreparedStatement automatically generates the complete SQL statement for execution.

Because the JDBC driver generates the SQL statement according to the particular database server's specifications, you don't need a manual escape and the JDBC driver takes care of the database-specific SQL syntax. The application is portable across all database servers that have JDBC drivers. Also, the template's precompilation greatly improves efficiency.

However, to use PreparedStatement's setXXXX() methods, you must know the database table field's type at compile time. That does not meet our "generic" requirement. "Java Tip 82: Build Data-Type-Independent JDBC Applications" gives a good discussion on how to convert generic type data from external sources to unknown SQL field types at runtime using the table metadata. But for Web applications, the situation is less complicated. The application knows the table schema at runtime. The Web application can also use the appropriate Java object type for data in each table field. For example, if a field is SQL Date type, the corresponding data in the Java application is probably already a java.sql.Date type object rather than a String containing the time information. This lets you use a simple solution to handle the runtime types.

setObject() method for generic types

This solution uses the PreparedStatement.setObject() method instead of setXXXX() methods. Method setObject() uses reflection to figure out a Java object's type at runtime before converting it to an appropriate SQL type. The method converts Java to SQL types using a standard JDBC map. If no match is found on the map, the method throws an exception.

Unfortunately, you cannot use Java primitive types with the setObject() method. Instead, you must use the corresponding object wrapper types. For example, if you want to set long type variable articleID into the template's first parameter, you need to use setObject(1, (new Long(articleID)). You can retrieve the query result data fields as Java objects from ResultSet, using the ResultSet.getObject() method.

Put everything together

Class AccessMethods (see AccessMethods.java) puts together the database access approach I discussed earlier. In addition to type conversion, genericness also requires you to process other schema information, such as table field names at runtime. Example method getSQLList() creates correct SQL field name list substrings for INSERT/SELECT statements from an input array of field names.

AccessMethods.main() puts dummy data into the Article table and then retrieves the data. With the following four steps, you can create an access object for any database table:

  1. Create a new AccessMethods instance using the correct JDBC driver, database connection, authentication, and table name information:

    AccessMethods acc = new AccessMethods( "org.gjt.mm.mysql.Driver",
                                           "jdbc:mysql://localhost/JWTest",
                                           "username",
                                           "password",
                                           "Article" );
    
  2. Create an array of strings to hold the field names:

    String [] fieldnames = { "ArticleID", "Title", "Text",
                             "WordCount", "SubmitDate", "Rating" };
    
  3. If you want to insert a data row into the table, you can first create an object array to hold field values. You should arrange the field values array in the same order as the field names array in Step 2. Then you can call method insert() and pass the field names and field value arrays as parameters:

    // Make up some data
    fieldvalues_in[0] = new Long(0);
    fieldvalues_in[1] = new String("Dummy Article");
    fieldvalues_in[2] = new String("This is a dummy article");
    fieldvalues_in[3] = new Integer(5);
    fieldvalues_in[4] = new Timestamp ( (new java.util.Date()).getTime());
    fieldvalues_in[5] = new BigDecimal(2.5);
    // Store data in database
    acc.insert( fieldnames, fieldvalues_in );
    

    The insert() method compiles a SQL template for the SQL command INSERT from the field names array, sets the values in the template using the field values array, and then executes the generated SQL statement:

    // Prepare the template
    String SQLstr = "INSERT INTO " + TABLENAME + " ( " +
                    getSQLList(fieldnames) + " ) VALUES ( " +
                    getSQLList(paras) + " ) ";
    Connection conn = getConn();
    PreparedStatement pstmt = conn.prepareStatement(SQLstr);
    // set parameter values
    for (int i = 0; i < fieldnames.length; i++) {
      pstmt.setObject(i + 1, fieldvalues[i]);
    }
    // execute SQL statement
    boolean succ = pstmt.execute();
    
  4. If you want to retrieve a data row from the table, you can call method select() and pass to it the field names array and the desired row's primary key field name and value. The returned object array contains the row field values:

    // Retrieve data 
    fieldvalues_out = acc.select( fieldnames, "ArticleID", (new Long(0)));
    

    The select() method executes a SQL SELECT command to retrieve a row in a ResultSet object using the primary key name:value pair idFieldname and idValue:

    // Prepared the template
    String SQLstr = "SELECT " + getSQLList(fieldnames) + " FROM " +
                    TABLENAME + " WHERE " + idFieldname + " = ?";
    Connection conn = getConn();
    PreparedStatement pstmt = conn.prepareStatement(SQLstr);
    // set parameter value
    pstmt.setObject(1, idValue);
    // execute SQL statement
    ResultSet rs = pstmt.executeQuery();
    // go to the first record
    rs.next();
    for ( int i = 0; i < fieldnames.length; i++) {
      result[i] = rs.getObject(i+1);
    }
    

I only implement the INSERT and SELECT SQL statements. You can implement more statements in a similar manner. For simplicity, I excluded the exception-handling code in the examples.

Besides simple types as I illustrated, my approach also works with complex data structures. For example, if you want to put a disk file's binary content into a SQL Blob field, you could define a wrapper class extending FileInputStream and implementing the Blob interface. Then you can pass the wrapper class to setObject(), which then writes the binary stream into the appropriate Blob field. The setObject() method also lets you use custom SQL types. You could just implement a corresponding Java type that implements the SQLData interface and then tell the connection object the new SQL-Java type mapping via the Connection.setTypeMap() method.

Java-SQL exchange

Now you have a flexible yet powerful approach to accessing arbitrary database tables using JDBC's PreparedStatement interface and setObject() method. Using this tip's technique, you can develop complex class structures to help exchange data between SQL relational database tables and Java objects.

Michael Juntao Yuan is a PhD candidate at the University of Texas at Austin. He has designed and implemented database-powered, server-side Java applications to help scientific research for more than two years and has extensive experience with JDBC.

Learn more about this topic