hibernate - Spring JPA 存储库为更新子句生成错误的 native 查询

Question

我正在尝试使用以下方法更新我的实体列:

@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);

此方法给出异常原因:

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

因为生成的原生查询是错误的:

update person cross join set hix_humber=?, set fix_humber=?其中类型=？

我的 Person 实体类如下所示:

@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;
}

和主键实体:

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

    private Long roleId;

    private Long storeId;
}

我做错了什么？谢谢。

Answer 1

我认为问题在于您正在间接尝试引用依赖实体 Role。这需要更新语句中的 JOIN 并且它在 native 查询中的转换效果不佳。

尝试改用子查询，这可能会更好地转换为 native 查询:

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'"
    )