Saturday, June 28, 2008

JPA and bit operations (Hibernate/Postgres)

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.

15 comments:

  1. Why do you use a bit mask in the first place? Optimization?

    -- Daan

    ReplyDelete
  2. Extensibility is the main reason, I want to add more flags without database change.

    I 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...

    ReplyDelete
  3. 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.

    ReplyDelete
  4. Sorry Will. I am not an expert in Postgres either, never heard of SMTWRFA (wow, that really is a WAYTOOLONGACRONYM).

    ReplyDelete
  5. Why not wrap the "binary and operator" in a stored function ?
    Hibernate/JPA wouldn't choke on it and it would also enhance readability IMO.

    ReplyDelete
  6. @Maarten, I always think of stored procedures as a last resort. But in this case they would have fit nicely. Thanks for the suggestion.

    ReplyDelete
  7. Sorry, 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
  8. @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.

    ReplyDelete
  9. Your 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.

    Actually, 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

    ReplyDelete
  10. Hi, great post saved me a day of pain

    I 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

    ReplyDelete
  11. Thanks rudibrovo, that fixes the problem that anonymous identified.

    ReplyDelete
  12. Thanks people it works. Formula mod(flagBits / bitmask, 2) = 1 resolved my problem.

    ReplyDelete
  13. I needed a check for a bit NOT being set so a small adjustment:
    mod(floor(flagBits / bitMask) , 2) = 0

    ReplyDelete
  14. To anonymous: please be aware that the article is flawed. Look at the comments.

    ReplyDelete
  15. This pre-previous answer saved my day : I needed a check for a bit NOT being set so a small adjustment:
    mod(floor(flagBits / bitMask) , 2) = 0

    ReplyDelete