JDBC/Java - How to check if a table and also a column exist in a database?

Question

I am using MySql, jdbc and java for my code. I want the code to check if:

1- A table exists in a particular database. 2- A column exists in a particular table of a particular database.

Can you tell me how to do this ?

Answer 1

A correct way is to use JDBC MetaData

Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();
ResultSet resultSet;
resultSet = metadata.getTables(null, null, "tablename", null);
if(resultSet.next())
    // Table exists

resultSet = metadata.getColumns(null, null, "tablename", "columnName");
if(resultSet.next())
    // Column exists

To debug your code it might be a good idea to try to fetch all table names first and print them out like this:

resultSet = metadata.getTables(null, null, "%", null);
while(resultSet.next())
    System.out.println(resultSet.getString("TABLE_NAME"));

NB! If no tables are listed, you are using an older version of MySQL JDBC driver with the following bug http://bugs.mysql.com/bug.php?id=20913 you should either upgrade or use database name as the first argument to getTables

Answer 2

Look for the table:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = 'db_name' 
AND table_name = 'table_name';

and if it exists then look for the column:

SELECT * 
FROM information_schema.COLUMNS 
WHERE 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'