CODEWITHSUNDEEP
javasqljavadb
Usman Riaz
Usman Riaz

Reputation: 3020

Check if a table exists using JDBC

I have a table named test2 in my database. now i want to check if it exists in java code. i have written these lines of code to check if the table exists or not.

DatabaseMetaData dbm = conn.getMetaData();
             ResultSet rs = dbm.getTables(null, "APP", "test2", null);
             if (!rs.next()) {
                 PreparedStatement create = conn.prepareStatement("create table test2(name2 varchar(33))");
                 create.executeUpdate();
             }else{
                 System.out.println("already exists");
             }

As test2 exists in APP schema, my else should get executed. but in my case if is getting executed.

Upvotes: 2

Views: 9841

Answers (1)

user330315
user330315

Reputation:

Derby stores table names in upper case (as required by the SQL standard and done by many other DBMS as well)

You need to pass the table name in upper case as well

ResultSet rs = dbm.getTables(null, "APP", "TEST2", null);

The driver (all drivers that I know of) runs something along the lines:

where table_name like 'TEST2'

to retrieve the list of tables. Where TEST2 is the unaltered value from the call to getTables(). If you have a DBMS that does case-sensitive string comparison (which almost all DBMS do - I think MySQL and SQL server are the exception) then obviously the table name must be passed in the same case as it was stored.

However you can create mixed case table names by quoting them: create table "FooBar" (...) In that case you would need to pass "FooBar" to getTables().

Upvotes: 1

Related Questions