Question about upsert a list of data solution [closed]

Solution for Question about upsert a list of data solution [closed]
is Given Below:

I have a Customer table with these fields: ID, name, age, phoneNumber, content, address.

with name+age+phoneNumber is unique.

I have an upsert POST API with request body:

[
   {
      "name": "A",
      "age": 100,
      "phoneNumber": 1000,
      "content": "content",
      "address": "address"
   }, ...
]

My requirement here check if name+age+phoneNumber existed, make a customer update. If not, do a customer insert.
And my solution is: loop the request list -> check DB based on name+age+phoneNumber to separate into 2 lists: insertList and updateList. After that using saveAll() on each list.

But if the list has 1000 record, I need to hit the DB 1000 times to separate. I think my solution is not good, so do you have another solution with better performance? I am using java 8 and oracle DB.

As I understand, you do not want to hit DB 1000 time to check whether you have to insert or update. One way is that, iterate through your request, collect all the name+age+phNumber in a list, now once this is done, hit db one time with the list and find out how many to insert and how many to update.

I don’t speak Java, but I do have a suggestion.

Create a new table in a database (having all those columns you mentioned – name, age, …) (let’s call it list_table) and store those 1000 records in there. As I said, I don’t know Java so I have no idea how much work it actually is, but I presume that it shouldn’t be too complex.

Once you’re done, switch to SQL and use a merge statement which will do the upsert of the target table in no time:

merge into target_table a
  using (select name, age, phonenumber, content, address
         from list_table
        ) b
  on (a.name        = b.name and
      a.age         = b.age  and
      a.phonenumber = b.phonenumber
     )
  when matched then update set
    a.content = b.content,
    a.address = b.address
  when not matched then insert (name, age, phonenumber, content, address)
    values (b.name, b.age, b.phonenumber, b.content, b.address);