Hibernate/JPA Map of Primitives causes strange left outer join

I am having difficulty achieving an appropriate mapping of an entity containing a Map of primitives (Map<String, String>). The resulting SQL is not what I expect (a left outer join) and the result is that when retrieving a list of said entities, entities that should be unique are duplicated in the result.

Here are is the entity (abbreviated):

@Entity(name = "ZPrincipal")
@Table(name = "users")
public class ZPrincipal implements Principal, Serializable {
    @Id
    @Column(name = "username")
    private String username;

    @ElementCollection(fetch = FetchType.EAGER)
    @CollectionTable(name = "user_metadata", joinColumns = { @JoinColumn(name = "username") })
    @MapKeyColumn(name = "meta_key")
    @Column(name = "meta_value", nullable = false)
    private Map<String, String> metadata;
}

As you can see, there is a user class (ZPrincipal) that contains a Map. My tables look like this (again, abbreviating some fields from 'users' such as email, password, etc):

CREATE TABLE users (
    username VARCHAR(60) NOT NULL,
    PRIMARY KEY (username)
);

CREATE TABLE user_metadata (
    username VARCHAR(60) NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value VARCHAR(1024) NOT NULL,
    CONSTRAINT user_meta_fk FOREIGN KEY (username) REFERENCES users (username),
    PRIMARY KEY (username, meta_key)
);

Some example contents:

Users Table:

|username |
+---------+
|admin    |
|brett    |
+---------+

Metadata Table:

|username |meta_key  |meta_value  |
+---------+----------+------------+
|brett    |key1      |value1      |
|brett    |key2      |value2      |
+---------+----------+------------+

With the above mapping, when I use Hibernate to retrieve a list of ZPrincipals, like so:

Criteria criteria = session.createCriteria(ZPrincipal.class);
List<ZPrincipal> list = criteria.list();

Hibernate runs the following query:

select this_.username as username9_1_, 
       metadata2_.username as username9_3_, 
       metadata2_.meta_value as meta2_3_, 
       metadata2_.meta_key as meta3_3_
from users this_ 
l开发者_如何学JAVAeft outer join user_metadata metadata2_ on this_.username=metadata2_.username

Resulting in returned rows:

|username9_1_  |username9_3_  |meta2_3_  |meta3_3_  |
+--------------+--------------+----------+----------+
|admin         |null          |null      |null      |
|brett         |brett         |key1      |value1    |
|brett         |brett         |key2      |value2    |
+--------------+--------------+----------+----------+

This results in a list of users containing 3 entities (i.e. "the problem"), the "admin" user object, and two "brett" user objects (identical). Both "brett" instances do indeed contain a properly populated Map. For those wondering, the ZPrincipal class does override equals() which provides a comparison based on username (same as primary key), and overrides hashCode() also hashing on username.

Our shop works from "schema first" design, and the schema seems "correct" in the database sense. Possibly this problem could be solved with an intervening mapping table and generated id's in the user_metadata table, but from reading the JPA and Hibernate documentation available (http://en.wikibooks.org/wiki/Java_Persistence/ElementCollection) it seems that a mapping of a collection of primitive elements using only two tables should be possible.

What is missing in the mapping? Or is it a bug in Hibernate? And if so, can anyone think of a mapping work-around? I've mucked around with the annotations quite a bit have no joy.

This is expected and documented behavior. The instances are the exact same object (since hibernate guarantees the same entity with the same primary key to have exactly one instance in a JVM). You can do a client-side distinct to remove them. You can read the Hibernate FAQ to understand why they decided to not do it automatically.

What you tried with "FROM ZPrincipal" is to tell Hibernate to not do an eager fetch of the collection and hence it would have to load the map for each entity on-the-fly (which can result in the N+1 select problem if you intend to read the map entries of each fetched ZPrincipals. (Either that or Hibernate is issuing follow-up queries to populate the map).

The best way is to do the de-deduplication yourself and save some queries.

Reference: http://community.jboss.org/wiki/HibernateFAQ-AdvancedProblems#Hibernate_does_not_return_distinct_results_for_a_query_with_outer_join_fetching_enabled_for_a_collection_even_if_I_use_the_distinct_keyword