Creating a database table if it does not exist in Java production code and confirming in JUnit
java, jdbc, junit, sql
Solution
The solution I found seems to work:
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);
}
I had to place the `IF NOT EXISTS` in the correct location in my SQL statement.
Problem
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> ```