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

Question

I have a simple users database table with three columns:

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

I want to release an update through a POST request http://mysite/users/2/nationality

Now my initial approach was to make one request

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

The problem is that the id passed in the request may not be in my database. How to check if a user exists in a database?

• Should I just check if the query returns an object?
• Should I check the update for the affected rows first (the affected rows will be zero if no changes were made for the data to be updated, so I cannot throw a UserNotFoundException in this case)?
• Is it better to issue a query before updating to check if a row exists, then it is updated, then it asks for an 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

• If you always need to update to return the updated object in response, option 1 seems like a reasonable way to check if the update matches an existing user. Although, if you are not using transactions, you should be aware that the user may not exist during the upgrade, but a separate connection may insert the user before your choice.

  However, without transactions, there is always a chance that the selection will return an object in a different state from the update just completed. In this case, this is a little worse, because technically the update should be unsuccessful.

• If you do not need an update to return the updated object in response, options 2 look like a much better solution. However, for this you will need an update to return the number of matched rows, not the number of rows changed (therefore, if the update matches an existing user, but the field you are updating does not change, you still get a non-zero result).

  Usually you need to set the connection attribute to do this job for MySQL (for example, the PHP PDO driver has the MYSQL_ATTR_FOUND_ROWS attribute). However, I understand that this parameter is already included in JDBC , so executeUpdate should return the number of matching rows. I cannot confirm this at the moment, but it should be easy enough for you to check.

Answer 2

I would choose option 3 for reusability and sanity if you are not worried about a few additional requests (very strict and not obvious) for performance reasons.

Since you are most likely reusing the code to retrieve the user in other places, I would first take the user and return 404 if it was not found. Then I would call update and health check the number of rows. Finally, I would call an extraction method to get the user and send it to the response body. It is simple, works, reads, predictably, verifies, and most likely, fast enough. And you reused the search method.

Answer 3

The best approach in your case is to run a query of choice by this identifier to make sure that the corresponding record exists in your database. If the record exists, you can continue the success stream and start the update and the selected queries mentioned above. Otherwise, if the record does not exist, you can continue the flow of failures (throw exceptions, etc.).

Answer 4

I had a similar problem. This is how I solved the problem

• Whenever his new user, then mark the identifier with the default number (for example, 51002122), here 51002122 is never id in db. Therefore, the page displays "/ 51002122 / user". When ever the user id is 51002122, I would do an insert in db. After insertion, I make a page with id from db. For instance. after insertion, the page will be "/ 27 / user".

• For all other identifiers other than 51002122 (for example, / 12 / user or / 129 / user), I would do an update in db because I know that this user exists in db.

  / li>

Not sure if this is the right approach, but it works. Can someone say the best or the right approach.

Answer 5

I think the safest way:

 SELECT EXISTS( SELECT * FROM users WHERE id = 3 ) as columnCount; 

This will return the number of rows with id = 3. Then you can return this and check if columnCount is 1, and then execute the update statement something else.

Answer 6

Before proceeding with the solution, there are a few things to consider.

• This is a REST API call that requires simplicity in terms of use.
• Server-side code should also consider the performance implications of the chosen implementation.
• The API must be reliable . The point is that what can come, the request should always accept the flows (lucky / exception) conceived in the design.

Based on these considerations, I would suggest the following approach.

• In the DAO, define two different methods: updateRecord(Long id, String username) and getRecord(Long id) .
• Mark the transaction transaction attribute ( @Transaction ) as follows
  • Mark the transaction attribute for updateRecord as REQUIRED .
  • Mark the transaction attribute for getRecord as NOT_REQUIRED , since this is a pure read access.
• Please note that in all cases, at least a database call is required.
• From the controller, first call the updateRecord method. This method will return an integer.
• If the return value is nonzero, call getRecord to retrieve the updated record from the database.
• If the return value is zero, this means that the user does not exist and you do not need to call getRecord . Corresponding error response (404 Not Found) to the returned client.
• In this approach, you save one database call when the user does not exist.

In general, this approach is neat, less cluttered, and, most importantly, simple and effective (we restrict the transaction boundary only for invoking updates). Moreover, getRecord can be used independently as another API to retrieve a record (without a transaction).

Answer 7

I had a similar problem when I had to update the table, but before that I need to check if the identifier exists. I used openjpa and wrote the verifyUser (id) method, where id was the one I need to check. OpenJpa findById returns u record. When updating, the full record is returned, while when adding, the new primary key is returned, according to which the record is added. I'm not sure how this works for hibernation, but there are many similarities in jpa n hibernate.