Does a Table Exist in a Database? JDBC

How do you test whether a given table name exists in a database? For the sake of our discussion, let’s say that the table name is TABLE_NAME. At least four possible solutions exist:

Solution 1: Use the getTableNames() method as described earlier and then check to see if the list contains your desired table (e.g., TABLE_NAME).

Solution 2: Execute the following SQL statement; if execution is successful (meaning there was no SQLException) then the table exists; otherwise the table does not exist:

select * from TABLE_NAME where 1=0;

This solution is preferable to others. Because the boolean expression 1=0 evaluates to false, no rows will be selected. This expression only checks whether or not the table exists (in other words, if the table exists, then it returns no records and no exception is raised; otherwise it will throw a SQLException).

Solution 3: Execute the following SQL statement; if execution is successful (meaning there was no SQLException) then the table exists; otherwise the table does not exist:

select count(*) from TABLE_NAME;

This solution might require a full table scan (to obtain the number of rows or records), and therefore using this solution might be more expensive than the others.

Solution 4: You may use a database vendor’s catalog to find out if a given table exists (this will be a proprietary solution). Using an Oracle database, for example, you may execute the following SQL query:

select object_name from user_objects
where object_type = 'TABLE' and
object_name = 'YOUR-TABLE-NAME';

If this query returns any rows, then the table does exist; otherwise it does not. This solution is an optimal one for Oracle databases (but it’s not portable to other databases).

Solution 1: Use getTableNames()

Testing Solution 1

Output for Testing Solution 1

Testing Solution 2

Output for Testing Solution 2

-------- does table TestTable77 exist -------
true
---------------------------------------------
-------- does table TestTable88 exist -------
false
---------------------------------------------
Solution 3: Execute select count(*) from TABLE_NAME;

This solution will work for both MySQL and Oracle databases:

Testing Solution 3

Output for Testing Solution 3

-------- does table TestTable77 exist -------
true
---------------------------------------------
-------- does table TestTable88 exist -------
false
---------------------------------------------



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

JDBC Topics