In our service, we are currently using a native PostgreSQL query to UPSERT data into our table. We are trying to get rid of a for loop used in our service in order to improve performance, but are having trouble passing a list to our native query.
Here is our service layer code:
for (UserData userData: userDataList) {
userDataRepository.saveUserData(userData);
}
This is how our native query handles the data:
@Transactional
@Modifying
@Query(value = "INSERT INTO user_table (user_id, user_name, user_data) " +
"VALUES(:#{#userData.user_id}, :#{#userData.user_name}, cast(:#{#userData.user_data} as jsonb) " +
"ON CONFLICT(user_id) DO UPDATE SET user_name= :#{#userData.user_name}, user_data= cast(:#{#userData.user_data} as jsonb)",
nativeQuery = true)
public void saveUserData(@Param("userData") UserData userData);
We would like for our service layer to work like:
userDataRepository.saveUserData(userDataList);
and for our native query method definition to look like:
public void saveUserData(@Param("userData") List<UserData> userDataList);
But we are unsure how to refactor the query to work with this, or if it is even possible.