Making sample JDBC code easier

I’m writing a lot of small JDBC examples for our upcoming practice tests book. I got tired of writing the same code over and over so I wrote this helper class. It can:

  • Drop the table if it already there (because I run the same examples many times)
  • Run a varargs of SQL statements (to create tables and insert data)
  • Spit out the contents of a table (to confirm what happened)


import java.sql.*;
public class DerbyUtil {

    public static void dropTablesIfExist(String url, String... tables) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String table : tables) {
                try (PreparedStatement ps = conn.prepareStatement("DROP TABLE " + table)) {
                    ps.executeUpdate();
                } catch (SQLException e) {
                    // ignore exceptions on drop since table might not exist
                }
            }
        }
    }

    public static void run(String url, String... sqlStatements) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String stmt : sqlStatements) {
                try (PreparedStatement ps = conn.prepareStatement(stmt)) {
                    ps.executeUpdate();
                }
            }
        }
    }

    public static void outputTable(String url, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName;
        try (var conn = DriverManager.getConnection(url);
             var ps = conn.prepareStatement(sql);
             var rs = ps.executeQuery()) {

            var metadata = rs.getMetaData();
            int numColumns = metadata.getColumnCount();

            while (rs.next()) {
                outputRow(rs, numColumns);
            }
        }
    }

    private static void outputRow(ResultSet rs, int numColumns) throws SQLException {
        for (int i = 1; i <= numColumns; i++) {
            System.out.print(rs.getString(i));
            System.out.print('\t');
        }
        System.out.println();
    }
}

try-with-resources and jdbc without sql injection

As I was on Oracle’s JDBC tutorial page, I noticed it was using a Statement rather than a PreparedStatement. I grumbled to myself about how this is teaching people to develop using SQL Injection and decided to Google for an example so I could tweet about it.

I was looking for an example of using try-with-resources automatic resource management using PreparedStatements. I searched on google for “try with resources jdbc”. This didn’t go well. I found a lot more of the same example including one from Martjin and Ben whom I respect. It is even that anyone’s example is bad. It is just oversimplified and implies that using createStatement is more common than using prepareStatement.

I then searched for “try with resources preparedstatement” to be more specific about it and found:

  • Informit does use a PreparedStatement but one without any binding variables.  Which means as an example, it isn’t much better.
  • JavaCodeGeeks does the same.
  • Someone on StackOverflow asked how best to do it and got an answer involving a nested try.  Which does work, but the nested try seem less readable than it needs to be.

I propose:


public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = createPreparedStatement(conn);  ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

list.add(rs.getString("name"));

}

}

return list;

}

private PreparedStatement createPreparedStatement(Connection conn) throws SQLException {

PreparedStatement ps = conn.prepareStatement(SQL);

ps.setString(1,  "test");

return ps;

}

The StackOverflow post proposes:

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = conn.prepareStatement(SQL)) {

stmt.setString(1, "test");

try (ResultSet rs = stmt.executeQuery()) {

while (rs.next()) {

list.add(rs.getString("name"));

}

}

}

return list;

}

The StackOverflow answer is shorter.  I think the reason I like mine better is that is is easier to template out so the JDBC plumbing is only in a superclass.  Leaving us with

public List<String> query(Connection conn) throws SQLException {

List<String> list = new ArrayList<String>();

try (PreparedStatement stmt = createPreparedStatement(conn); ResultSet rs = stmt.executeQuery()) {

return processResultSet(rs);

}

return list;

}

The subclass then has two methods to implement:

  1. PreparedStatement createPreparedStatement(Connection conn)
  2. T processResultSet(ResultSet rs)  [templated to return type of subclass’ choosing]

Which approach do you like better?

Connecting to MS Access Files via JDBC in 64-bit Java

In 32-bit Java, the technique for connecting to a Microsoft Access file via JDBC connection is well-known and available as part of the Sun JDK. There are a number of errors, though, if you attempt to use a 64-bit version of Java that are not as well documented. This article points out some of those issues and a working strategy for how to successfully connect to a Microsoft Access file via JDBC in 64-bit Java.

1. Review of the 32-bit Connection Strategy

The well-known syntax for connecting to an Microsoft Access file via JDBC is as follows:

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

If you are using the Sun JDK, then the driver will be available in the classpath automatically. Notice, I make sure to close my connection object in a finally block as all good JDBC developers know to do.

2. Errors in the 64-bit World

Attempting to run the proceeding code returns the following error when using a 64-bit JDK:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

If you would prefer to stick with the 32-bit version of the driver, there are two options available:

  • Use the 64-bit Java installation but run java.exe with the “-D32” flag.
  • Use a 32-bit Java installation

Both of these solutions limit the amount of memory a Java application can use.

3. Adapting JDBC to 64-bit Java

If you would prefer to use the 64-bit Java, there is a solution, although for some users this may require removing Microsoft Office.

Step #1: Download the Microsoft Access Database Engine 2010 Redistributable, specifically the AccessDatabaseEngine_x64.exe file.

Step #2: Install the Microsoft Access Database Engine. If you are running a 32-bit version of Microsoft Office, you will likely get the following error when you try to install it:

You cannot install the 64-bit version of Office 2010 because you have 32-bit Office products installed.

At this point, you must decide whether or not to uninstall the 32-bit version of Microsoft Office. Newer versions of Office, such as 2010, often contain both 32-bit and 64-bit versions on the installation DVD, although the 32-bit version is used by default when installed via the AutoRun process. In this case, you would first uninstall the 32-bit version of Office. Restart the computer. Then, ignore the AutoRun on the DVD and manually open the setup executable in the x64 directory. After a 64-bit Office version is installed, continue with the Microsoft Access Database Engine installation.

Note: If you are installing a recent 64-bit version of Microsoft Office, you may be able to skip installing the Microsoft Access Database Engine, as it is often included in the Office installation.

If a 64-bit version of Office is not available, then you will unable to run the 32-bit version of Microsoft Office alongside the 64-bit Microsoft Access Database Engine, and must decide which is more important.

Step #3 Update the JDBC code to use the following revised connection string as follows:

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

After making these changes, you should be able to connect to your Access database in 64-bit Java.