问题描述:

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.

网友答案:

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>
网友答案:

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!)

相关阅读:
Top