[FIXED] Spring JPA - read a list of entities (with only partial properties) and update value without SELECTING the whole object from database?

Issue

I'm using Spring Boot 1.5.2. I have a big Entity with multiple properties and multiple OneToMany relations, for example:

@Entity
@Table(name = "person")
class Person {
   @Id
   protected long id;

   private String property1;
   private String property2;
   private String property3;
   private String property4;
   private String property5;
   private String property6;

   @OneToMany
   private List<Obj1> obj1List;
   @OneToMany
   private List<Obj2> obj2List;
   @OneToMany
   private List<Obj3> obj3List;
   @OneToMany
   private List<Obj4> obj4List;


}

How can I read the list of Person from database, but only with 2 properties id and property2, and update property2=0.

Then, I can use JPA CrudRepository to save():

public interface PersonRepository extends CrudRepository<Person, Long> {

}

for (Person person : personList) {
   this.personRepository.save(person)
}

I don't want to use findAll() from CrudRepository which enables Hibernate to SELECT the whole list of Person with a big SQL query before saving to database.

Solution

Inside your PersonRepository interface, you should be able to add a Query like this:

@Query("select new Person(id, property2) from Person")
List<Person> findIdAndProperty2();

The other fields should come back null as they haven't been specified in your query. You'll just need to add a constructor to Person with id and property2 as arguments.

For updating, you can use similar syntax...

@Modifying
@Query("update Person set property2 = ?1 where id in ?2")
int updateProperty2(String property2, List<Long> ids);

com.google.common.collect.Iterables.partition can be used to process your updates in chunks. For instance...

for (List<Long> curUpdateIds : Iterables.partition(ids, 1000)) {
    personRepository.updateProperty2("0", curUpdateIds);
}

Answered By - Zack Macomber