Mapping multiple-row per item objects in Hibernate

Question

I'm encountering somewhat of an, uh, unorthodox design and I'm not quite sure how to handle it. The table I'm trying to map looks like:

TABLE example {
    ID INT,
    CATEGORY VARCHAR,
    PROPERTY VARCHAR,
    VALUE VARCHAR);

A single id can have several rows (obviously, not a primary key). As an example, it could look like:

# ID  CATEGORY     PROPERTY VALUE
  1   general_info name     order 1
  1   general_info date     1/1/2009
  ...

Every ID might have several different categories for it. Property names are unique for any given (id, category) combination.

(EDIT) The ID field is a foreign key to objects in a different table. I need to be able to get from these objects to the various properties stored in this table, using only the ID field. If a composite key is the way to go, how do I then link them?

(EDIT2) I also think the detail you're missing here is that all the data with the same ID in column one conceptually belongs to the same object. I don't want a separate instance for every (ID,CATEGORY) combination.

Obviously, this isn't very normalized. Worst case scenario, I set up some extra tables that are normalized and copy everything over, but I was wondering if anyone could suggest a sensible way to get this information into hibernate backed objects directly? If necessary in some sort of bag of String properties.

I'm using hibernate-annotations btw.

Answer 1

Use a composite key with ID, CATEGORY and PROPERTY. See Multiple key in hibernate how to? and JPA - Entity design problem for examples on how to implements this (@EmbeddedId is the key!)

Answer 2

Since the ID is a foreign key to another table (let's call that table 'container'), this can be mapped as a Map with a composite key.

Example:

public class Container {
    private int id;
    private Map<Key,String> values = new HashMap<Key,String>();

    public String getValue(String category, String property) {
        return values.get(new Key(category, property));
    }

    public void setValue(String category, String property, String value) {
        values.put(new Key(category, property), value);
    }

    public static class Key {
        private String category;
        private String property;

        public Key(String cat, String prop) {
            category = cat;
            property = prop;
        }

        public String getCategory() {
            return category;
        }

        public String getProperty() {
            return property;
        }

        @Override
        public boolean equals(Object obj) {
            if (!(obj instanceof Key)) {
                return false;
            }
            Key k = (Key)obj;
            return category.equals(k.category) && property.equals(k.property);
        }

        @Override
        public int hashCode() {
            return 37*category.hashCode() + property.hashCode();
        }
    }
}

Mapping:

<class name="Container" table="container">
    <id column="ID" name="id">
        <generator class="native"/>
    </id>
    <map cascade="all-delete-orphan" name="values" table="example">
        <key column="ID"/>
        <composite-map-key class="Container$Key">
            <key-property name="category" type="string" column="CATEGORY"/>
            <key-property name="property" type="string" column="PROPERTY"/>
        </composite-map-key>
        <element type="string" column="VALUE"/>
    </map>
</class>