Bulk update over spring data jpa with join

Question

I'm having troubles doing a bulk update (JPA 2.1) with spring-data-jpa.

According to docs:

5.3.8. Modifying queries All the sections above describe how to declare queries to access a given entity or collection of entities. Of course you can add custom modifying behaviour by using facilities described in Custom implementations for Spring Data repositories. As this approach is feasible for comprehensive custom functionality, you can achieve the execution of modifying queries that actually only need parameter binding by annotating the query method with @Modifying:

@Modifying
@Query("update User u set u.firstname = ?1 where u.lastname = ?2")
int setFixedFirstnameFor(String firstname, String lastname);

But the entity I need update has a @ManyToOne relationship with another entity.

@Entity
@Table(name = "usuarios", indexes = {
    @Index(name = "idx_usuarios_rut", columnList = "rut")
    ,@Index(name = "idx_usuarios_email", columnList = "email")})
public class User extends BaseEntity implements UserDetails {
    ...

    @NotNull(message = "Debe seleccionar un nivel")
    @ManyToOne(fetch = FetchType.LAZY)
    public Role getRole() {
        return role;
    }
}

So my UPDATE is:

@Modifying
@Transactional
@Query("update User u set u.password = ?2 where u.company = ?1 and u.role.name not in ('ROLE_ADMIN', 'ROLE_ROOT')")
int updatePasswordForAll(Company company, String password);

The resulting native query is update usuarios cross join set password=? where company_id=? and (nombre not in ('ROLE_ADMIN' , 'ROLE_ROOT')) so I get com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual ...

What am I doing wrong ?

I tried with

@Query("update User u set u.password = ?2 join u.role r where u.company = ?1 and r.name not in ('ROLE_ADMIN', 'ROLE_ROOT')")

But this one is a bad formed update sentence org.hibernate.QueryException: node to traverse cannot be null! [update cl.arvisoft.mantenimiento.jpa.User u set u.password = ?2 join u.role r where u.company = ?1 and r.name not in ('ROLE_ADMIN', 'ROLE_ROOT')].

Answer 1

In JPQL you cannot join entities when doing update operation (see reference).

To workaround this situation try to use a sub-select, something like this (not tested):

update 
    User u 
set 
    u.password = ?2 
where 
    u.company = ?1 and 
    u.role in (select r from Role r where r.name not in ('ROLE_ADMIN', 'ROLE_ROOT'))