Hibernate bit array to entity mapping

Question

I am trying to map a normalized Java model to a legacy database schema using Hibernate 3.5. One particular table encodes a foreign keys in a one-to-many relationship as a bit array column.

Consider tables person and club that describes people's affiliations to clubs:

person: .----.------.    club: .----.---------.---------------------------.
        | id | name |          | id |   name  | members | binary(members) |
        |----+------|          |----+---------|---------+-----------------|
        |  1 | Bob  |          | 10 | Cricket |       0 |             000 |
        |  2 | Joe  |          | 11 | Tennis  |       5 |             101 | 
        |  3 | Sue  |          | 12 | Cooking |       7 |             111 |   
        '----'------'          | 13 | Golf    |       3 |             100 |
                               '----'---------'---------'-----------------'

So hopefully it is clear that person.id is used as the bit index in the bit array club.members:

.---.---.---.
| S | J | B |
| u | o | o |
| e | e | b |
|---+---+---|
| 1 | 0 | 1 |
'---'---'---'

In this example the members column tells us that:

• no one is a member of Cricket --- no flags set
• Bob/Sue -> Tennis --- flags at positions 1 and 3 are set
• Bob/Sue/Joe -> Cooking --- flags at positions 1, 2 and 3 are set
• Sue -> Golf --- flag at position 3 is set

Now, for this example a join table could've been used instead which would simplify matters and avoid many potential issues - e.g: the maximum range of members placing an upper bound on the number of people rows. However, I am stuck with this schema and it seems that there were factors in favour of using a bit array column way back when.

In my Java domain I'd like to model this schema with entities like so:

class Person {
    private int id;
    private String name;
    ...
}

class Club {
    private Set<Person> members;
    private int id;
    private String name;
    ...
}

I am assuming that I must use a UserType implementation but have been unable to find any examples where the items described by the user type are references to entities - not literal field values - or composites thereof. Additionally I am aware that I'll have to consider how the person entities are fetched when a club instance is loaded.

Can anyone tell me how I can tame this legacy schema with Hibernate?

Answer 1

I've never faced this situation but I think that you'll need to implement a custom UserCollectionType (see chapter 5.2.3. Custom value types in Hibernate Core documentation), the UserCollectionType being an extension point which may be used to support any damn collection and collection semantics you like.

I'm not sure how well they are supported by annotations though (according to HHH-4417, you may have to use a hack). Using hbm.xml for this would be a good idea here IMO.

Some more pointers/discussions:

• https://forum.hibernate.org/viewtopic.php?f=9&t=938522&start=0
• http://www.javalobby.org/java/forums/m91832311.html (different case but may give you an idea of how to start)