GHOST-34 GHOST-34 - 3 months ago 23
Java Question

Empty ResultSet still evaluting 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?

Answer

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