Java code is running select and update query for every data

Question

I am writing a Java code in Spring JPA which can add or update a data when saveData function is called. what I am trying to do is that if data is new it gets added as a new record in the database or else it will be updated.

public void saveData(List<StudentTable> studentTableList){  

    List< StudentTable > data= new ArrayList<>();

    for(StudentTable dt: studentTableList){
        if(dt.getStudentId() ==null) {
            data.add(dt);

        }else{
            studentRepository.findById(dt.getStudentId()).map(
                    student->{
                        student.setFirstName(dt.getFirstName());
                        student.setLastName(dt.getLastName ());
                        student.setPhone(dt.getPhone());
                        student.setAddress(dt.getAddress());

                        return data.add(student);
                    });
        }
        studentRepository.saveAll(data);
        data.clear();
    }
}

While this code is working fine I see performance issues as my entries grow. I see that update and select queries are run for each row of the table which is slowing down the performance.

I want to know if there is any way to run queries only for those rows which are updated or added on a single post request or to improve the performance?

score 0 · Answer 1 · answered Dec 23 '21 at 15:55

What about 2 blocks of code for each case?

Student student = studentRepository.findById(dt.getStudentId());
if(student == null){
  Student newStudent = new Student();
  //add data
  newStudent.save();
} else {
  student.setFirstName(dt.getFirstName());
  student.setLastName(dt.getLastName ());
  student.setPhone(dt.getPhone());
  student.setAddress(dt.getAddress());
  student.update();
}

score 0 · Answer 2 · answered Dec 23 '21 at 16:49

You can follow what @fladdimir mentioned. Or, here is my suggestion[will not provide any code, leaving up to you]:

filter objects which are old ones[contains id as I can see from your code] and news ones in two separate lists
List all ids
use id <--> object as keyValue in a map
Query all the objects with idList you got using IN clause
Now, set value as you are doing in your 2nd code block from the map, add them in a list
merge this list with new objectList
save them altogether with saveAll(your_merged_list)

I am not saying this is the optimized way, but at least you can reduce load on your db.

Java code is running select and update query for every data

2 Answers2