java - 使用可选参数更新查询

Question

我正在尝试使用可选参数更新行，但我在以下查询中遇到错误。

@Modifying
@Transactional
@Query("UPDATE Visit visits SET "
        + " visits.approvalStatus is null OR visits.approvalStatus = :approvalStatus "
        + " , visits.declineReason is null OR visits.declineReason =:declineReason "
        + " WHERE visits.visitId in :visitIds ")
public int patchUpdate(@Param("approvalStatus") String approvalStatus,
        @Param("declineReason") String declineReason, @Param("visitIds") List<Integer> visitIds);

错误日志:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting EQ, found 'is' near line 1, column 69 [UPDATE com.quest.vms.entity.Visit visits SET  visits.approvalStatus is null OR visits.approvalStatus = :approvalStatus  WHERE visits.visitId in :visitIds ]

编辑添加信息:这里我想在新数据不为空时更新特定字段，否则应忽略该参数。

谢谢你的帮助

Answer 1

is null 用于检查字段。要根据条件设置列，您可以使用:

UPDATE Visit visits SET "
        + "  visits.approvalStatus = CASE "
        + " WHEN visits.visitId in :visitIds THEN :approvalStatus "
        + " ELSE NULL "
        + " END"