Search code examples
javahibernatehqlwhere-clausejpql

JPQL - Update with Multiple where conditions

Does JPQL support the following Update?

Update Person p set p.name = :name_1 where p.id = :id_1,
                    p.name = :name_2 where p.id = :id_2,
                    p.name = :name_3 where p.id = :id_3

If not, are there any alternatives? I tried it myself. But createQuery returned null.

Solution

  • Case expression is supported in JPA 2.0. I have provided pseudo-code, can make modifications accordingly.

    • You can try below JPQL query using CASE.

      Update Person p set p.name = CASE WHEN (p.id = :id1) THEN :name_1 WHEN (p.id = :id2) THEN :name_2 WHEN (p.id = :id3) THEN :name_3 END

      general_case_expression::= CASE WHEN conditional_expression THEN scalar_expression ELSE scalar_expression END

    • Else, can try Criteria API to build the query.

      when(Expression condition, R result) : Add a when/then clause to the case expression.

      criteriaBuilder.selectCase().when(criteriaBuilder.equal(person.get("id"), id1), name_1);