Monday, March 1, 2010

Derby "create table if not exists"

A few discussions have asked how to asked how to mimic use "Create table if not exists" functionality in Derby Java DB. This is a non-standard sql extension, implemented in sqlite and MySQL among other databases. The most obvious use of this functionality is in embedded database applications where you don't want to trouble the user with db creation. (sqlite and derby both suitable for this use case.) You can create the db as part of the install process, but if you want to support a db per user of the app or similar scenario, you generally want to create the db the first time a given user runs the app.

Trying to create a table that already exists will throw a SQLException. Some authors have suggested that in the above scenario, you simply catch and ignore this exception on subsequent calls after the initial call. This is a bad idea unless you search the exception for a specific code because you will be unable to handle any problems with db creation on the initial run of the app.

A clearer way to handle this is to use java.sql.DatabaseMetaData interface. This is implemented by most (all?) db providers and allows you to check existence in about 3 lines of code. Here's an example:

Connection dbConnection
= DriverManager.getConnection("jdbc:derby:MyDb;create=true");
DatabaseMetaData dbmd = dbConnection.getMetaData();
ResultSet rs = dbmd.getTables(null, "MYSCHEMA", "MYTABLE", null);
if(!rs.next())
{
createMyTable();
}


You can find a discussion with the alternate detection method here on nabble: http://old.nabble.com/%22create-table-if-not-exists%22---td22558480.html

1 comment: