JPA Query for toggling a boolean in a UPDATE

jpa, jpql, sql

Solution

That can be done with the case expression:

UPDATE FOO a 
SET a.bar = 
  CASE a.bar 
    WHEN TRUE THEN FALSE
    ELSE TRUE END
WHERE a.id in :ids

For nullable Boolean bit more is needed:

UPDATE FOO a 
SET a.bar = 
  CASE a.bar 
    WHEN TRUE THEN FALSE
    WHEN FALSE THEN TRUE
    ELSE a.bar END
WHERE a.id in :ids

Problem

SQL version works okay and I can toggle the boolean called bar ... ``` mysql> update Foo set bar = ! bar WHERE id IN (1, 7, 13); Query OK, 3 rows affected (0.02 sec) ``` Is there a easy JPA Query equivalent, I tried ``` final Set<Integer> ids; final Query query = em.createQuery("UPDATE " + Foo.class.getName() + " a set bar= !bar" + " where a.id in :ids"); query.setParameter("ids", ids); query.executeUpdate(); ``` The above gives a org.hibernate.QueryException. In my entity : ``` @Column(columnDefinition = "INTEGER", nullable = false) private boolean bar; ``` Any ideas on the JPA syntax ?

Original source