Java jdbc check if table exists

Question

I'm trying to check if the table I created already exists. Somehow it always goes into the if (as if the table does not exist). And it drops the current table and creates a new one. What I'd like it to do is, it shouldn't drop the table if the table is already there.

 public void createTable() throws SQLException {
        L.info("Creating tables...");
        L.info("Check if already there...");
        DatabaseMetaData dbm = connection.getMetaData();
        ResultSet tables = dbm.getTables(null, null, "videogamestable", null);
        if (!tables.next()) {

            System.out.println("Table doesn't exist so creating it...");
            L.info("Table videogamestable does not yet exists, creating it and all others");
            Statement statement = connection.createStatement();
            statement.execute("DROP TABLE IF EXISTS videogamestable");
            String createQuery = "CREATE TABLE videogamestable " +
                    "(id INTEGER NOT NULL IDENTITY," +
                    "name VARCHAR(45) NOT NULL," +
                    "price DOUBLE," +
                    "releaseDate DATE," +
                    "genre VARCHAR(15)," +
                    "founder VARCHAR(25)," +
                    "minimumRequiredAge INTEGER," +
                    "rating DOUBLE)";
            statement.execute(createQuery);
            statement.close();
            //PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO videogamestable VALUES (NULL, ?, ?, ?)");
            for (VideoGame videoGame : Data.getData()) {
                insert(videoGame);
            }
            System.out.println("Database aangemaakt");
            //preparedStatement.close();
        } else {
            System.out.println("Table already exists");
            L.info("Table videogamestable does already exist!");
            //throw new VideoGameException();
        }
    }

Which database system are you using? The problem is likely with the fact that `getTables` is **case-sensitive** to how a name is actually stored in the database metadata tables, and in a lot of database systems, a table created with `create table videogamestable` is not actually called `videogamestable`, but `VIDEOGAMESTABLE`. — Mark Rotteveel, Jan 05 '21 at 17:22

Tim Biegeleisen · Answer 1 · 2021-01-05T15:43:15.153

Taken from this SO question, you may define a helper method which can check for the existence of the table:

public boolean tableExists(String tableName, Connection conn) {
    boolean found = false;
    DatabaseMetaData databaseMetaData = conn.getMetaData();
    ResultSet rs = databaseMetaData.getTables(null, null, tableName, null);
    while (rs.next()) {
        String name = rs.getString("TABLE_NAME");
        if (tableName.equals(name)) {
            found = true;
            break;
        }
    }

    return found;
}

Note that the third parameter to DatabaseMetaData#getTables() is actually a pattern, so it is possible that multiple table names matching it could be found and returned in the result set. Therefore, we iterate the entire result set and do an equality check against the exact table name being passed to the helper method.

I've tried this now, but it seems like the program just skips the while loop. — Robbe Gillis, Jan 05 '21 at 16:10
There is no need to compare `tableName` with the result of `rs.getString("TABLE_NAME")`, assuming that `tableName` doesn't contain `_` or `%`, it will match exactly. — Mark Rotteveel, Jan 05 '21 at 17:27

Java jdbc check if table exists

1 Answers1