Spring Data JPA Native UPDATE SQL Query

In this short Spring Data JPA tutorial, you will learn how to write a Native UPDATE SQL query.

Let’s assume you need to run the following SQL query:

update users u set u.EMAIL_VERIFICATION_STATUS = 'true' where u.USER_ID = '5JwQBHd1mL73XEg8S2e2w4ITn9gOW4'

I assume you will not want to hardcode the values of EMAIL_VERIFICATION_STATUS and USER_ID into SQL query so I will show you how to create a Dynamic SQL Query as well.

Native UPDATE SQL Query with Named Parameters

Let’s assume we have the following Spring Data JPA Repository with one query method which updates the user details.

package com.appsdeveloperblog.tutorial.jpa.jpatutorial.io;

import com.appsdeveloperblog.tutorial.jpa.jpatutorial.io.entity.UserEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface UserRepository extends CrudRepository<UserEntity, Long> {

    @Transactional
    @Modifying
    @Query(value = "UPDATE Users u set EMAIL_VERIFICATION_STATUS =:emailVerificationStatus where u.USER_ID = :userId",
            nativeQuery = true)
void updateUser(@Param("emailVerificationStatus") boolean emailVerificationStatus, @Param("userId") String userId);

}

Please note that the above query method has a Native SQL Query which starts with @Query annotation:

@Modifying
@Query(value = "UPDATE Users u set EMAIL_VERIFICATION_STATUS =:emailVerificationStatus where u.USER_ID = :userId",
           nativeQuery = true)

Please also note that the @Query is also annotated with @Modifying. 

I have also annotated the query with @Transactional annotation but this is only because I do not demonstrate the Service layer code here. If you have a service interface and a service interface implementation class then it is better to use @Transactional there an annotate with it a method which calls the above updateUser() method on the UserRepository interface. If you do not have a service class then you can use @Transactional together with @Modifying annotation above the @Query annotation.

To call the above updateUser() method and execute the native SQL Query with two named parameters the values of which will be injected into the query, you can use the following code:

userRepository.updateUser(true, "5JwQBHd1mL73XEg8S2e2w4ITn9gOW4");

where:

  • true” is the value for emailVerificationStatus,
  • and 5JwQBHd1mL73XEg8S2e2w4ITn9gOW4 is the value for userId parameter.

Native UPDATE SQL Query with Index or Positional Parameters

To prepare Native SQL query with Positional Parameters we need to use “?1” or “?2” instead of parameter names. For example, below is an updated query but this time with positional parameters.

    @Transactional
    @Modifying
    @Query(value = "UPDATE Users u set EMAIL_VERIFICATION_STATUS =?1 where u.USER_ID = ?2",
            nativeQuery = true)
void updateUser(boolean emailVerificationStatus, String userId);

when you call the updateUser() method this way:

userRepository.updateUser(true, "5JwQBHd1mL73XEg8S2e2w4ITn9gOW4");

the value of “true” will be used to replace the parameter at position “?1” and the value of “5JwQBHd1mL73XEg8S2e2w4ITn9gOW4” will be used to replace the userId parameter at position “?2”

I hope this short Spring Data JPA tutorial on how to prepare Native SQL UPDATE query was helpful to you.

If you are interested to learn more about building RESTful Web Services with Spring Boot, Spring MVC and Spring Data JPA, check my other tutorials on this page: RESTful Web Services with Spring MVC.

If you prefer to learn by following step by step video lessons then below is a list of video courses that teach Spring Data JPA.