Spring Data Repository @Query - Update and Return Modified Entity

Spring JPA Update and Return the Updated Data

As you have discovered, just changing the return type of your method doesn't cause the auto-generated JPA method to return a different type.

The problem is that you are doing a bulk update operation. This is going to get turned into an equivalent SQL "update...where" statement at execution time, and the only data that is going to be returned by JDBC is the number of rows updated. (And BTW, in that case, you should return a long, not an int).

So, you have to devise some other way of doing this.
Off-hand, I can think of two ways:

1. If you can be pretty certain that you will always be updating a relatively small number of rows (say, a up to a few hundred), then you could create a method that selects the Entity instances that you want to update, e.g.

   @Query("SELECT u from User"
   + " WHERE u.isActive = false AND u.reactivateTime <= currentTime"
   + " AND u.reactivateTime IS NOT null")
   List getUsersToReactivate(@Param("currentTime") ZonedDateTime currentTime);

And then, in a concrete implementation of the JPA Repository interface,

• Call the method to retrieve the entities.
• Update the entities via java code
• Save the entities

For example (I just typed this, didn't try it - syntax errors are your responsibility):

@Transactional
@Modifying
List<User> reactivateUsers(ZonedDateTime currentTime) {
   List<User> users = getUsersToReactivate(currentTime);
   users.forEach(user -> {
       user.setActive(true);
       save(user);
   ); 
   return users; 
}

2. This may be more intrusive, but you could add a Date property to the User class called 'lastUpdated', get the current date, update the isActive flag to true and the 'lastUpdated' property to the current date, and then select entities where the lastUpdate property is the current date.

Update by JPA native query

If you would like to determine whether any rows were updated, the updateMobileVerified method can be defined to return int instead of void to indicate the number of rows updated

JpaRepository findBy query (SELECT) is updating records in the DB

Is it because entity is being modified in .stream().map(v)?

Yes, this is arguably the defining feature of JPA: You load entities, you modify them and at the end of the transaction (actually the session but that's mostly the same) JPA gathers all modifications from managed entities and updates their states in the database.

JPA entity update via getById & save didn't updated it's updatedtimestamp column

ON UPDATE CURRENT_TIMESTAMP is a construct of MySql's SQL dialect. It is described as follows in the documentation:

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

Since all JPA implementation update all columns no matter, if they changed or not they will also provide a value for your CURRENT_TIMESTAMP column thus preventing any automatic update as described in the documentation.

You have various ways to get an update timestamp with this technology stack:

1. You can use dynamic inserts to only update fields that changed. Note that in most cases this is bad for performance. See Hibernate: Dirty Checking and Only Update of Dirty Attributes?

2. You can make the column read only by using @Column(updatable=false). See JPA readonly mapping

3. Instead of using the MySql feature you may use the Hibernates UpdateTimestamp for this purpose

4. Or you use JPAs EntityListener.

5. And since you seem to use Spring Data JPA you may also use Spring Datas @LastModifiedDate.

---

Related Topics