Is It Necessary to Create Tables Each Time You Connect the Derby Database

Is it necessary to create tables each time you connect the derby database?

Three common reasons for "table does not exist" when you think you've already created the tables:

1. You are connecting to a different database than you think you were connecting to, and since you specified "create=true" on the Connection URL, Derby quietly created a new empty database for you.
2. You are using the "in-memory" configuration of Derby, which means that when the database is closed (or your application exits), all the contents of the database disappear.
3. You are connecting to the database as a different user, and you aren't issuing the SET SCHEMA statement, so you are using the default schema name, which is based on your user name, and so the two schemas are different and have completely different tables, so the table you created doesn't seem to exist when you use the other schema.

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

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.

jdbc with embedded derby in javafx application: Table/View 'TABLE NAME' does not exist exception

As @Mark Rotteveel said it's all about the relative paths and directories, and it's well explained in the answer of @BlueRat at this question

---

Related Topics