0

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.

0 Answers0