Search code examples
javamysqljdbcresultset

Empty ResultSet still evaluating to true, JDBC

I wrote a simple check method to check whether data exists in a MySQL db for a given string id:

    public boolean checkForData(String etfSymbol){
    try (Connection conn = DriverManager.getConnection(DBURL, USERNAME, PASSWORD)) {
        if (conn != null) {
            PreparedStatement statement = conn.prepareStatement(CHECK_FOR_DATA);
            statement.setString(1, etfSymbol);
            ResultSet result = statement.executeQuery();
            if (!result.isBeforeFirst()){
                System.out.println("No data currently in database for this ETF");
                return false;
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    System.out.println("Data already exists in database for this ETF");
    return true;
}

Query:

    final String CHECK_FOR_DATA = "SELECT COUNT(*) FROM basic_etf_data WHERE etf_name = (?)"

I can confirm with MySQL workbench that no data is in the table for the id that I am requesting (no data at all actually) yet the condition is evaluating to true. Here's the debug output for the ResultSet:

first-half second-half

At first, "InvalidRowReason" led me to believe that it was evaluating to true because the index is in fact before the first, but the docs for isBeforeFirst() state:

true if the cursor is before the first row; false if the cursor is at any other position or the result set contains no rows

I then expanded the rowData and found that the size was equal to 1:

row data

I have no idea how this can be as I can confirm that no rows or even any data exist in the table. What am I missing?

Solution

  • Because you are making a COUNT in SQL and COUNT has always a result(a row), in your case 0. Anyway, I probably would write your query as

    SELECT * FROM basic_etf_data WHERE etf_name = ? LIMIT 1