Apache Derby - Check Database Already Created?

Question

Hey,

Using Apache Derby with Java (J2ME, but I don't think that makes a difference) is there any way of checking if a database already exists and contains a table?

Cheers,

Pete

Answer 1

I know of none, except few work around, unlike MySQL where we have that facility of IF EXIST.

What you do is, try to connect to the database, if couldn't its likely its not there. And after a successful connection, you can do a simple select, like SELECT count(*) FROM TABLE_NAME, to know whether the table exist or not. You would be depending on the exception. Even in an official example from Sun, I have seen the similar work around.

In Oracle we have dictionary tables to know about the database objects. I doubt if we have anything like that in Derby.

[Edited]

Well, I found that there is a way to know if the table exist. Try, SELECT tablename FROM SYSTABLES. It is for checking the existence of a table, for checking database you may need to do similar thing, I explained above.

Answer 2

Vinegar You could also use Connection.getMetaData to return a DatabaseMetaData object, then use the getTables, once you have the connection to the database of course. This has the advantage of working for any database with a JDBC driver worth it's salt.

For checking if the database exists, if you are using Derby in the embedded way, or the server is on the same machine, you could check if the folder for the database exists. Is a bit kludgy though. I would do as Vinegar suggests and try to connect, catching the exception if it's not there.

Answer 3

I would suggest getting the DatabaseMetaData object, then using the getTables(null, null, null, new String[]{"TABLE"}) method from it, which returns a ResultSet. Use the next() method of the ResultSet, which returns a boolean, to test if any tables exist. If it tests true, you have tables in existence. False, and the database is empty.