如何使用JDBC检查数据库表是否存在

1. Introduction

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

2. Using 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:

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:

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

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:

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”.

4. Check if Table Exists With 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).

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:

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:

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

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

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