How to check if existing database matches program's format

Question

First, I'm new to Java DB programming so maybe my approach is wrong. So please keep that in mind. Now the question:

My program let's the user open an existing Java Derby database. When it's opened I want to check if the database contains the correct tables and each table has the correct columns - not just names but type also. So far I'm trying to create each table and if it throws an exception then I know the table exists. Now I need to check column types. What is the best practice to do this? Hard-code check for each column type as answered here in the catch block?

Answer 1

If you want to figure out what tables are present in the database, and what columns each table has, etc., there are (at least) two simple approaches:

1. Issue SQL queries against the Derby system catalogs, such as

   select s.schemaname || '.' || t.tablename
   from sys.systables t, sys.sysschemas s
   where t.schemaid = s.schemaid and t.tabletype = 'T' order by s.schemaname, t.tablename;

2. Use the API provided by java.sql.DatabaseMetaData, and its methods such as getSchemas(), getTables(), and getColumns()