title: Hibernate bit array to entity mapping
body: <p>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.</p>
  
  <p>Consider tables <code>person</code> and <code>club</code> that describes people's affiliations to clubs:</p>
  
  <pre><code>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 |
                                 '----'---------'---------'-----------------'
  </code></pre>
  
  <p>So hopefully it is clear that <code>person.id</code> is used as the bit index in the bit array <code>club.members</code>:</p>
  
  <pre><code>.---.---.---.
  | S | J | B |
  | u | o | o |
  | e | e | b |
  |---+---+---|
  | 1 | 0 | 1 |
  '---'---'---'
  </code></pre>
  
  <p>In this example the <code>members</code> column tells us that:</p>
  
  <ul>
  <li>no one is a member of Cricket --- no flags set</li>
  <li>Bob/Sue -> Tennis --- flags at positions 1 and 3 are set</li>
  <li>Bob/Sue/Joe -> Cooking --- flags at positions 1, 2 and 3 are set</li>
  <li>Sue -> Golf --- flag at position 3 is set </li>
  </ul>
  
  <p>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 <code>members</code> placing an upper bound on the number of <code>people</code> 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.</p>
  
  <p>In my Java domain I'd like to model this schema with entities like so:</p>
  
  <pre><code>class Person {
      private int id;
      private String name;
      ...
  }
  
  class Club {
      private Set&lt;Person&gt; members;
      private int id;
      private String name;
      ...
  }
  </code></pre>
  
  <p>I am assuming that I must use a <code>UserType</code> 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 <code>person</code> entities are fetched when a <code>club</code> instance is loaded.</p>
  
  <p>Can anyone tell me how I can tame this legacy schema with Hibernate?</p>
  
  <p><strong>Update</strong></p>
  
  <p>I have recently had to revisit this type of mapping in a legacy database. This time around it became apparent that our equivalent of the <code>members</code> table was actually a static set and could be hardcoded as an <code>Enum</code>. With this simplification it was fairly straightforward to implement a Hibernate <code>UserType</code> that converted between the bit array and a set of enums.</p>