JDBC SELECT COUNT(*) returns empty resultset on HSQLDB

Question

I would expect to always receive a resultset with one row on a SELECT COUNT, but results.next() always returns false. This is on HSQLDB 2.5.1. The code below prints:

number of columns: 1. First column C1 with type INTEGER

No COUNT results

           statement = connection.createStatement();
            // check if table empty
            statement.executeQuery("SELECT COUNT(*)  FROM mytable");
            ResultSet results = statement.getResultSet();
            System.out.println("number of columns: " + results.getMetaData().getColumnCount() + ". First column " +results.getMetaData().getColumnName(1) + " with type " +results.getMetaData().getColumnTypeName(1) );
            int numberOfRows = 0;
            boolean hasResults = results.next();
            if (hasResults){
                numberOfRows = results.getInt(1);
                System.out.println("Table size " + numberOfRows );
            }else{
                System.out.println("No COUNT results" );
            }
            statement.close();

Executing the same SQL statement in my SQL console works fine:

C1
104

Other JDBC actions on this database work fine as well. Is there something obvious I'm missing?

Answer 1

The getResultSet method is applicable to execute, but not executeQuery which returns a ResultSet. That is the one you need to refer to, at the moment you are losing it as you are not assigning it to anything.

See https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeQuery(java.lang.String) and https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getResultSet()

ResultSet results = statement.executeQuery("SELECT COUNT(*) FROM mytable");