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

Question

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.

Answer 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.

Answer 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());     }   }