how to create table if it doesn't exist using Derby Db

I am new to apache derby and I cant seem to make work

    CREATE TABLE IF NOT EXISTS table1 ...

as can be achieved in MySql etc. I am getting a 'Syntax error: Encountered "NOT" at line 1, column 17.', when I try to run this SQL statement in my Java program.

I checked in the documentation page for Derby Db Create Statements, but couldn't find such an alternative.

Solution 1:

Create the table, catch the SQLException and check SQL status code.

The full list of error codes can be found here but I couldn't find Table <value> already exists; it's probably X0Y68. The code you need is X0Y32.

Just run the code once and print the error code. Don't forget to add a test to make sure the code works; this way, you can catch changes in the error code (should not happen ...).

In my projects, I usually add a helper class with static methods so I can write:

} catch( SQLException e ) {
    if( DerbyHelper.tableAlreadyExists( e ) ) {
        return; // That's OK
    }
    throw e;
}

Another option is to run a SELECT against the table and check the status code (which should be 42X05). But that's a second command you need to send and it doesn't offer any additional information.

What's worse, it can fail for other reasons than "Table doesn't exist", so the "create-and-ignore-error" is better IMO.

Solution 2:

Derby does not support that sql-statement.
In my program I parse all the Tables from the Database into a Set and check if the table exists there. Like this:

  private Set<String> getDBTables(Connection targetDBConn) throws SQLException
  {
    Set<String> set = new HashSet<String>();
    DatabaseMetaData dbmeta = targetDBConn.getMetaData();
    readDBTable(set, dbmeta, "TABLE", null);
    readDBTable(set, dbmeta, "VIEW", null);
    return set;
  }

  private void readDBTable(Set<String> set, DatabaseMetaData dbmeta, String searchCriteria, String schema)
      throws SQLException
  {
    ResultSet rs = dbmeta.getTables(null, schema, null, new String[]
    { searchCriteria });
    while (rs.next())
    {
      set.add(rs.getString("TABLE_NAME").toLowerCase());
    }
  }