Spring JPA repository generates wrong native query for update clause

Question

I'm trying to update my entity columns with following method:

@Modifying
@Query("update Person person set person.hixNumber = :hixNumber , "
        + "fixNumber.fixNumber = :fixNumber where person.role.type = 'ADMIN'")
int updatePersonByRole(@Param("hixNumber ") int hixNumber , @Param("fixNumber ") int fixNumber);

This method gives exception with reason:

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "cross"

because the generated native query is wrong:

update person cross join set hix_humber=?, set fix_humber=? where type=?

My Person entity class looks like this:

@Getter
@Setter
@Entity
@NoArgsConstructor
public class Person {

    @EmbeddedId
    private PersonId personId;

    @MapsId("roleId")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "role_id", insertable = false, updatable = false)
    private Role role;

    @MapsId("storeId")
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "store_id", insertable = false, updatable = false)
    private Store store;

    private int hixNumber;

    private int fixNumber;
}

and primary key entity:

@Getter
@Setter
@EqualsAndHashCode
@Embeddable
public class Personid implements Serializable {

    private Long roleId;

    private Long storeId;
}

What am I doing wrong? Thank you.

What is the relation of Person and Role? How is it annotated? — Maciej Kowalski, Sep 01 '19 at 10:11

score 1 · Answer 1 · answered Sep 01 '19 at 10:33

I think the problem lies in the fact that you are indirectly trying to reference a dependent entity Role. This requires a JOIN in the update statement and it simply does not translate well in a native query.

Try to use a subquery instead, that might transalte better to a native query:

update Person person 
set person.hixNumber = :hixNumber,
    person.fixNumber = :fixNumber 
where exists (
      select personSub.id 
      from Person personSub
        inner join personSub.role r 
      where person.id = personSub.id 
          and r.type = 'ADMIN'"
    )

score 1 · Answer 2 · answered Sep 01 '19 at 10:57

That is probably the issue : where person.role.type = 'ADMIN'".
Here person is an entity and role another. When you apply the where clause to a field of the relation of the entity, that means that you ask to JPA to perform a join between person and role. The problem is that you cannot use join in a update JPA query.

Some SO references that could interest you :

To bypass that limitation, you should use a subquery that is not a join but a valid alternative in this context. For example something like that :

@Query("update Person person set person.hixNumber = :hixNumber , "
        + "fixNumber.fixNumber = :fixNumber where " 
        + "person.roleId in (select r.roleId from role r where r.type = 'ADMIN')")

Spring JPA repository generates wrong native query for update clause

2 Answers2