StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POH2 DB: How to check whether table schema is initialized programmatically?
text
Body
copied!<p>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.</p> <p>Here is the code of how I initialize DB:</p> <pre><code>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(); </code></pre> <p>As you see, I check for table existence before creation using <code>IF NOT EXISTS</code> statement. But then I run at the problem with <code>ALTER</code> and <code>INSERT</code> - these commands don's allow IF usage.</p> <p>I tried to do the following:</p> <pre><code>Connection conn = DriverManager.getConnection("jdbc:h2:database/svc", "sa", ""); ResultSet meta = conn.getMetaData().getTables(null, null, "table1", null); if(meta.next()) { //do something } </code></pre> <p>But <code>meta.next()</code> is <code>false</code>.</p> <p>So how to check whether table schema is initialized? Or maybe this should be done some other way?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload