Why Does Execute() Return True On An Empty Table?

Through the following snippet, I am trying to run a query that either updates the data or inserts a new data into the table named JustPinged. The table contains a column named Node

Solution 1:

Your searchQuery will return ResultSet. hence the execute method returns 'true'. Try using executeQuery instead.

So your code would become:

String searchQuery = "select NodesThatJustPinged from JustPinged where NodesThatJustPinged = '" + nodeInfo + "'";
    Statement searchToEliminateDuplicates = connection.createStatement();
    ResultSet duplicateExists = searchToEliminateDuplicates.executeQuery(searchQuery);

    if(duplicateExists.next()) {
        // update the LastPingedAt column in the JustPinged table 
        String updateQuery = "update JustPinged set LastPingedAt='" + pingedAt + "' where NodesThatJustPinged = '" + nodeInfo + "'";
        PreparedStatement updateStatement = connection.prepareStatement(updateQuery);
        updateStatement.executeUpdate();System.out.println("If statement");
    } else {
        // make a new entry into the database
        String newInsertionQuery = "insert into JustPinged values('" + nodeInfo + "','" + pingedAt + "')";
        PreparedStatement insertionStatement = connection.prepareStatement(newInsertionQuery);
        insertionStatement.executeUpdate();System.out.println("else statement");              
      }

P.S. If you are using PreparedStatement, then use parameters in your query and call ps.setString etc.

PPS. Don't use execute() method. Use executeQuery or executeUpdate. execute() is used where you don't know in advance whether your query is INSERT or UPDATE.

PPPS Close your resultset and statements as soon as you are done with them.

PPPPS A more better approach is to use count aggregate function in your SQL statement i.e.

select count(NodesThatJustPinged) from JustPinged where NodesThatJustPinged = '" + nodeInfo + "'";

Now you can check whether count is 0 or greater than 1 and branch your code accordingly.

Solution 2:

A SELECT statement that returns zero rows will still return a ResultSet -- just one that immediately returns false when calling next(). You need to check the number of rows in the returned ResultSet.