SQL Query returns different results in java and when ran

Question

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

You can use `DatabaseMetaData` to get information about the schema (including table names). You can't get a count from a non-existent table, so the proposed solution is complete rubbish. — Kayaman, May 10 '16 at 16:18
Are you sure you're connected to the same database as the same user, or at least looking at the same schema? Have you recently created and populated the table, and if so, have you committed the inserts? (Also, why are you looking at `getCause()` rather than, say, [`getErrorCode()`](https://docs.oracle.com/javase/7/docs/api/java/sql/SQLException.html#getErrorCode())?) — Alex Poole, May 10 '16 at 16:25
Thank you for your answer. As I said, I'm a complete newbie to this. You're going to have to elaborate, how do I find out if a table exists in DatabaseMetaData? — Luke Sykpe, May 10 '16 at 16:26
On Oracle you can use a dictionary view to check if a table exists -SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='CITIES' — krokodilko, May 10 '16 at 16:27
I am a complete moron. Of course, I forgot that I had to commit the changes. I got it confused with the java queries, where it automatically commits. Thank you. Still, the issue that this is not an appropriate solution to the "table exists" problem persists. At least isEmpty works. I'll update the original question. — Luke Sykpe, May 10 '16 at 16:29
I did not actually think to use getErrorCode(). Would that be a valid method of checking for the existence of a table though, if I can actually make it work? — Luke Sykpe, May 10 '16 at 16:34
See http://stackoverflow.com/a/2942795/984823 the better database independent answer with full code. — Joop Eggen, May 10 '16 at 16:38

score 1 · Accepted Answer · answered May 10 '16 at 16:34

1

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.

answered May 10 '16 at 16:34

Bajal

5,487
3
20
25

The real "objective" here, is to get whether the database has data or not. Problem is, that is the same database I use in class, so it always has data in it. To circumvent that, I am only checking for the tables used by the application. That said, I can just run 2 checks, both for tableExists and for isEmpty. – Luke Sykpe May 10 '16 at 16:36

SQL Query returns different results in java and when ran

1 Answers1