Luke Sykpe Luke Sykpe - 6 months ago 22
Java Question

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

Answer

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.