Java jdbc检查表是否存在

Question

我正在检查我创建的表是否已经存在。不知何故，它总是进入if（好像这个表不存在）。它会删除当前表并创建一个新表。我想让它做的是，如果桌子已经在那里，它不应该掉桌子

 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();
        }
    }

Answer 1

取自this SO question，您可以定义一个助手方法，该方法可以检查表的存在：

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;
}

请注意，DatabaseMetaData#getTables()的第三个参数实际上是一个模式，因此可能会在结果集中找到并返回与之匹配的多个表名。因此，我们迭代整个结果集，并根据传递给helper方法的确切表名进行相等性检查