How to create missing database view in Derby?

Question

I'd like to create a view in Derby RDBMS when my WebApp starts up (ApplicationListener.contextInitialized) if it does not exist yet. In this point of time there is not a transaction so I have to use JDBC & SQL. My tries with DatabaseMetaData.getTables() were not successfull. It gives back always an empty result set but I can see in the Services tab in NetBeans it definitively exists (and the requested table as well). The code:

public isDBViewExists( Connection conn_, String catalogName_, String schemaName_, String viewName_ ) throws SQLException
{
  conn_.setAutoCommit( false );
  DatabaseMetaData metadata = conn_.getMetaData();
  ResultSet rs = metadata.getTables( catalogName_, schemaName_, viewName_, new String[] { "VIEW" } );
  return rs.next();
}

The Connection created by the injected DataSource resource in the app context event handler:

@Resource( name="jdbc/x" )
DataSource ds;
...
try
{
  Connection conn = ds.getConnection();
  if ( isDBViewExists( conn, ... ) )
  ...
}
finally
{
  conn.close();
}

All of the passed names are in upperCase (catalog, schema, view/table). The conn_ is not null. What is my fault?

Answer 1

In this case it's probably easier to just create the table and ignore the SQLException that comes back if the table already exists. Let the DB worry about checking if the table exists already.

For example:

Connection conn = ds.getConnection()
try {
  conn.createStatement().executeUpdate("CREATE TABLE ....");
} finally {
  conn.close();
} catch(SQLException ignore) {
  // ignore exception, not much can go wrong here except for the table already existing.

  // If you don't mind making vendor specific logic, check the error message for "already exists" or some equivalent
  if(!ignore.getMessage().contains("already exists"))
    throw ignore;
}