This short Spring Data JPA tutorial will teach you 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 an SQL query, so I will show you how to create a Dynamic SQL Query as well.
To learn how to perform the SELECT query, read the following tutorial: “Spring Data JPA Native SQL Query“.
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. Suppose you have a service interface and a service interface implementation class. In that case, it is better to use @Transactional there and annotate a method that calls the above updateUser() method on the UserRepository interface.
If you do not have a service class, you can use @Transactional with the @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 a Native SQL query with Positional Parameters, we need to use “?1” or “?2” instead of parameter names. For example, below is an updated query 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 preparing Native SQL UPDATE query was helpful to you.
If you want 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.
