Update: Don't use the code in this article as it is flawed. See the comments for more tips.
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.
Why do you use a bit mask in the first place? Optimization?
ReplyDelete-- Daan
Extensibility is the main reason, I want to add more flags without database change.
ReplyDeleteI used an integer column type because that just works with any database layer (JDBC, Hibernate, Spring JDBC, etc) we may or may not use. Well, except if you start to query on it...
Do you know of a way with just postgres to return SMTWRFA Format for the bitmask? I have been trying to figure it out but I'm not an expert in postgres.
ReplyDeleteSorry Will. I am not an expert in Postgres either, never heard of SMTWRFA (wow, that really is a WAYTOOLONGACRONYM).
ReplyDeleteWhy not wrap the "binary and operator" in a stored function ?
ReplyDeleteHibernate/JPA wouldn't choke on it and it would also enhance readability IMO.
@Maarten, I always think of stored procedures as a last resort. But in this case they would have fit nicely. Thanks for the suggestion.
ReplyDeleteSorry, I probably used the wrong notation. I was just thinking that SMTWRFA is Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday in one letter terms.
ReplyDelete@Will. Haha, that's funny. I have a good grasp of English, but would never have seen that. Maybe I would have if the shorthand started with an M, as most of Europe uses Monday as first day of the week.
ReplyDeleteYour math is wrong. 24 / 8 = 1 is not true even though bit 3 of 24 is set (bit 3 AND bit 4 are set). Your test does not detect what you think it does.
ReplyDeleteActually, you do want (24 / 8) % 2 = 1. You don't have the % operator, so you need to simulate it. Let (24 / 8) / 2 = q. Then (24 / 2) - 2 * q = 1 is the test you need.
Integer arithmetic throughout, of course.
PTB
Hi, great post saved me a day of pain
ReplyDeleteI just did a small modification:
MOD(flagBits/bitmask,2) >= 1
or
I tried MOD(trunc(flagBits/bitmask),2) = 1 but that doesn't work with JPQL as well :P
that way you are safe on such case:
MOD(5/4, 2)
Cheers
Thanks rudibrovo, that fixes the problem that anonymous identified.
ReplyDeleteThanks people it works. Formula mod(flagBits / bitmask, 2) = 1 resolved my problem.
ReplyDeleteI needed a check for a bit NOT being set so a small adjustment:
ReplyDeletemod(floor(flagBits / bitMask) , 2) = 0
To anonymous: please be aware that the article is flawed. Look at the comments.
ReplyDeleteThis pre-previous answer saved my day : I needed a check for a bit NOT being set so a small adjustment:
ReplyDeletemod(floor(flagBits / bitMask) , 2) = 0