Spring JPA repository update multiple entities with a single query-CodePudding

At the moment I have the following code which iterates a list of parameter entities and updates each of their names in the database:

public class test {

    @Autowired
    private ParameterJpaRepository parameterJpaRepository;

    public updateParameters(List<Parameter> parameters) {
       for (Parameter parameter : parameters) {
           parameterJpaRepository.setNameById(parameter.getId(), parameter.getName());
       }
    }
}

public interface ParameterJpaRepository extends JpaRepository<Parameter, Long> {

    @Modifying
    @Query("UPDATE Parameter p SET p.name = :name WHERE p.id = :id")
    void setNameById(@Param("id") long id, @Param("name") String name);
}

Obviously, this results in N queries:

Hibernate: update parameter set name=? where id=?
Hibernate: update parameter set name=? where id=?
Hibernate: update parameter set name=? where id=?
Hibernate: update parameter set name=? where id=?

I would like to combine then into a single query equivalent to this attempt:

public interface ParameterJpaRepository extends JpaRepository<Parameter, Long> {

    @Modifying
    @Query("UPDATE Parameter p SET p.name = (:names) WHERE p.id = (:ids)")
    void setNameById(@Param("ids") List<Long> ids, @Param("names") List<String> names);
}

Which should yield something like:

Hibernate: UPDATE parameter
           SET name = (case when id = ? then ?
                            when id = ? then ?
                            when id = ? then ?
                            when id = ? then ?
                       end)
           WHERE id in (?, ?, ?, ?);

Is this possible?

CodePudding user response：

You probably want something like this

@Modifying
@Query(value = "UPDATE Parameter p SET p.name = (CASE "  
               "WHEN p.id IN (:ids) THEN (CASE "  
               "WHEN p.id = :ids[0] THEN :names[0] "  
               "WHEN p.id = :ids[1] THEN :names[1] "  
               "WHEN p.id = :ids[2] THEN :names[2] "  
               // ...
               "ELSE p.name "  
               "END) "  
               "ELSE p.name "  
               "END) "  
               "WHERE p.id IN (:ids)", nativeQuery = true)
void setNameById(@Param("ids") List<Long> ids, @Param("names") List<String> names);

This is a bad approach. Don't try to do that.

It's very bad for large lists, since the query will become very long and very difficult to maintain. It does not work if the ids and names lists are not in the same order. If you need to update a big number of rows, or if the order of the ids and names lists is not fixed, you might want to consider using a different approach, such as executing a separate update statement for each row or using a temporary table .

CodePudding user response：

(Un)fortunately, spring-data-jpa functionality is not so flexible as you would like to see, however it does allow to create Custom Implementations for Spring Data Repositories and thus you may write any update query you want, some examples:

by the way, you need to keep in mind that in general that is not a good idea to update hibernate entities via direct update, the reasoning is following:

it is not cache friendly - if you are using second level cache, hibernate needs to completely cleanup those cache, cause it has no chance to get know what entities have been updated
if you are using auditing solution like envers, direct update bypasses that solution

so, sometimes it is much better to enable batch updates and write something like:

@Transactional
default void setNameById(List<Long> ids, List<String> names) {
    Map<Long, Parameter> data = StreamSupport.stream(findAllById(ids).spliterator(), false)
            .collect(Collectors.toMap(
                    Customer::getId,
                    Function.identity()
            ));
    
    for (int i = 0, n = ids.size(); i < n; i  ) {
        Parameter parameter = data.get(ids.get(i));
        if (parameter != null) {
            parameter.setName(names.get(i));
        }
    }

    saveAll(data.values());
}