CODEWITHSUNDEEP
javah2
Dragon
Dragon

Reputation: 2481

H2 DB: How to check whether table schema is initialized programmatically?

I have a DB schema that creates several tables and fills them with data. I want to check whether db contains corresponding tables or not during app start. I could check for db file existence, but H2 creates db if it doesn't exist. So the only way, I think, is to check for tables existence.

Here is the code of how I initialize DB:

Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:database/svc", "sa", "");

Statement st = conn.createStatement();
st.execute("CREATE TABLE IF NOT EXISTS table1 (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, name VARCHAR(100), record INT, record_date DATE, UNIQUE(name))");
st.execute("CREATE TABLE IF NOT EXISTS table2 (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, name VARCHAR(100), record INT, record_date DATE, UNIQUE(name))");
st.execute("CREATE TABLE IF NOT EXISTS daily_record_stat (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL, date DATE, table1_id INT, table1_record INT, table2_id INT," +
            " table2_record INT, total_record INT);");
st.execute("ALTER TABLE daily_record_stat ADD FOREIGN KEY (table1_id) REFERENCES table1(id);");
st.execute("ALTER TABLE daily_record_stat ADD FOREIGN KEY (table2_id) REFERENCES table2(id);");
st.execute("INSERT INTO table1 VALUES(1, 'non_existed_stub', 0, NULL)");
st.execute("INSERT INTO table2 VALUES(1, 'non_existed_stub', 0, NULL)");

conn.close();

As you see, I check for table existence before creation using IF NOT EXISTS statement. But then I run at the problem with ALTER and INSERT - these commands don's allow IF usage.

I tried to do the following:

Connection conn = DriverManager.getConnection("jdbc:h2:database/svc", "sa", "");
ResultSet meta = conn.getMetaData().getTables(null, null, "table1", null);
if(meta.next()) {
    //do something
}

But meta.next() is false.

So how to check whether table schema is initialized? Or maybe this should be done some other way?

Upvotes: 3

Views: 4584

Answers (2)

Cebence
Cebence

Reputation: 2416

This is a check I used to (re)create the H2 database:

// IMPORTANT A sorted list of table names.
private static final String[] REQUIRED_TABLES = { "USER", ... };
public static final String CREATE_USER = "create table USER (...)";

private boolean schemaExists() throws SQLException {
  final List<String> requiredTables = Arrays.asList(REQUIRED_TABLES);
  final List<String> tableNames = new ArrayList<String>();

  final Connection conn = dataSource.getConnection();
  try {
    final Statement st = conn.createStatement();
    final ResultSet rs = st.executeQuery("show tables");
    while (rs.next()) {
      tableNames.add(rs.getString("TABLE_NAME"));
    }
    rs.close();
    st.close();
  }
  finally {
    if (conn != null) { conn.close(); }
  }

  Collections.sort(tableNames);
  return tableNames.equals(requiredTables);
}

private void initializeDatabase() throws SQLException {
  final Connection conn = dataSource.getConnection();
  try {
    if (schemaExists()) {
      return;
    }

    final Statement st = conn.createStatement();
    st.executeUpdate(CREATE_USER);
    conn.commit();
  }
  finally {
    if (conn != null) { conn.close(); }
  }
}

And you just call:

  initializeDatabase();

Notice the list of required tables has to be sorted because I use List.equals() to compare two lists. It would probably be better to also programmatically sort the required tables list too.

It's not fool-proof (not checking if any table exists and if it should be altered) but it works for me.

Take a look at the SHOW command for other uses.

Upvotes: 1

Dmitry Kuskov
Dmitry Kuskov

Reputation: 1021

Not sure if that's what you mean by saying to check programmatically, buy you can try to use DatabaseMetaData.getTables(). This call will return ResultSet which you'll have to check programmatically. You can see what fields are returned in this ResultSet in corresponding section here. And meta data itself can be obtained by conn.getMetaData().

Following code should return all tables which names start with 'TABLE':

ResultSet meta = conn.getMetaData().getTables(null, null, "TABLE%", new String[]{"TABLE"});
while (meta.next()) {
     System.out.println(meta.getString(3));
}

Note that you have to specify table name pattern in upper case. Also it's good to pass table types that you need, although it is optional.

Upvotes: 3

Related Questions