How to validate if a record exists when issuing a REST update request using spring jdbcTemplate?

Question

I have a simple database table users with 3 columns:

| id | username | nationality | 
|  1 |   John   | American    | 
|  2 |   Doe    | English     |

I want to issue an update via a POST request to http://mysite/users/2/nationality

Now my initial approach was to do a single query

UPDATE users SET nationality="French" WHERE id=2; followed by a query for the updated object SELECT * FROM users WHERE id=2; then return the updated object in the response.

The problem is the id passed in the request may not exist in my database. How should I validate if a user exists in the database?

1. Should I just check if the query returns an object?
2. Should I validate the update first for the affected rows (affected rows will be zero if the no change was made to the data to be updated so I can't throw a UserNotFoundException in that case)?
3. Is it better to issue a query before the update just to check if the row exists then update then query the updated row?

  public void updateRecord(Long id, String username) {  
 String updateSql = "UPDATE users SET username = ? WHERE id = ?";  
 JdbcTemplate template = new JdbcTemplate(dataSource);  
   Object[] params = { username, id};  
    int[] types = {Types.VARCHAR, Types.BIGINT};  
    int rows = template.update(updateSql, params, types);  
    System.out.println(rows + " row(s) updated.");  
    }

Answer 1

1. If you always need the update to return the updated object in the response, then option 1 seems like a reasonable way to check if the update matched an existing user. Although if you aren't using transactions, you should be aware that the user may not exist at the time of the update, but a separate connection could insert the user before your select.

   That said, without transactions there is always a chance that the select will return the object in a different state from the update you just performed. It is slightly worse in this case, though, because technically the update should have failed.

2. If you don't need the update to return the updated object in the response, then options 2 seems like a much better solution. For this to work, though, you need the update to return the number of matched rows rather than the number of changed rows (so if the update matches an existing user, but the field you are updating doesn't change, you'll still get a non-zero result).

   Usually you would have to set a connection attribute to make this work for MySQL (for example, in PHP's PDO driver there is the MYSQL_ATTR_FOUND_ROWS attribute). However, my understanding is that this option is already enabled in JDBC so executeUpdate should return the number of matched rows. I can't confirm that at the moment, but it should be easy enough for you to test.

Answer 2

Best approach in your case is to run a select query against given id in order to verify that a corresponding record exists in your database. If record exists, then you can proceed with success flow and run the update and the select queries you mentioned above. Otherwise, if record does not exists, then you can proceed with the failure flow (throw exceptions etc.)