1. Introduction

1.绪论

In this tutorial, we’ll look at how we can check if a table exists in the database using JDBC and pure SQL.

在本教程中，我们将看看如何使用JDBC和纯SQL检查数据库中是否存在一个表。

2. Using DatabaseMetaData

2.使用DatabaseMetaData

JDBC gives us tools to read and write data to the database. Besides actual data stored in tables, we can read metadata describing the database. To do that, we’ll use the DatabaseMetaData object that we can obtain from the JDBC connection:

JDBC为我们提供了向数据库读取和写入数据的工具。除了存储在表中的实际数据，我们还可以读取描述数据库的元数据。要做到这一点，我们将使用DatabaseMetaData对象，我们可以从JDBC连接中获得。

DatabaseMetaData databaseMetaData = connection.getMetaData();

DatabaseMetaData provides a lot of informative methods, but we will need only one: getTables. Let’s use it to print all available tables:

DatabaseMetaData提供了很多信息量大的方法，但我们只需要一个。getTables。让我们用它来打印所有可用的表：。

ResultSet resultSet = databaseMetaData.getTables(null, null, null, new String[] {"TABLE"});

while (resultSet.next()) {
    String name = resultSet.getString("TABLE_NAME");
    String schema = resultSet.getString("TABLE_SCHEM");
    System.out.println(name + " on schema " + schema);
}

Because we didn’t provide the first three parameters, we got all tables in all catalogs and schemas. We could also narrow our query to, for example, only one schema:

因为我们没有提供前三个参数，我们得到了所有目录和模式中的所有表。我们也可以把查询范围缩小到，例如，只有一个模式。

ResultSet resultSet = databaseMetaData.getTables(null, "PUBLIC", null, new String[] {"TABLE"});

3. Checking if Table Exists With DatabaseMetaData

3.用DatabaseMetaData检查表是否存在

If we want to check if a table exists, we don’t need to iterate over the result set. We only need to check if the result set isn’t empty. Let’s first create an “EMPLOYEE” table:

如果我们想检查一个表是否存在，我们不需要对结果集进行迭代。我们只需要检查结果集是否为空。让我们首先创建一个 “EMPLAYEE “表。

connection.createStatement().executeUpdate("create table EMPLOYEE (id int primary key auto_increment, name VARCHAR(255))");

Now we can use the metadata object to assert that the table we just created actually exists:

现在我们可以使用元数据对象来断言我们刚刚创建的表确实存在。

boolean tableExists(Connection connection, String tableName) throws SQLException {
    DatabaseMetaData meta = connection.getMetaData();
    ResultSet resultSet = meta.getTables(null, null, tableName, new String[] {"TABLE"});

    return resultSet.next();
}

Mind that while SQL isn’t case-sensitive, the implementation of the getTables method is. Even if we define a table with lowercase letters, it will be stored in uppercase. Because of that, the getTables method will operate on uppercase table names, so we need to use “EMPLOYEE” and not “employee”.

请注意，虽然SQL不区分大小写，但getTables方法的实现是区分大小写的。即使我们用小写字母定义一个表，它也会被存储为大写字母。正因为如此，getTables方法将对大写的表名进行操作，所以我们需要使用 “EMPLAYEE “而不是 “employee”。

4. Check if Table Exists With SQL

4.用SQL检查表是否存在

While DatabaseMetaData is convenient, we may need to use pure SQL to achieve the same goal. To do so, we need to take a look at the “tables” table located in schema “information_schema“. It’s a part of the SQL-92 standard, and it’s implemented by most major database engines (with the notable exception of Oracle).

虽然DatabaseMetaData很方便，但我们可能需要使用纯SQL来实现同样的目标。为此，我们需要看看位于模式”information_schema“中的”tables“表。它是SQL-92标准的一部分，它被大多数主要数据库引擎所实施（Oracle是明显的例外）。

Let’s query the “tables” table and count how many results are fetched. We expect one if the table exists and zero if it doesn’t:

让我们查询”tables“表，并计算获取的结果有多少。如果该表存在，我们期望得到一个结果，如果不存在，则期望得到零。

SELECT count(*) FROM information_schema.tables
WHERE table_name = 'EMPLOYEE' 
LIMIT 1;

Using it with JDBC is a matter of creating a simple prepared statement and then checking if the resulting count isn’t equal to zero:

在JDBC中使用它，只需要创建一个简单的prepared statement，然后检查产生的计数是否不等于零。

static boolean tableExistsSQL(Connection connection, String tableName) throws SQLException {
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT count(*) "
      + "FROM information_schema.tables "
      + "WHERE table_name = ?"
      + "LIMIT 1;");
    preparedStatement.setString(1, tableName);

    ResultSet resultSet = preparedStatement.executeQuery();
    resultSet.next();
    return resultSet.getInt(1) != 0;
}

5. Conclusion

5.总结

In this tutorial, we learned how to find information about table existence in the database. We used both JDBC’s DatabaseMetaData and pure SQL.

在本教程中，我们学习了如何在数据库中查找有关表存在的信息。我们同时使用了JDBC的DatabaseMetaData和纯SQL。

As usual, all the code examples are available over on GitHub.

像往常一样，所有的代码实例都可以在GitHub上找到。