Check if database table exists with JDBC
JDBC (Java Database Connectivity) is a Java API that enables Java applications to connect to and interact with databases.
JDBC provides a standard set of Java classes and interfaces that enable Java programs to access relational databases.
In this article, we will understand how to check if a database table exists using JDBC. We will be using MySQL database for our examples, but the same techniques should work with other databases as well.
Checking if a database table exists is important for two main reasons.
1. It can help to prevent errors when accessing the database. If a table doesn’t exist, then trying to access it will usually result in an error.
2. It can be used as a way of checking if the data in the database has been updated. For example, if we are creating a table dynamically using a query, then we can test the result.
There are following two ways to check if a database table exists with JDBC.
1. Executing query
To get the list of tables in a database, following query may be used.
SHOW TABLES LIKE ‘TABLE NAME’
We can execute this query using JDBC statement object using its executeQuery() method.
executeQuery() returns a ResultSet, which can be iterated with next() method as shown below.
Class.forName("com.mysql.cj.jdbc.Driver");
// get connection
Connection connection = DriverManager.
getConnection("jdbc:mysql://localhost/mydb", "user", "pass");
// create statement
PreparedStatement statement = connection.
prepareStatement("SHOW TABLES LIKE 'userdetail'");
// execute query
ResultSet resultSet = statement.executeQuery();
boolean isExists = false;
while(resultSet.next()) {
isExists = true;
} 2. Using DatabaseMetaData
java.sql.DatabaseMetaData provides information about the database such as its URL, user name used to connect to it, list of tables, columns in each table etc.
An object of DatabaseMetaData can be retrieved using getMetaData() method of Connection.
Once we have its object, use its getTables() method to get the list of tables in this database.
getTables() accepts multiple arguments which may be used to narrow down the search of tables.
First argument is the name of the catalog. For this example, we will be setting it to null.
Second parameter is the schema name, inside which it should find the tables.
Third parameter is the table name that it should search for. If you want to find all tables in the schema, then leave it null.
Fourth parameter stands for table types. There are multiple table types in a schema as given below
TABLE: a table VIEW: a view SYSTEM TABLE: a system table GLOBAL TEMPORARY: a global temporary table LOCAL TEMPORARY: a local temporary table ALIAS: an alias for a table SYNONYM: a synonym for a table
To get only tables, provide a string array with a single value “table”.
getTables() will return a ResultSet, containing the list of all tables. Since, we have also provided the table name as third argument to getTables(), the result set will contain only one record.
If there is a single record in this result set, then table exists in the database.
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.
getConnection("jdbc:mysql://localhost/mydb", "user", "pass");
DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData.
getTables(null, "mydb", "userdetail", new String[] {"table"});
boolean isExists = false;
while(resultSet.next()) {
isExists = true;
} 