JPA and bit operations (Hibernate/Postgres)

In my current data model one of the entities has a column of integer type that contains a large set of boolean flags. Of course this is well hidden in the domain model objects, so you can just call things like event.isCanceled().

However, in the administration interface we need to search for this entity based on separate flags. The JPA query I found in our code base is select ... where flagBits = 1 (canceled is expressed in bit 0). Oops. What if the entity is not only canceled, but also sold out (bit 1)? Quite wrong, lets fix it.

So, I rewrote the where clause to flagBits & 1 = 1 (& is the binary and operation in Postgres). Hibernate, our JPA implementation parses the query to do its ORM magic. Unfortunately, Hibernate does not understand the & operation (though I heard it did in Hibernate 2) so it will throw an exception.

Looking through the Hibernate and Postgres documentations I found the get_bit operation. It does not except integer types as their value, so a cast was needed: get_bit(''||flagBits, 1) == 1. Another exception. And hopelessly wrong as well, ''||flagBits creates a string with the decimal representation of the number, the function get_bit expects a byte array. Maybe: get_bit((''||flagBits)::bytea, 1) == 1. Not really, the problem is still there. And furthermore, Hibernate also does not support the || and :: operations, more exceptions from Hibernate. (The :: could be replaced by a supported cast operation.)

Back to basics. How can you access a given bit with more basic, supported operations?
Fact: division by 2x will shift the bits right by x.
Fact: a number modulo 2 gives 1 if bit 0 is set.

What follows is the where clause (flagBits / bitmask) % 2 = 1. Where bitmask is 1 for bit 0, and 2 for bit 1, etc. Oops, % is not supported either! Last attempt: mod(flagBits / bitmask, 2) = 1. Success at last.

Conclusion

• JPA/Hibernate give an extensive but still limited subset of SQL expressiveness.
• It pays off to pay attention during math classes.