Suhail Gupta Suhail Gupta - 2 months ago 8
SQL Question

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
NodesThatJustPinged
and
LastPingedAt
. If there is already a node in
NodesThatJustPinged
then the time in milliseconds in
LastPingedAt
is updated. Otherwise a new
node
information is inserted.

The problem is, that the following snippet is unable to insert the data into the database's table. The reason is the statement:

boolean duplicateExists = searchToEliminateDuplicates.execute();


returns
true
to start with. (Initially the table is empty) Why does this statement return true? According to the documentation it returns true if the first result is a ResultSet object; false if the first result is an update count or there is no result. So here the boolean should contain a false value. But it contains a
true
value and thus the
if
statement always works. (And in
if
section,update query works when there is nothing to update !)

String searchQuery = "select NodesThatJustPinged from JustPinged where NodesThatJustPinged = '" + nodeInfo + "'";
PreparedStatement searchToEliminateDuplicates = connection.prepareStatement(searchQuery);
boolean duplicateExists = searchToEliminateDuplicates.execute();

if(duplicateExists) {
// 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");
}


So how should I edit the code, so that duplicate values are updated and new values are inserted?

Answer

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.