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
– Kayaman, Commented May 10, 2016 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()?) — Alex Poole
– Alex Poole, Commented May 10, 2016 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
– Luke Sykpe, Commented May 10, 2016 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
– krokodilko, Commented May 10, 2016 at 16:27
See stackoverflow.com/a/2942795/984823 the better database independent answer with full code. — Joop Eggen
– Joop Eggen, Commented May 10, 2016 at 16:38

Bajal · Accepted Answer · 2016-05-10 16:34:20Z

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, 2016 at 16:34

Bajal

6,1563 gold badges22 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Sykpe Over a year ago

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.

Collectives™ on Stack Overflow

SQL Query returns different results in java and when ran

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related