Creating a database table if it does not exist in Java production code and confirming in JUnit

Question

I am writing a database program in Java and want to create a table if it does not already exist. I learned about DatabaseMetaData.getTables() from How can I detect a SQL table's existence in Java? and I am trying to use it:

private boolean tableExists() throws SQLException {
    System.out.println("tableExists()");

    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rs = dbmd.getTables(null, null, this.getTableName(), null);

    System.out.println("TABLE_NAME: " + rs.getString("TABLE_NAME"));

    return rs.getRow() == 1;
}

The problem is that rs.getRow() always returns 0, even after the table has been created. Using rs.getString("TABLE_NAME") throws an exception stating that the result set is empty.

One possible solution I thought of is to execute the CREATE TABLE statement and catch any exceptions that are thrown. However, I don't like the idea of using exceptions for control flow of my program.

FWIW, I am using HSQLDB. However, I would like write Java code that is independent of the RDMS engine. Is there another way to use DatabaseMetaData.getTables() to do what I want? Or is there some other solution to write my tableExists() method?

Added:

Using the suggestions given here, I found a solution that seems to work in my production code:

private void createTable() throws SQLException {
    String sqlCreate = "CREATE TABLE IF NOT EXISTS " + this.getTableName()
            + "  (brand           VARCHAR(10),"
            + "   year            INTEGER,"
            + "   number          INTEGER,"
            + "   value           INTEGER,"
            + "   card_count           INTEGER,"
            + "   player_name     VARCHAR(50),"
            + "   player_position VARCHAR(20))";

    Statement stmt = conn.createStatement();
    stmt.execute(sqlCreate);
}

Now I am also writing a JUnit test to assert that the table does indeed get created:

public void testConstructor() throws Exception {
    try (BaseballCardJDBCIO bcdb = new BaseballCardJDBCIO(this.url)) {
        String query = "SELECT count(*) FROM information_schema.system_tables WHERE table_name = '" + bcdb.getTableName() + "'";
        Connection conn = DriverManager.getConnection(this.url);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        Assert.assertTrue(rs.next());
        Assert.assertEquals(1, rs.getInt(1));
        Assert.assertFalse(rs.next());
    }
}

This test fails on the assertEquals() with the following message:

FAILED: expected: <1> but was: <0>

Hi, why not just use the SQLs CREATE TABLE name IF NOT EXISTS (...) ? Its much easier, and if the table already exists, it leaves it in — Balázs Édes
– Balázs Édes, Commented Aug 10, 2012 at 21:14
@bali182 Thanks for the suggestion. The correct syntax is CREATE TABLE IF NOT EXISTS <name> (<columns>). Now I am also writing a JUnit test. If you have time, some help would be great! — Code-Apprentice
– Code-Apprentice, Commented Aug 10, 2012 at 22:02
I bet you don't have read rights on information_schema schema (or it doesn't exist) — Alonso Dominguez
– Alonso Dominguez, Commented Aug 10, 2012 at 23:10
@AlonsoDominguez I will look into that when I have some more time tomorrow. — Code-Apprentice
– Code-Apprentice, Commented Aug 10, 2012 at 23:46

Code-Apprentice · Accepted Answer · 2012-08-10 21:37:05Z

10

The solution I found seems to work:

private void createTable() throws SQLException {
    String sqlCreate = "CREATE TABLE IF NOT EXISTS " + this.getTableName()
            + "  (brand           VARCHAR(10),"
            + "   year            INTEGER,"
            + "   number          INTEGER,"
            + "   value           INTEGER,"
            + "   card_count           INTEGER,"
            + "   player_name     VARCHAR(50),"
            + "   player_position VARCHAR(20))";

    Statement stmt = conn.createStatement();
    stmt.execute(sqlCreate);
}

I had to place the IF NOT EXISTS in the correct location in my SQL statement.

answered Aug 10, 2012 at 21:37

Code-Apprentice

84k26 gold badges162 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alonso Dominguez · Accepted Answer · 2012-08-10 21:18:42Z

3

From the ResultSet definition at Java docs:

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

So, you must always call the next() method otherwise getRow() will always return zero as the cursor is positioned before the first row.

answered Aug 10, 2012 at 21:18

Alonso Dominguez

7,8781 gold badge29 silver badges38 bronze badges

1 Comment

Code-Apprentice Over a year ago

Hmm...my ResultSet is empty ;-(

Robin · Accepted Answer · 2012-08-10 21:24:00Z

1

There is build in mysql functionality for what you seek: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

In short: Just append IF NOT EXISTS at the end of your table creation query.

Edit:
There is no general way of doing this. Most databases have an information_scheme table though, a query to determine the information could look like this:

SELECT count(*) FROM information_schema.system_tables WHERE table_schem = 'public' AND table_name = 'user';

This works with sqlite, mysql, msql, mariadb and postgres + probably a lot of others.

edited Aug 10, 2012 at 21:24

answered Aug 10, 2012 at 21:13

Robin

1,3422 gold badges13 silver badges23 bronze badges

5 Comments

Code-Apprentice Over a year ago

Thanks for the suggestion. I need my code to be independent of any specific database engine.

Code-Apprentice Over a year ago

OHHHH! I don't need RDBM independent code...I just need different subclasses for each database engine...grrrr

Code-Apprentice Over a year ago

In fact, that might be best since there may be slight differences in the CREATE TABLE syntax anyway...

Code-Apprentice Over a year ago

Your original suggestion to use IF NOT EXISTS seems to work. The correct syntax is CREATE TABLE IF NOT EXISTS <name> (<columns>).

Code-Apprentice Over a year ago

Hey, I tried using your select statement and it's returning 0. I'm using HSQLDB.

user1590499 · Accepted Answer · 2012-08-10 21:15:06Z

-2

I don't know if this will necessarily help towards your goals, but when I ran into this problem using Python and MySQL, I just added a "Drop Table" statement before each "Create Table" statement, such that just running the script automatically deletes the existing table, and then rebuilds each table. That may not work for your needs, but it's one solution that I found successful for my similar problem.

answered Aug 10, 2012 at 21:15

user1590499

9933 gold badges10 silver badges17 bronze badges

2 Comments

Ben Over a year ago

The main (very large) problem with this is that if the table is being used chances are it has data in it. Dropping the table isn't the best solution then.

Code-Apprentice Over a year ago

Thanks for the suggestion. Unfortunately this won't work because eventually the table will have data in it that I don't want to get rid of. I just need to create the table the first time the program runs...maybe some kind of flag would be just as good...hmm...

Collectives™ on Stack Overflow

Creating a database table if it does not exist in Java production code and confirming in JUnit

4 Answers 4

Comments

1 Comment

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related