Creating a database table if it does not exist in Java production code and confirming in JUnit

Viewed 39755

I am writing a database program in Java and want to create a table if it does not already exist. I learned about DatabaseMetaData.getTables() from How can I detect a SQL table's existence in Java? and I am trying to use it:

private boolean tableExists() throws SQLException {
    System.out.println("tableExists()");

    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rs = dbmd.getTables(null, null, this.getTableName(), null);

    System.out.println("TABLE_NAME: " + rs.getString("TABLE_NAME"));

    return rs.getRow() == 1;
}

The problem is that rs.getRow() always returns 0, even after the table has been created. Using rs.getString("TABLE_NAME") throws an exception stating that the result set is empty.

One possible solution I thought of is to execute the CREATE TABLE statement and catch any exceptions that are thrown. However, I don't like the idea of using exceptions for control flow of my program.

FWIW, I am using HSQLDB. However, I would like write Java code that is independent of the RDMS engine. Is there another way to use DatabaseMetaData.getTables() to do what I want? Or is there some other solution to write my tableExists() method?

Added:

Using the suggestions given here, I found a solution that seems to work in my production code:

private void createTable() throws SQLException {
    String sqlCreate = "CREATE TABLE IF NOT EXISTS " + this.getTableName()
            + "  (brand           VARCHAR(10),"
            + "   year            INTEGER,"
            + "   number          INTEGER,"
            + "   value           INTEGER,"
            + "   card_count           INTEGER,"
            + "   player_name     VARCHAR(50),"
            + "   player_position VARCHAR(20))";

    Statement stmt = conn.createStatement();
    stmt.execute(sqlCreate);
}

Now I am also writing a JUnit test to assert that the table does indeed get created:

public void testConstructor() throws Exception {
    try (BaseballCardJDBCIO bcdb = new BaseballCardJDBCIO(this.url)) {
        String query = "SELECT count(*) FROM information_schema.system_tables WHERE table_name = '" + bcdb.getTableName() + "'";
        Connection conn = DriverManager.getConnection(this.url);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        Assert.assertTrue(rs.next());
        Assert.assertEquals(1, rs.getInt(1));
        Assert.assertFalse(rs.next());
    }
}

This test fails on the assertEquals() with the following message:

FAILED: expected: <1> but was: <0>
4 Answers
Related