JPA Query for toggling a boolean in a UPDATE

Question

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 ?

Answer 1

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