How to Hibernate annotate Map> or Map>

Question

using Hibernate Core 4.1.7, JPA annotations, java 1.7

Easy to fine are examples about Map<String, Entity> reading Hibernate doc on collections, or Map<String, String> here (stackoverflow).

Hard to find are examples on Map<String, Set<String>> (or even Map<String, Map<String, String>> just for curiosity about daisy chaning) why I ask this question here.

All I want is to save entities (accounts) containing named, multi-valued properties (=account attributes).

I have all working with 3 entity types: Account -> @OneToMany -> AccountAttribute -> @OneToMany -> AccountAttributeValue

But wrapping native Java types with my own Classes seems a bit silly to me. Cloning the Map<String, String> example I would like to have something like

@Entity
public class Account {

    @Id @GeneratedValue(strategy = GenerationType.AUTO) 
    @Column(name="key")
    private Long key;

    @ElementCollection(fetch=FetchType.EAGER)
    @JoinTable(name = "Attribute",
            joinColumns = @JoinColumn(name = "fkAccount"))
    @MapKeyColumn(name = "name")
    // next line is working for Map<String, String>, but not here!
    @Column(name = "value")  
    private Map<String, Set<String>> attributes = new HashMap<String, Set<String>>();

    // ... omitting: constructor, getters, setters, toString()

}

Which gives me
Initial SessionFactory creation failed: org.hibernate.MappingException: Could not determine type for: java.util.Set, at table: Attribute, for columns:[org.hibernate.mapping.Column(attributes)]

As DB layout I have created 2 Tables. - Table Account just having a key to point foreign key to - Table Attribute containing named value in each line.

E.g. for multivalued attributes I thought of it containing 2 lines with same fkAccount and name but different value - yes, I could have normalized even more, but I want to read my data in acceptable time :-)

CREATE  TABLE IF NOT EXISTS `foo`.`Account` (
  `key` INT NOT NULL AUTO_INCREMENT ,
  ...

CREATE  TABLE IF NOT EXISTS `foo`.`Attribute` (
  `fkAccount` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `value` VARCHAR(45) NOT NULL 
  ...

Any hints or alternate DB layout proposals appreciated.

EDIT - SOLVED
Solution from Tom (as far as I understood) working for me Thanky you guys, what an experience, solution in 1 day!

The table layout just mentioned above in my question works now with this classes.

@Entity
public class Account {
    /* ... omitting "key", see as above */

    /* NEW: now @CollectionTable 
            replaces @JoinTable / @MapKeyColumn / @Column  from above
    */
    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name="AccountAttribute",
                     joinColumns=@JoinColumn(name="fkAccount"))
    private Set<AccountAttribute> attributes = null;

    // ... omitting: constructor, getters, setters, toString()
}

and NEW

    @Embeddable
    public class AccountAttribute {

        @Column(name="attributeName")
        private String attributeName = null;

        @Column(name="attributeValue")
        private String attributeValue = null;

        // ... omitting: constructor, getters, setters, toString()
    }

Answer 1

JPA doesn't give you any way to map collections of collections of anything. You can map primitives, references to entities, embeddables, and collections of any of the preceding things.

Collection there means a set, list, or map; there isn't a multimap type here which would help you.

Therefore, there is sadly no way to map exactly the structure you want to map.

I think the closest you could come would be to define an embeddable class Attribute, containing a name and a value, then map a Set<Attribute>. You could then convert this to a Map<String, Set<String>> in code.

It's a shame there's no way to do this. I assume the JPA spec authors either didn't think of it, or thought it was an obscure enough corner case that it wasn't worth dealing with.

Answer 2

There's two ways to model this in the database, and it comes down to how many tables you want. If you want three, then the way you have working is basically right, although you could trim it to two entities (Account and AccountAttribute) where AccountAttribute contains a Set of values.

You can't model it with three tables and just an Account entity, because you don't have enough identifiers. The VALUE table would have to have a compound key made up of the account id and some kind of attribute key, your tables would have to look like:

ACCOUNT (id, ...)
ACCOUNT_ATTRIBUTE(account_id, account_attribute_id, ...)
ACCOUNT_ATTRIBUTE_VALUE(account_id, account_attribute_id, value, ...)

if AccountAttribute is an entity, then it has an ID. If not, it doesn't, and so how would you key the ACCOUNT_ATTRIBUTE_VALUE table?

This is borne out by the JPA spec, as mentioned in this other answer.

Now, you COULD do this in two tables with just an Account entity, by collapsing that Set in to some serialized form and persisting it as binary, or XML, or whatever. Not sure if that's worth the effort to you, but the column you sketched out (value varchar(45)) is almost certainly not long enough.