5

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 ?

sweet dreams
  • 2,094
  • 13
  • 32
  • 46

4 Answers4

11

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

anttix
  • 7,709
  • 1
  • 24
  • 25
  • Then you are using wrong names. Just print out everything you get from the resultSet. Updated example to show how. – anttix Jul 30 '12 at 22:00
  • 1
    The debugging part of your answer will not work. It needs to be corrected. Put the name of the database that you want to search the table in as 1st parameter. ie, resultSet = metadata.getTables(TheDataBaseNameGoesHere, null, "%", null); – sweet dreams Jul 30 '12 at 22:25
  • @anttix- Thanks ! Btw, how did you find that link ? Apparently, its not a bug. – sweet dreams Jul 30 '12 at 22:52
  • It is a bug because JDBC spec says that if catalog is set to null, the driver must list tables in all catalogs. Thanx for pointing it out though. – anttix Jul 30 '12 at 23:19
1

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'
Samson
  • 2,801
  • 7
  • 37
  • 55
0

Use Connection.getMetaData(), and use the returned object to get the catalogs, schemas, tables and columns.

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
0
 Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
 DatabaseMetaData metadata = connection.getMetaData();
 ResultSet resultSet;
 resultSet = metadata.getTables(null, null, "tablename", null);
 if(resultSet!=null){
   // next() checks if the next table exists ...
      System.out.println("Table exists");
 }
Andre
  • 172
  • 2
  • 8