CODEWITHSUNDEEP
javaoracle-databaseoracle-sqldeveloper
Luke Sykpe
Luke Sykpe

Reputation: 311

SQL Query returns different results in java and when ran

I am trying to write a method to check whether or not a table exists in my database, in java. After some looking around, I found an answer stating I need to run SELECT COUNT(*) FROM tableName; to do so, so I wrote the following code. 

public static boolean isEmpty(Connection con) throws SQLException, ClassNotFoundException{
        PreparedStatement stm = con.prepareStatement("SELECT COUNT(*) FROM  Cities");
        String[] tables = {"Cities", "Connections"};
        ResultSet rs = null;
        //for(String table : tables){
           //stm.setString(1, "Cities");
           rs = stm.executeQuery();
           rs.next();
           System.out.println(rs.getInt(1));
           if(rs.getInt(1) != 0){
               return false;
           }
        //}
        return true;
    }

Notes:I am using oracle sql. Also, shouldn't this query return 0 if a table does not actually exist? I get an exception in that case, and my understanding was that it returns 0 when the table either does not exist or is empty.

Another question I would like to ask, albeit only informatively: Before searching around for a solution to the table exists problem, I thought of running a SELECT * FROM tableName query, and handling the "table does not exist" error SQL would throw me as an exception. What "crushed" my plan was that SQLexception.getCause(); returns null, instead of the actual cause, that I imagine would be "table or view does not exist", or something similar. Despite that, would that be a valid way to check for existence? That said, I am open to suggestions other than the SELECT COUNT(*) FROM tableName method that would work for what I want to do, if this is incorrect/ineffective.

Thanks in advance, LukeSykpe

Upvotes: 1

Views: 1027

Answers (1)

Bajal
Bajal

Reputation: 5986

Something like this should work for Oracle:

 public static boolean doesTableExist(Connection con, String tableName) throws SQLException {

    ResultSet rs = null;

    PreparedStatement stm = con.prepareStatement("SELECT * FROM user_objects WHERE object_type = 'TABLE' and object_name=?");
    stm.setString(1,tableName.toUpperCase()); //case sensitive
    rs = stm.executeQuery();
    if(rs.next()) {
        return true;
      }
    return false;
}

Note: This would return true if the table exists, irrespective of whether it is empty or not.

Upvotes: 1

Related Questions